Re: Problem creating trigger

[note: please CC the list for follow-up questions]

On Sun, 2012-06-17 at 23:23 +0930, Michael Rowan wrote:
> Hi Jeff, thanks for your help, but I am puzzled. I will look at
> rewriting the function in plpgsql later, but for now it works
> correctly.
>
> Being very new to most of this I do not understand the meaning of "The
> function should have return type TRIGGER and must be declared to take
> no arguments". Looking at the examples in:
>
> http://www.postgresql.org/docs/9.1/static/sql-createfunction.html
>
> there is no mention of return type TRIGGER.

The RETURNS part of the trigger definition must be "RETURNS TRIGGER".
Now that I think about it, that is a little confusing.

> Does "must be declared to take no arguments" refer to the creation
> of the function or the trigger? This form of English is not my first
> language, and the meaning is not clear to me.

The creation of the function. You don't need arguments for this trigger
function anyway, because all trigger functions have access to the new
and the old row (if applicable).
>
> Anyway, I have tried a very simple function taken straight from the
> 9.1 docs and I get the the same function xyz() does not exist error.
> pgAdminIII lists it, in the correct schema with the correct owner
> namely, for the present, postgres.
>
Was the function you used from the docs a trigger function?

The most important doc to read is this one:

http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html

Because it contains examples similar to what you're trying to do. Notice
that the functions being created take no arguments and "return trigger".

Since you already have a working SQL function, you can use that from
your trigger function:

CREATE OR REPLACE FUNCTION detect_branch_trfunc() RETURNS TRIGGER
LANGUAGE plpgsql AS $$
BEGIN
PERFORM detect_branch(NEW.br_co_id);
RETURN NEW;
END;
$$;

CREATE TRIGGER run_detect_branch
AFTER UPDATE OR INSERT ON branch
FOR EACH ROW
EXECUTE PROCEDURE detect_branch_trfunc();

But I recommend rewriting it in plpgsql after you get the hang of it.

Hope this helps,
Jeff Davis