Re: Help with triggers

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(dot)Burski(at)911ep(dot)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(dot)Burski(at)911ep(dot)com

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