By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,352 Members | 1,593 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,352 IT Pros & Developers. It's quick & easy.

Parameters from trigger to function.

P: n/a
Everyone, I need your help,

I am trying to pass a parameter to a function that is to be called by a
trigger. But, the correct function isn't being recognized and I am getting
an error stating that the function doesn't exist even though it does!

What I got going in psql...

CREATE TABLE testing (id SERIAL, data text); -- sequence = testing_id_seq

CREATE OR REPLACE FUNCTION id_protect(text) RETURNS TRIGGER AS '
DECLARE
sequence ALIAS FOR $1;
BEGIN
IF TG_OP = ''INSERT'' THEN
NEW.id := nextval(sequence);
RETURN NEW;
ELSIF TG_OP = ''UPDATE'' THEN
NEW.id := OLD.id;
RETURN NEW;
ELSE
RETURN NEW;
END IF;
END;
' LANGUAGE PLPGSQL;

\df id_protect
List of Functions
Result data type | Schema | Name | Argument data types
-------------------+----------+------------+------------------------
"trigger" | public | id_protect | text

CREATE TRIGGER protector BEFORE INSERT OR UPDATE ON testing FOR EACH ROW
EXECUTE PROCEDURE id_protect('testing_id_seq');

ERROR: function id_protect() does not exist

-----------------------------------------------------------------------------------------------------

Now, why am I getting that error?

Thx all,
Vams

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Vams <vm********@charter.net> writes:
CREATE OR REPLACE FUNCTION id_protect(text) RETURNS TRIGGER AS '
...
CREATE TRIGGER protector BEFORE INSERT OR UPDATE ON testing FOR EACH ROW
EXECUTE PROCEDURE id_protect('testing_id_seq'); ERROR: function id_protect() does not exist Now, why am I getting that error?


Trigger functions *never* take any normal parameters.

You can put parameters into the CREATE TRIGGER command, but they are
passed via a different mechanism (in plpgsql, it's an array called
tg_argv[]). This is a bit weird but I think it goes along well with
the fact that triggers get a whole bunch of parameters, and from time
to time we add some. We could not do that without breaking every
existing user trigger if the parameters were all to be declared as
explicit function parameters.

See http://www.postgresql.org/docs/7.4/s...l-trigger.html
for more ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.