Обсуждение: Problem creating trigger-function with arguments (8.0rc4)
Hi I want to create a simple trigger that denies inserts into a particular table - but, since I want meaningfull error-messages, but don't want to create a function for each table, I figured I could pass the error-message to the trigger-function. This is what I tried: CREATE OR REPLACE FUNCTION functions.t_insert_deny(v_message text) RETURNS "trigger" AS $$ begin raise exception '%', v_message ; end ; $$ LANGUAGE 'plpgsql' VOLATILE STRICT; This results in a parse error at "v_message" (in the 4th line). If I replace RETURNS "trigger" with e.g. RETURNS "int8" it works... Is this is a bug, or has something regarding triggerfunctions and parameters changed in 8.0 (I also try omiting the parameter name in the function declartion, and using "$1" directly, but then the error-message says "Unknown parameter $1"). greetings, Florian Pflug
Вложения
On Fri, Jan 07, 2005 at 09:00:12PM +0100, Florian G. Pflug wrote: > CREATE OR REPLACE FUNCTION functions.t_insert_deny(v_message text) > RETURNS "trigger" AS $$ See the "Trigger Procedures" section of the PL/pgSQL documentation. The first paragraph contains this: Note that the function must be declared with no arguments even if it expects to receive arguments specified in CREATE TRIGGER --- trigger arguments are passed via TG_ARGV, as described below. > If I replace RETURNS "trigger" with e.g. RETURNS "int8" it works... > Is this is a bug, or has something regarding triggerfunctions and > parameters changed in 8.0 Changed since when? Are you saying this worked in an older version of PostgreSQL? If so, what version? The paragraph I quoted above goes back to at least 7.2. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
"Florian G. Pflug" <fgp@phlo.org> writes: > This is what I tried: > CREATE OR REPLACE FUNCTION functions.t_insert_deny(v_message text) > RETURNS "trigger" AS $$ There should probably be a specific error check telling you that a trigger function can't take any explicit arguments. But there isn't (and it's too late for 8.0 because we froze error message strings long since :-(). The CREATE TRIGGER parameter comes to the trigger function via TGARGS, not as a regular parameter. regards, tom lane
What do I do to unsubscribe from this mailing list? -- Internal Virus Database is out-of-date. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.5.0 - Release Date: 12/9/2004
On Fri, Jan 07, 2005 at 03:52:15PM -0500, Tom Lane wrote: > "Florian G. Pflug" <fgp@phlo.org> writes: > > This is what I tried: > > > CREATE OR REPLACE FUNCTION functions.t_insert_deny(v_message text) > > RETURNS "trigger" AS $$ > > There should probably be a specific error check telling you that a > trigger function can't take any explicit arguments. But there isn't > (and it's too late for 8.0 because we froze error message strings > long since :-(). > > The CREATE TRIGGER parameter comes to the trigger function via > TGARGS, not as a regular parameter. Um, so how would one write a trigger that takes arguments? I stubbed my toe on this in re: dbi-link, and would like to be able to write a trigger with arguments :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
On Fri, Jan 07, 2005 at 02:00:07PM -0800, David Fetter wrote: > On Fri, Jan 07, 2005 at 03:52:15PM -0500, Tom Lane wrote: > > > > The CREATE TRIGGER parameter comes to the trigger function via > > TGARGS, not as a regular parameter. > > Um, so how would one write a trigger that takes arguments? By accessing TG_ARGV (not TGARGS) in the function. See the "Trigger Procedures" documentation. CREATE TABLE foo (x INTEGER); CREATE FUNCTION trigfunc() RETURNS TRIGGER AS $$ BEGIN RAISE INFO 'trigger argument = %', TG_ARGV[0]; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trig_insert BEFORE INSERT ON foo FOR EACH ROW EXECUTE PROCEDURE trigfunc('insert argument'); CREATE TRIGGER trig_update BEFORE UPDATE ON foo FOR EACH ROW EXECUTE PROCEDURE trigfunc('update argument'); test=> INSERT INTO foo VALUES (123); INFO: trigger argument = insert argument INSERT 0 1 test=> UPDATE foo SET x = 456; INFO: trigger argument = update argument UPDATE 1 -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Fri, Jan 07, 2005 at 03:57:44PM -0700, Michael Fuhr wrote: > By accessing TG_ARGV (not TGARGS) in the function. Tom was probably thinking in C when he said TGARGS. The Trigger type (struct Trigger) has a tgargs member. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael Fuhr wrote: > On Fri, Jan 07, 2005 at 09:00:12PM +0100, Florian G. Pflug wrote: >>CREATE OR REPLACE FUNCTION functions.t_insert_deny(v_message text) >>RETURNS "trigger" AS $$ > See the "Trigger Procedures" section of the PL/pgSQL documentation. > The first paragraph contains this: > > Note that the function must be declared with no arguments even if > it expects to receive arguments specified in CREATE TRIGGER --- > trigger arguments are passed via TG_ARGV, as described below. Seems I should have RTFMed more ;-). I believe I even read this paragraph, but thought this refers to C-Functions, not plpgsql ones. Thanks for pointing this out. >>If I replace RETURNS "trigger" with e.g. RETURNS "int8" it works... >>Is this is a bug, or has something regarding triggerfunctions and >>parameters changed in 8.0 > Changed since when? Are you saying this worked in an older version > of PostgreSQL? If so, what version? The paragraph I quoted above > goes back to at least 7.2. I didn't test on anything other than 8.0 - but I used the 7.4 docu, not the 8.0 one, and since the docu says that trigger functions _can_ take parameters, I somehow believed that it has to be possible to _declare_ those arguments - Well, guess I should read more carefully ;-) greetings, Florian Pflug