pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCreating Views with Column Names based on Distinct Row Data
3+ messages / 3 participants
[nested] [flat]
* Creating Views with Column Names based on Distinct Row Data
@ 2003-06-06 14:59 Damien Dougan <damien.dougan@mobilecohesion.com>
2003-06-09 14:13 ` Re: Creating Views with Column Names based on Distinct Frank Bax <fbax@sympatico.ca>
0 siblings, 1 reply; 3+ messages in thread
From: Damien Dougan @ 2003-06-06 14:59 UTC (permalink / raw)
To: pgsql-sql
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 character varying,
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 {Value of ud.attributename}
FROM
user u, userdetail ud
;
Is what I'm trying to do feasible?
Thanks for any and all help,
Damien
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Creating Views with Column Names based on Distinct
2003-06-06 14:59 Creating Views with Column Names based on Distinct Row Data Damien Dougan <damien.dougan@mobilecohesion.com>
@ 2003-06-09 14:13 ` Frank Bax <fbax@sympatico.ca>
2003-06-11 17:01 ` Re: Creating Views with Column Names based on Distinct Guy Fraser <guy@incentre.net>
0 siblings, 1 reply; 3+ messages in thread
From: Frank Bax @ 2003-06-09 14:13 UTC (permalink / raw)
To: Damien Dougan <damien.dougan@mobilecohesion.com>; pgsql-sql
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'.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Creating Views with Column Names based on Distinct
2003-06-06 14:59 Creating Views with Column Names based on Distinct Row Data Damien Dougan <damien.dougan@mobilecohesion.com>
2003-06-09 14:13 ` Re: Creating Views with Column Names based on Distinct Frank Bax <fbax@sympatico.ca>
@ 2003-06-11 17:01 ` Guy Fraser <guy@incentre.net>
0 siblings, 0 replies; 3+ messages in thread
From: Guy Fraser @ 2003-06-11 17:01 UTC (permalink / raw)
To: pgsql-sql
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@postgresql.org
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-06-11 17:01 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-06 14:59 Creating Views with Column Names based on Distinct Row Data Damien Dougan <damien.dougan@mobilecohesion.com>
2003-06-09 14:13 ` Re: Creating Views with Column Names based on Distinct Frank Bax <fbax@sympatico.ca>
2003-06-11 17:01 ` Re: Creating Views with Column Names based on Distinct Guy Fraser <guy@incentre.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox