Обсуждение: 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