Thread: Help with triggers
I'm having a bit of trouble creating a trigger.
First, a bit of background about what I'm trying to accomplish. I've
certain objects that will have restricted access (buildings, rooms and
areas for example). Each of these objects will have a table that
contains an ID, a description of the object, and the name of the talble
that constitutes the access control list. The access control list will
have the same format in all cases - a table of employee IDs that are
allowed access to the resource. When a new resource, a new area for
example, is added to the area table I would like the insertion into the
area_tbl to automatically create the corresponding access control list
table. (BTW, if there's an easier or better way to do this I'm all ears
- my paper rookie hat isn't wrinkled yet).
On to my case...
I've defined a table as follows:
CREATE TABLE area_tbl
(
id char(5) primary key,
description text not null,
access_list text -- A standard format has been defined.
-- See the "create_functions.sql" file for
-- a description.
);
The functions that create the access_list strings work OK - I tested
them prior to attempting to create a trigger. However, I'll include the
code in case there is something in there that causes a problem. Here it is:
-- The "make_acl_name" function is used to create a name for an
-- access control list table.
-- The standard format is "$1_$2_acl_tbl". For example, if you wanted
-- to create an access control list table name for "area 1" you would
-- use the function as follows:
-- make_acl_name('area','1');
CREATE FUNCTION make_acl_name(text,text)
RETURNS text
AS 'BEGIN
RETURN $1 || ''_'' || $2 || ''_acl_tbl'';
END;'
LANGUAGE 'plpgsql';
-- The "make_area_acl_name" function is used to create a name for
-- an access control list table as used in the "area_tbl" table.
CREATE FUNCTION make_area_acl_name(text)
RETURNS text
AS 'DECLARE
aclname text;
BEGIN
aclname = make_acl_name(''area'',$1);
RETURN aclname;
END;'
LANGUAGE 'plpgsql';
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';
-- This creates the actual trigger.
CREATE TRIGGER new_area_acl
AFTER INSERT
ON area_tbl
FOR EACH ROW
EXECUTE PROCEDURE create_acl_table();
Before I attempted to create the trigger I ran the following insert:
INSERT INTO area_tbl VALUES ('1', 'Area 1', make_area_acl_name('1') );
which worked OK.
After I created the trigger function and assigned it as a trigger (which
seemed to work OK - I didn't receive an error message) I ran a similar
insert that returned the following error message:
ERROR: parser: parse error at or near "$1"
When I dropped the trigger I could once again do inserts.
Thanks for your help.
--
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
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
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