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

function problems.

From: "Duncan Adams (DNS)" <duncan(dot)adams(at)vcontractor(dot)co(dot)za>
To: pgsql-novice(at)postgresql(dot)org
Subject: function problems.
Date: 2002-10-24 11:36:37
Message-ID: 034A824BAA3FBA4CA0CBEF1031A02F3519F209@zablv02001.vodacom.corp (view raw or flat)
Thread:
Lists: 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');



Responses

pgsql-novice by date

Next:From: Josh BerkusDate: 2002-10-24 16:49:20
Subject: Re: how to create secondary key!!
Previous:From: Simon KellyDate: 2002-10-24 11:15:46
Subject: oidin error when parsing XML documents

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