Re: Problem with CREATE TRIGGER

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

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