[GENERAL] Nested tables
How can I create nested tables ? I haven't find how it can be made in the doc. Could someone give me a little example to create and then insert in nested table? Thank you. Renaud THONNART
Re: [GENERAL] Nested tables
Jason Vasquez writes: PostgreSQL lets me create the class myItem with a "acl" field type, but I can't figure out how to insert multiple values (or any values!) into the access_control field... This seems to be a remnant of the highly praised "object-relational" features once present in Postgres but now fallen to neglect and poorly understood. You probably don't want to do this but use a foreign key instead. -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
Re: [GENERAL] Nested tables
Thanks to all for the great responses. Foreign keys do seem to be the way to go. That would be my luck to pick a data type for a table name...this type seems a bit obscure--I wasn't able to find any documentation on it. Does anyone know what it is for? Thanks, -Jason Ed Loehr [EMAIL PROTECTED] on 03/03/2000 06:32:19 PM To: Jason Vasquez/AM/LLY@Lilly, [EMAIL PROTECTED] cc: bcc: Subject: Re: [GENERAL] Nested tables Ed Loehr wrote: Jason Vasquez wrote: Basically, I'd like to set up an ACL-like property for each record. A table could be strucured like this: Table ACL: Attribute |Type | Modifier ---+-+ uniqid| varchar(12) | not null date_created | timestamp | not null date_modified | timestamp | not null read | boolean | not null default 'f'::bool write | boolean | not null default 'f'::bool execute | boolean | not null default 'f'::bool delete| boolean | not null default 'f'::bool Table myItem: Attribute|Type | Modifier +-+-- uniqid | varchar(12) | not null date_created | timestamp | not null date_modified | timestamp | not null author | text| not null title | text| not null access_control | acl | not null PostgreSQL lets me create the class myItem with a "acl" field type, but I can't figure out how to insert multiple values (or any values!) into the access_control field... Hmmm. Why would you want that? Do you really mean to insert multiple records, each with an individual access_control value? At the risk of completely oversimplifying your problem, my guess is that you're looking for the notion of a foreign key in myItem, i.e., Table ACL: uniqid integer not null unique, -- or use pgsql 'serial' type... ... Table myItem: uniqid integer not null unique, ... ACL_uniqid integer not null -- a foreign key into ACL Well, I snooped around a bit and apparently there is a built-in type called 'acl'. So either I spoke up when I had no idea what I was talking about (and no idea that I had no idea) but you did, or you got unlucky and named a table by the same name as a pgsql built-in type. Chances are it's the former. Cheers, Ed Loehr
Re: [GENERAL] Nested tables
Jason Vasquez wrote: I've looked through the documentation, but I've not found anything that addresses this situation. Is this possible with PostgreSQL? Or maybe there is a better way to accomplish what I want to do? Basically, I'd like to set up an ACL-like property for each record. A table could be strucured like this: Table ACL: Attribute |Type | Modifier ---+-+ uniqid| varchar(12) | not null date_created | timestamp | not null date_modified | timestamp | not null read | boolean | not null default 'f'::bool write | boolean | not null default 'f'::bool execute | boolean | not null default 'f'::bool delete| boolean | not null default 'f'::bool Table myItem: Attribute|Type | Modifier +-+-- uniqid | varchar(12) | not null date_created | timestamp | not null date_modified | timestamp | not null author | text| not null title | text| not null access_control | acl | not null PostgreSQL lets me create the class myItem with a "acl" field type, but I can't figure out how to insert multiple values (or any values!) into the access_control field... Hmmm. Why would you want that? Do you really mean to insert multiple records, each with an individual access_control value? At the risk of completely oversimplifying your problem, my guess is that you're looking for the notion of a foreign key in myItem, i.e., Table ACL: uniqid integer not null unique, -- or use pgsql 'serial' type... ... Table myItem: uniqid integer not null unique, ... ACL_uniqid integer not null -- a foreign key into ACL Cheers, Ed Loehr
Re: [GENERAL] Nested tables
Ed Loehr wrote: Jason Vasquez wrote: Basically, I'd like to set up an ACL-like property for each record. A table could be strucured like this: Table ACL: Attribute |Type | Modifier ---+-+ uniqid| varchar(12) | not null date_created | timestamp | not null date_modified | timestamp | not null read | boolean | not null default 'f'::bool write | boolean | not null default 'f'::bool execute | boolean | not null default 'f'::bool delete| boolean | not null default 'f'::bool Table myItem: Attribute|Type | Modifier +-+-- uniqid | varchar(12) | not null date_created | timestamp | not null date_modified | timestamp | not null author | text| not null title | text| not null access_control | acl | not null PostgreSQL lets me create the class myItem with a "acl" field type, but I can't figure out how to insert multiple values (or any values!) into the access_control field... Hmmm. Why would you want that? Do you really mean to insert multiple records, each with an individual access_control value? At the risk of completely oversimplifying your problem, my guess is that you're looking for the notion of a foreign key in myItem, i.e., Table ACL: uniqid integer not null unique, -- or use pgsql 'serial' type... ... Table myItem: uniqid integer not null unique, ... ACL_uniqid integer not null -- a foreign key into ACL Well, I snooped around a bit and apparently there is a built-in type called 'acl'. So either I spoke up when I had no idea what I was talking about (and no idea that I had no idea) but you did, or you got unlucky and named a table by the same name as a pgsql built-in type. Chances are it's the former. Cheers, Ed Loehr
Re: [GENERAL] Nested tables
Table ACL: Attribute |Type | Modifier ---+-+ uniqid| varchar(12) | not null date_created | timestamp | not null date_modified | timestamp | not null read | boolean | not null default 'f'::bool write | boolean | not null default 'f'::bool execute | boolean | not null default 'f'::bool delete| boolean | not null default 'f'::bool Table myItem: Attribute|Type | Modifier +-+-- uniqid | varchar(12) | not null date_created | timestamp | not null date_modified | timestamp | not null author | text| not null title | text| not null access_control | acl | not null PostgreSQL lets me create the class myItem with a "acl" field type, but I can't figure out how to insert multiple values (or any values!) into the access_control field... So you will have your tables with data redundancies and not normalized. __ Get Your Private, Free Email at http://www.hotmail.com
Re: [GENERAL] Nested tables
On Fri, 24 Jul 1998, Ferruccio Zamuner wrote: CREATE TABLE foo ( x int2 ); CREATE TABLE bar ( y foo, z int ); I read on some Postgres mailing digest that I had to put the OID of instance of foo into y field of bar. So I tried. The problem is that when I submit the following query: SELECT foo.x, bar.z WHERE foo.oid=bar.y; trying to catch int fields either from bar and from its instance of foo, Postgres replies that bar.y (of type foo) and foo.oid (of type oid) cannot be compared. I've also tried to cast them in many ways but the answer never changes! You could declare bar.y to be of type oid instead of type foo.
