Обсуждение: Creating Views with Column Names based on Distinct Row Data
Hi All, (I'm sure I'm not the first person to want to do this, but I didn't see any mention of it in the FAQ or developers FAQ nor in the docs - if I've missed something, a gentle pointer will be fine :) I was wondering if it is possible to create a table view based on a table which is effectively an "attribute list". For example, suppose I have two tables: CREATE TABLE user ( userid integer, username character varying, userpassword character varying, startdate date ); CREATE TABLE userdetail ( userid integer, attributename character varying, attributevalue character varying ); "user" holds pre-defined details about a user (things which are common to all users). "userdetail" holds (name,value) pairs about users. Now I want to make a public view of the user, which would have all of the defined fields in user, and all of the defined attributes across userdetail. (e.g. suppose we have 2 types of user - typeA has attributes x, y, z and typeB has attributes a, b. Then I'd want my public view to look like): CREATE TABLE PvUser ( userid integer, username character varying, userpassword character varying, startdate date, x character varying, y charactervarying, z character varying, a character varying, b character varying ); It is possible to do this (i.e. have the public view created by specifying the column names "AS" the distinct value of a column in rows in another table? CREATE VIEW PvUser AS SELECT u.userid u.username u.password u.startdate -- For each unique attributename in userdetail ud.attributevalue AS {Valueof ud.attributename} FROM user u, userdetail ud ; Is what I'm trying to do feasible? Thanks for any and all help, Damien
At 10:59 AM 6/6/03, Damien Dougan wrote: >I was wondering if it is possible to create a table view based on a table >which is effectively an "attribute list". > >For example, suppose I have two tables: > >CREATE TABLE user >( > userid integer, > username character varying, > userpassword character varying, > startdate date >); > >CREATE TABLE userdetail >( > userid integer, > attributename character varying, > attributevalue character varying >); > >Now I want to make a public view of the user, which would have all of the >defined fields in user, and all of the defined attributes across userdetail. I'll think you'll find what you're looking for if you search the archives of this mailing list for 'crosstab'.
Hi CREATE VIEW user_stuff ...select comand that displays what you want... ; This might be what you want ?:-) CREATE VIEW user_stuffSELECT username AS "Username",userpassword AS "Pass/Attribute",startdate::TEXT AS "Date/Value" FROM user UNION SELECT user.username,userdetail.attributename,userdetail.attributevalue::TEXT FROM user,userdetail WHERE user.userid = userdetail.userid ; Here is some psuedo output : -- select "Username"s that start with 'j' from view. -- NOTE: The columns I setup have capitals and 'unsafe' characters so they must be in double quotes. SELECT * from user_stuff where "Username" ~ '^j'; Username | Pass/Attribute | Date/Value ----------+----------------+------------joeuser | 5n00py | 01-01-01 joeuser | ju-attribute1 | ju-value1joeuser | ju-attribute2 | ju-value2 ...janedow | 5eaShe11 | 02-02-02 janedow | jd-attribute1 | jd-value1janedow | jd-attribute2 | jd-value2 ... NOTE: The the colums all have to be the same type {and probably size}. I would suggest using TEXT instead of VARCHAR(). Since the data in the third column is either a date or character data, I cast the date and value to TEXT so that they would both match. This looks suprisingly like a radius authentication database, I recently patched cistron to do PostgreSQL accounting, and will likely make an SQL authentication patch as well, or switch to freeRadius and help them fix up there software. I have looked at freeRadius a couple of times, but it has really bad docs for the SQL support. Hope this helps. Guy Frank Bax wrote: > At 10:59 AM 6/6/03, Damien Dougan wrote: > >> I was wondering if it is possible to create a table view based on a >> table >> which is effectively an "attribute list". >> >> For example, suppose I have two tables: >> >> CREATE TABLE user >> ( >> userid integer, >> username character varying, >> userpassword character varying, >> startdate date >> ); >> >> CREATE TABLE userdetail >> ( >> userid integer, >> attributename character varying, >> attributevalue character varying >> ); >> >> Now I want to make a public view of the user, which would have all of >> the >> defined fields in user, and all of the defined attributes across >> userdetail. > > > > I'll think you'll find what you're looking for if you search the > archives of this mailing list for 'crosstab'. > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >