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

Re: function problems.

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: "Duncan Adams (DNS)" <duncan(dot)adams(at)vcontractor(dot)co(dot)za>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: function problems.
Date: 2002-10-25 09:15:26
Message-ID: 1035537326.14168.136.camel@linda (view raw or flat)
Thread:
Lists: pgsql-novice
On Thu, 2002-10-24 at 12:36, Duncan Adams (DNS) wrote:
> I'm trying to get a audit trail going for a table but can't seem to get the
> function below working.
...
> ERROR:  control reaches end of trigger procedure without RETURN
> 
> Ok so I don't have a return in the function, but i don't want to return any
> thing.

Triggers must return something, so return NULL.

...
> create function fun_test() RETURNS opaque as '
>         BEGIN
>             INSERT INTO test (hos_nam,ser_num,loc,dep,use) VALUES
> (''a'',''b'',0,0,''c'');

              RETURN NULL;
 
>         END;
>         '
> language 'plpgsql';
> 
> create trigger tri_test before insert or update or delete on sa_host_table
                          ^^^^^^
                          AFTER
>     for each row EXECUTE PROCEDURE fun_test('that');


A BEFORE trigger must return the NEW row after possible alterations --
returning NULL will abort the update or insert, so this must be changed
to be an AFTER trigger.  The return value of an AFTER trigger is always
ignored.


-- 
Oliver Elphick                                Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Only take heed to thyself, and keep thy soul  
      diligently, lest thou forget the things which thine 
      eyes have seen, and lest they depart from thy heart 
      all the days of thy life; but teach them to thy sons, 
      and to thy sons' sons..."        Deuteronomy 4:9 


In response to

Responses

pgsql-novice by date

Next:From: Ludwig LimDate: 2002-10-25 10:34:56
Subject: Re: function problems.
Previous:From: Jason EarlDate: 2002-10-24 20:11:47
Subject: Re: [NOVICE] pg or DBI

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