Thread: 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
Attachment
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