Problem with CREATE TRIGGER

[Michael Rowan]

Hi
In Postgres 9.1 I have a function as a test (of my ability):

CREATE OR REPLACE FUNCTION insert_payment(integer, numeric)
RETURNS numeric AS
$BODY$
UPDATE company
SET co_payments=co_payments+$2
WHERE co_id=$1
RETURNING co_payments;
$BODY$
LANGUAGE sql VOLATILE
COST 100;
ALTER FUNCTION insert_payment(integer, numeric)
OWNER TO postgres;

This function exists, according to pgAdminIII

So I attempt to create a trigger:

CREATE TRIGGER increment_payments
AFTER INSERT ON payment
FOR EACH ROW
EXECUTE PROCEDURE insert_payment();

ERROR: function insert_payment() does not exist

What am I doing wrong here?


Michael Rowan
***@internode.on.net

11 Kingscote Street
ALBERTON
South Australia 5014

tel [phone]
mob [phone]

[Alan Hodgson]

Post by Michael Rowan
Hi
CREATE OR REPLACE FUNCTION insert_payment(integer, numeric)
RETURNS numeric AS
$BODY$
UPDATE company
SET co_payments=co_payments+$2
WHERE co_id=$1
RETURNING co_payments;
$BODY$
LANGUAGE sql VOLATILE
COST 100;
ALTER FUNCTION insert_payment(integer, numeric)
OWNER TO postgres;
This function exists, according to pgAdminIII
CREATE TRIGGER increment_payments
AFTER INSERT ON payment
FOR EACH ROW
EXECUTE PROCEDURE insert_payment();
ERROR: function insert_payment() does not exist
What am I doing wrong here?

PostgreSQL allows a basic form of function overloading. That is,
insert_payment() is not the same function as insert_payment(integer,numeric).
Both could exist and need to be referred to explicitly.

Also, trigger functions cannot take arguments (since there is no way to supply
them). And they should (probably must?) return type TRIGGER. So this function
couldn't be a trigger function.

[Bartosz Dmytrak]

Hi,
Trigger function in plpgsql must return a type of trigger. It is also
possible to pass arguments to trigger function (but not in the way You
expect).
Take a look at:
http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html
and consider TG_ARGV[] and TG_NARGS. It is also possible to recognize
record fields (using NEW, OLD variables) - maybe this will help with
arguments.

Of course You can use Your function with trigger function wrap-up e.g.:

CREATE OR REPLACE FUNCTION insert_payment() RETURNS TRIGGER
AS
$BODY$
BEGIN
IF TG_OP <> 'DELETE' THEN
PERFORM insert_payment(NEW.co_payments, NEW.co_id);
RETURN NEW;
ELSE
PERFORM insert_payment(OLD.co_payments, OLD.co_id);
RETURN OLD;
END IF;
END;
$BODY$
LANGUAGE PLPGSQL;

This example code has not been tested.

Regards,
Bartek

Post by Alan Hodgson

Post by Michael Rowan
Hi
CREATE OR REPLACE FUNCTION insert_payment(integer, numeric)
RETURNS numeric AS
$BODY$
UPDATE company
SET co_payments=co_payments+$2
WHERE co_id=$1
RETURNING co_payments;
$BODY$
LANGUAGE sql VOLATILE
COST 100;
ALTER FUNCTION insert_payment(integer, numeric)
OWNER TO postgres;
This function exists, according to pgAdminIII
CREATE TRIGGER increment_payments
AFTER INSERT ON payment
FOR EACH ROW
EXECUTE PROCEDURE insert_payment();
ERROR: function insert_payment() does not exist
What am I doing wrong here?

PostgreSQL allows a basic form of function overloading. That is,
insert_payment() is not the same function as
insert_payment(integer,numeric).
Both could exist and need to be referred to explicitly.
Also, trigger functions cannot take arguments (since there is no way to supply
them). And they should (probably must?) return type TRIGGER. So this function
couldn't be a trigger function.
--
http://www.postgresql.org/mailpref/pgsql-novice