Message-ID: <3EE76052.4060801@incentre.net>
Date: Wed, 11 Jun 2003 11:01:06 -0600
From: Guy Fraser <guy@incentre.net>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030609
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Creating Views with Column Names based on Distinct
References: <5.2.1.1.0.20030609100829.026126c0@pop6.sympatico.ca>
In-Reply-To: <5.2.1.1.0.20030609100829.026126c0@pop6.sympatico.ca>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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