Lenorovitz, Joel wrote:
I'd like to create a trigger function whose use can extend to multiple tables by employing the special variables available (e.g., TG_RELNAME). Below is a simple version of such a function that ought to prevent insertion of greater than 4 total records in the table that calls it. I'm not sure that I'm using or dereferencing the trigger variables correctly, however, particularly in the query. I have tried many syntax, type casting, and alternate variable assignment variations, but, aside from parsing successfully, this code does not seem to work as intended. Can somebody correct this specific example to have it work
You need to use EXECUTE to execute your dynamic query. You can't just put a string in a query and have it be handled as an identifier.
during testing would be welcome as well (RAISE EXCEPTION doesn't allow a variable value in the message string, plus it seems a little harsh).
Not true, and you don't need to raise an exception; a notice'd do just fine. Try this: RAISE NOTICE 'Trigger fired on table %', TG_RELNAME;
CREATE OR REPLACE FUNCTION trigger_fxn() RETURNS TRIGGER AS $$ BEGIN IF ((TG_OP = 'INSERT') AND (TG_WHEN = 'BEFORE')) THEN IF (SELECT COUNT(*) FROM text(TG_RELNAME)) < 4
You'll want to DECLARE an integer variable and use SELECT INTO with it. And EXECUTE, as mentioned.
-- Alban Hertroys alban@xxxxxxxxxxxxxxxxx magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
