Re: Writing First Trigger Function in PostgreSQL

----- 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
>