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