Thread: Problem creating trigger

Using PostgreSQL 9.1 I have created my first function.  Heres the create statement reported by pgAdminIII:

CREATE OR REPLACE FUNCTION detect_branch(integer)
 RETURNS integer AS
' UPDATE company SET co_has_branch_sa=CASE WHEN (SELECT sum(br_ca_rate_sa) FROM branch WHERE br_co_id=$1)>0 THEN TRUE
ELSEFALSE END 
 WHERE co_id=$1 RETURNING 1; '
 LANGUAGE sql VOLATILE
 COST 100;
ALTER FUNCTION detect_branch(integer)
OWNER TO postgres;

Works well.

However, if I try to create a trigger:
CREATE TRIGGER run_detect_branch
AFTER UPDATE OR INSERT ON branch
FOR EACH ROW
EXECUTE PROCEDURE detect_branch()

I get an error "function detect_branch() does not exist".

Can some kind soul tell me why?

Michael Rowan
mike.rowan@internode.on.net

11 Kingscote Street
ALBERTON
South Australia 5014

tel 618 8240 3993
mob 0417 812 509

On Sun, 2012-06-17 at 10:21 +0930, Michael Rowan wrote:
> Using PostgreSQL 9.1 I have created my first function.  Heres the create statement reported by pgAdminIII:
>
> CREATE OR REPLACE FUNCTION detect_branch(integer)
>  RETURNS integer AS

...

> Works well.
>
> However, if I try to create a trigger:
> CREATE TRIGGER run_detect_branch
> AFTER UPDATE OR INSERT ON branch
> FOR EACH ROW
> EXECUTE PROCEDURE detect_branch()
>
> I get an error "function detect_branch() does not exist".

1. You need to write your function in a different language. sql is meant
for very simple functions. Try plpgsql:

  http://www.postgresql.org/docs/9.2/static/plpgsql.html

2. The function should have return type TRIGGER and must be declared to
take no arguments:

  http://www.postgresql.org/docs/9.2/static/sql-createtrigger.html

Also, see:

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

for some examples.

Regards,
    Jeff Davis

[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