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.0Changed 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