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