473,320 Members | 2,041 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Parameters from trigger to function.

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
1 4568
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Dunc | last post by:
I'm new to Postgres, and getting nowhere with a PL/Perl trigger that I'm trying to write - hopefully, someone can give me some insight into what I'm doing wrong. My trigger is designed to reformat...
2
by: Jules Alberts | last post by:
Hello everyone, Several columns in sereval tables in my DB should always be lowercase. I now have a simple function: create or replace function code_lower() returns trigger as ' begin...
4
by: Jules Alberts | last post by:
Hello everyone, I'm working on a tiny trigger function that needs to ensure that all values entered in a field are lowercase'd. I can't use pl/pgsql because I have a dozen different columns...
2
by: Josué Maldonado | last post by:
Hello list, I have a tcl trigger function called audit_log(varchar, bpchar), if I write a trigger in a table CREATE TRIGGER tg_test AFTER INSERT OR UPDATE OR DELETE ON xtable FOR EACH ROW...
1
by: Barbara Lindsey | last post by:
I am a postgres newbie. I am trying to create a trigger that will put a copy of a record into a backup table before update or delete. As I understand it, in order to do this I must have a...
33
by: C# Learner | last post by:
Note ---- Please use a fixed-width font to view this, such as Courier New. Problem
2
by: Karl O. Pinc | last post by:
I'm sure I saw something like this on the postgresql web site but the the search function is down in the documentation area. I'm unable to pass a function arguments in a CREATE TRIGGER...
8
by: Frank van Vugt | last post by:
Hi, If during a transaction a number of deferred triggers are fired, what will be their execution order upon the commit? Will they be executed in order of firing or alfabetically or...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.