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 [EMAIL PROTECTED]
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
