Thread: Writing First Trigger Function in PostgreSQL

Hello all,
 
I am using PostgreSQL 8.0.1 on a Windows 2003 platform.
 
I am (trying to) set up a function and a related insert trigger via pgAdmin3 / psql, but I am not 
succeeding.
 
I have set up a bare bones function named update_rpt_history (stripped down because of trying
to eliminate any errors). 
CREATE OR REPLACE FUNCTION update_rpt_history(char) RETURNS INTEGER AS $$
BEGIN
  RETURN 0;
END
$$ LANGUAGE plpgsql;
 
The only message I get back from pgAdmin3 is:
    Query returned successfully with no result in 47 ms.
I don't know whether 'no result' means success or not, as it does not appear in pgAdmin's list of 
functions after executing. So I went into psql, and found that the function does appear to exist:
 
adns-# \df update_rpt_history;
                          List of functions
 Schema |        Name        | Result data type | Argument data types
--------+--------------------+------------------+---------------------
 public | update_rpt_history | integer          | character
(1 row)
 
So, I proceeded to try to set up a trigger against it in pgAdmin3 like so:
create trigger tg_crchist_aft_ins after insert on adns_crc_history
  for each row execute procedure update_rpt_history(crc)
and got back this response from pgAdmin3:
        ERROR:  function update_rpt_history() does not exist
Tried the command again qualified by schema name (public.) but got same response.
 
Tried the create trigger command in psql, but same result.
adns=# create trigger tg_crchist_aft_ins after insert on adns_crc_history 
adns>  for each row execute procedure update_rpt_history(crc);
ERROR:  function update_rpt_history() does not exist
 
Questions:
(1) Can anyone tell what I am doing wrong?
(2) Why the inconsistent reporting on the presence of a function between pgAdmin3 and psql?
(3) If  I am doing something wrong, does PostgreSQL make any attempt to tell you what is
      wrong? So far, the messages have been cryptic and non-specific.
 
 

----- Original Message -----
From: "Sean Davis" <sdavis2@mail.nih.gov>
To: "Van Ingen, Lane" <lvaningen@ESNCC.com>
Sent: Monday, May 16, 2005 5:21 PM
Subject: Re: [NOVICE] Writing First Trigger Function in PostgreSQL


>
> ----- Original Message -----
> From: "Van Ingen, Lane" <lvaningen@ESNCC.com>
> To: <pgsql-novice@postgresql.org>
> Sent: Monday, May 16, 2005 4:55 PM
> Subject: [NOVICE] Writing First Trigger Function in PostgreSQL
>
>
>> Hello all,
>>
>> I am using PostgreSQL 8.0.1 on a Windows 2003 platform.
>>
>> I am (trying to) set up a function and a related insert trigger via
>> pgAdmin3 / psql, but I am not
>> succeeding.
>>
>> I have set up a bare bones function named update_rpt_history (stripped
>> down because of trying
>> to eliminate any errors).
>> CREATE OR REPLACE FUNCTION update_rpt_history(char) RETURNS INTEGER AS $$
>> BEGIN
>>  RETURN 0;
>> END
>> $$ LANGUAGE plpgsql;
>>
>> The only message I get back from pgAdmin3 is:
>>    Query returned successfully with no result in 47 ms.
>> I don't know whether 'no result' means success or not, as it does not
>> appear in pgAdmin's list of
>> functions after executing.
>
> Remember that after creating the function in pgAdmin III, you will have to
> refresh the database meta info to have an up-to-date listing.
>
>> So, I proceeded to try to set up a trigger against it in pgAdmin3 like
>> so:
>> create trigger tg_crchist_aft_ins after insert on adns_crc_history
>>  for each row execute procedure update_rpt_history(crc)
>> and got back this response from pgAdmin3:
>>        ERROR:  function update_rpt_history() does not exist
>> Tried the command again qualified by schema name (public.) but got same
>> response.
>>
>> Tried the create trigger command in psql, but same result.
>> adns=# create trigger tg_crchist_aft_ins after insert on adns_crc_history
>> adns>  for each row execute procedure update_rpt_history(crc);
>> ERROR:  function update_rpt_history() does not exist
>>
>
> You probably need to work through one of the examples in the manual
> carefully.  From the documenation
> (http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html):
>
> "PL/pgSQL can be used to define trigger procedures. A trigger procedure is
> created with the CREATE FUNCTION command, declaring it as a function with
> no arguments and a return type of trigger. Note that the function must be
> declared with no arguments even if it expects to receive arguments
> specified in CREATE TRIGGER — trigger arguments are passed via
> TG_ARGV...."
>
> So, your trigger function cannot be declared to take arguments (no (char))
> and must return a trigger.  There are some examples there which are quite
> helpful.
>
> Also, it looks like you are trying to make an audit trail from the sounds
> of the function name (you want to save information about updates to some
> table).  There is an archive here (http://archives.postgresql.org/) and a
> search using the terms "audit trail" will give you several examples of how
> others have suggested to do this.
>
> Hope this helps.
>
> Sean
>