From: Damien Dougan <damien.dougan@mobilecohesion.com>
To: pgsql-sql@postgresql.org
Subject: Creating Views with Column Names based on Distinct Row Data
Date: Fri, 6 Jun 2003 14:59:40 +0000
Message-ID: <200306061459.40974.damien.dougan@mobilecohesion.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: damien.dougan@mobilecohesion.com
Subject: Re: Creating Views with Column Names based on Distinct Row Data
In-Reply-To: <200306061459.40974.damien.dougan@mobilecohesion.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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