Обсуждение: 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
>
>