From: Guy Fraser <guy@incentre.net>
To: pgsql-sql@postgresql.org
Subject: Re: Creating Views with Column Names based on Distinct
Date: Wed, 11 Jun 2003 11:01:06 -0600
Message-ID: <3EE76052.4060801@incentre.net> (raw)
In-Reply-To: <5.2.1.1.0.20030609100829.026126c0@pop6.sympatico.ca>
References: <5.2.1.1.0.20030609100829.026126c0@pop6.sympatico.ca>
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
>
>
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: guy@incentre.net
Subject: Re: Creating Views with Column Names based on Distinct
In-Reply-To: <3EE76052.4060801@incentre.net>
* 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