Thread: function problems.

Hi all

I'm trying to get a audit trail going for a table but can't seem to get the
function below working.

I get:

wire_dev=# \i /database/pgsql/sql_scripts/assets_work/24_10_2002/trigger.sql
DROP
DROP
DROP
CREATE
CREATE
CREATE
wire_dev=# insert into sa_host_table (hostname) values ('test');
NOTICE:  Error occurred while executing PL/pgSQL function fun_test
NOTICE:  at END of toplevel PL block
ERROR:  control reaches end of trigger procedure without RETURN
wire_dev=#

Ok so I don't have a return in the function, but i don't want to return any
thing.

p.s. at the bottom is some ting like i want in the end.

drop table test;
drop function fun_test();
drop trigger tri_test on sa_host_table;

create table test (
    hos_nam varchar,
    ser_num varchar,
    loc int,
    dep int,
    use varchar,
    date timestamp DEFAULT now()
);

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

create trigger tri_test before insert or update or delete on sa_host_table
    for each row EXECUTE PROCEDURE fun_test('that');

thanx

Duncan


goal:



drop table test;
drop function fun_test();
drop trigger tri_test on sa_host_table;

create table test (
    hos_nam varchar,
    ser_num varchar,
    loc int,
    dep int,
    use varchar,
    date timestamp DEFAULT now()
);

create function fun_test() RETURNS opaque as '
          DECLARE
              col1 TEXT;
              col2 TEXT;
              col3 INT;
              col4 INT;
              col5 TEXT;
              col6 TEXT;
          begin
              col1 := NEW.hoastname;
              col2 := NEW.serial_number;
              col3 := NEW.location;
              col4 := NEW.dept;
              col5 := select user;
        BEGIN
            INSERT INTO test (hos_nam,ser_num,loc,dep,use) VALUES
(''col1'',''col2'',col3,col4,''col5'');
        END;
        '
language 'plpgsql';

create trigger tri_test before insert or update or delete on sa_host_table
    for each row EXECUTE PROCEDURE fun_test('that');

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

Hi :

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

  All trigger functions (i.e functions returning type
opaque) must have either have "RETURN NULL","RETURN
NEW" or "RETURN OLD"


ludwig.

__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com

Oliver Elphick <olly@lfix.co.uk> writes:
> 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.

Or leave it as a BEFORE trigger and do "RETURN NEW" at the end.

I'd recommend BEFORE triggers over AFTER triggers anytime you have the
choice, because AFTER triggers go into a list of things-to-do-later;
if that list gets large (ie, you do many updates in one command) you
will have performance problems.

            regards, tom lane