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