Thread: Nested tables
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... Thanks, Jason
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
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
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 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden