Skip site navigation (1) Skip section navigation (2)

Re: Writing First Trigger Function in PostgreSQL

From: "Sean Davis" <sdavis2(at)mail(dot)nih(dot)gov>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Writing First Trigger Function in PostgreSQL
Date: 2005-05-16 21:23:34
Message-ID: 006f01c55a5d$8417ae50$5179f345@WATSON (view raw or flat)
Thread:
Lists: pgsql-novice
----- Original Message ----- 
From: "Sean Davis" <sdavis2(at)mail(dot)nih(dot)gov>
To: "Van Ingen, Lane" <lvaningen(at)ESNCC(dot)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(at)ESNCC(dot)com>
> To: <pgsql-novice(at)postgresql(dot)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
> 



pgsql-novice by date

Next:From: Greg Sabino MullaneDate: 2005-05-16 23:00:02
Subject: Re: describe table (from perl)
Previous:From: Van Ingen, LaneDate: 2005-05-16 20:55:21
Subject: Writing First Trigger Function in PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group