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).
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
2012/8/29 Alan Hodgson
<ahodgson@simkin.ca>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.
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice