Re: [SQL] Creating Views with Column Names based on Distinct

2003-06-11 Thread Guy Fraser 
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 Usernames 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 [EMAIL PROTECTED]



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [SQL] Creating Views with Column Names based on Distinct

2003-06-09 Thread Frank Bax 
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 [EMAIL PROTECTED]

[SQL] Creating Views with Column Names based on Distinct Row Data

2003-06-08 Thread Damien Dougan 
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



---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly