Re: Help with triggers - Mailing list pgsql-novice

Thanks for the information.  My approach may have worked for a while,
but growth would certainly have caused a problem.  I've decided to
change my design a bit, to include the following table:

acl_tbl (
   resource_id SERIAL,
   employee_id CHAR(8)
      REFERENCES employee_tbl(id)
      ON DELETE CASCADE
   PRIMARY KEY (resource_id, employee_id)
);


Each resource that requires an access list will have a column named
"resource_id" of type INTEGER.  I think this will be more utilitarian.
There may be more to this table, like a "who granted the permission"
column and an expiration timestamp.  I think I'll revisit my design and
see if there are any other similar stumbling blocks.

Thanks again.

Tom Lane wrote:

> John Burski <John.Burski@911ep.com> writes:
>
>> I've defined a trigger as follows:
>
>> CREATE FUNCTION create_acl_table()
>> RETURNS opaque
>> AS 'BEGIN
>>       CREATE TABLE new.access_list (
>>          id char(8) unique
>>             REFERENCES employee_tbl
>>             ON DELETE CASCADE);
>>    END;'
>> LANGUAGE 'plpgsql';
>
>
> plpgsql can't substitute plpgsql variable names into utility statements,
> only into the "big 4" query types (select/insert/update/delete).  You
> could make this work by forming the CREATE TABLE command as a string and
> feeding it to EXECUTE.
>
> However, my counsel is to forget this approach entirely and change your
> database design.  Creating tables on-the-fly as data items come in is
> going to be a constant headache for you; SQL just isn't designed to make
> that work conveniently, and databases with thousands of tables in them
> are going to have performance problems too.  You'd be a lot better off
> to merge all of these on-the-fly access_list tables into one big,
> permanent access_list table with a couple more columns serving as keys.
> Something like
>
> CREATE TABLE access_list (
>     area1 text,
>     area2 text,
>         id char(8)
>             REFERENCES employee_tbl
>             ON DELETE CASCADE,
>     unique(area1, area2, id));
>
> (You weren't clear about what the two components of your area_acl
> names really were, so I've called them area1 and area2 here.)
>
> With this approach all of your routine operations are simple DML
> (insert/update/delete), not DDL (create/delete table).  DML stuff
> tends to be a lot better optimized in Postgres, and most other DBMSes
> that I've heard of.  You'll also find yourself able to use the regular
> foreign key support for lots of things that would take custom triggers
> in the multi-table design --- for example, making the individual
> access_list tables go away again at appropriate times.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>

--
John Burski
I.T. Manager and Systems Administration
911 Emergency Products, Inc.
25 Sixth Avenue North
Saint Cloud, MN  56303
John.Burski@911ep.com

800-863-6911, extension 221
FAX: 800-863-2991
www.911ep.com