Thread

1. Nested tables

   Jason Vasquez <vasquez_jason@lilly.com> — 2000-03-03T22:02:23Z

   
   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
   
   
   
   

2. Re: [GENERAL] Nested tables

   Ed Loehr <eloehr@austin.rr.com> — 2000-03-03T23:18:37Z

   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
   
   

3. Re: [GENERAL] Nested tables

   Ed Loehr <eloehr@austin.rr.com> — 2000-03-03T23:32:19Z

   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
   
   

4. Re: [GENERAL] Nested tables

   Peter Eisentraut <peter_e@gmx.net> — 2000-03-04T17:05:49Z

   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