Content-Type: text/plain;
  charset="us-ascii"
From: Damien Dougan <damien.dougan@mobilecohesion.com>
Organization: Mobile Cohesion
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
User-Agent: KMail/1.4.1
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200306061459.40974.damien.dougan@mobilecohesion.com>

Hi All,


(I'm sure I'm not the first person to want to do this, but I didn't see any=
=20
mention of it in the FAQ or developers FAQ nor in the docs - if I've missed=
=20
something, a gentle pointer will be fine :)


I was wondering if it is possible to create a table view based on a table=
=20
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 a=
ll=20
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=
=20
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 ty=
peB=20
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=20
column names "AS" the distinct value of a column in rows in another table?

CREATE VIEW PvUser AS
SELECT=20=20
  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?

=20=20=20=20=20=20=20=20=20=20
Thanks for any and all help,

Damien