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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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');
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2002-10-24 16:49:20 | Re: how to create secondary key!! |
Previous Message | Simon Kelly | 2002-10-24 11:15:46 | oidin error when parsing XML documents |