Re: Help with triggers

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