Обсуждение: Problem with CREATE TRIGGER
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
11 Kingscote Street
ALBERTON
South Australia 5014
tel 618 8240 3993
mob 0417 812 509
On Wednesday, August 29, 2012 08:52:23 AM Michael Rowan wrote: > 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? > 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.
Hi,
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;
Regards,
Bartek
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$
BEGINIF 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
2012/8/29 Alan Hodgson <ahodgson@simkin.ca>
On Wednesday, August 29, 2012 08:52:23 AM Michael Rowan wrote:PostgreSQL allows a basic form of function overloading. That is,
> 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?
>
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.
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice