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_stuff
SELECT 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-value1
joeuser | ju-attribute2 | ju-value2
...
janedow | 5eaShe11 | 02-02-02
janedow | jd-attribute1 | jd-value1
janedow | 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(at)postgresql(dot)org
>
>