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

Writing First Trigger Function in PostgreSQL

From: "Van Ingen, Lane" <lvaningen(at)ESNCC(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Writing First Trigger Function in PostgreSQL
Date: 2005-05-16 20:55:21
Message-ID: A3FF4275060B76459B5C08A64AE330C805C38E@twmail.esncc.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.
 
 

pgsql-novice by date

Next:From: Sean DavisDate: 2005-05-16 21:23:34
Subject: Re: Writing First Trigger Function in PostgreSQL
Previous:From: D.C.Date: 2005-05-16 20:27:54
Subject: Re: Problem with Out-of-resources error?

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