Re: function problems.

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.Elphick@lfix.co.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