function problems. - Mailing list pgsql-novice

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');