Re: Creating Views with Column Names based on Distinct

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
>
>