Re: using trigger (plpgsql) on table with default value in not null field.(solved)

Hi all,
The earlier email was written as a result of an oversight on my part. The cause of the problem lay in the adopted audit function code which I edited. I have now rectified the code and all is well. I would like to apologize to the mailing list for not rechecking my work, bandwidth and time.

Allan.

----- Original Message ----
From: Allan Kamau <kamauallan(at)yahoo(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Sent: Thursday, January 10, 2008 12:30:29 PM
Subject: [ADMIN] using trigger (plpgsql) on table with default value in not null field.

Hi all,
I am writing a row trigger called after insert, update and delete
operations on a table, lets call this table 'togo', the table definition
contains a not null field having a default clause. On running the trigger
(performing an insert on 'togo') I get an error which indicates that
the insert attempted to insert a value (from the NEW record returned from
function on which the trigger is based) into the not null field having
a default clause.
This could mean that the sql insert statement called after return of
the trigger did not make use of field names (even though) I had supplied
the field names in my hand written insert statement on which the insert
statement was called.

On solution (am trying out but still having managed to find out how to
do this) is maybe to use a second variable of type RECORD where I
provide the value of the default value as it's first field then append all
the fields of the (internal) NEW RECORD variable?

Perhaps my table definition and trigger definition may help explain
(included below)

drop sequence if exists data_update_audit_seq cascade;
drop table if exists data_update_audit cascade;
DROP FUNCTION IF EXISTS process_data_update_audit() cascade;
drop table if exists togo cascade;
drop sequence if exists togo_seq;
drop trigger if exists trig_process_data_update_audit on togo cascade;
create sequence data_update_audit_seq;
create table data_update_audit
(
id int not null default nextval('data_update_audit_seq')
,table_name text not null
,operation char(1) NOT NULL
,stamp timestamp NOT NULL
,userid text NOT NULL
,old_or_new char(1)not null
,row_data text null
,primary key(id)
);
CREATE OR REPLACE FUNCTION process_data_update_audit() RETURNS TRIGGER
AS $data_update_audit$
DECLARE
new2 RECORD;
BEGIN
--
-- Create a row in data_update_audit to reflect the operation
performed on emp,
-- make use of the special variable TG_OP to work out the
operation.
--
RAISE NOTICE 'TG_TABLE_SCHEMA:%,
TG_TABLE_NAME:%',TG_TABLE_SCHEMA,TG_TABLE_NAME;
IF (TG_OP = 'DELETE') THEN
INSERT INTO data_update_audit SELECT 'D', now(), user, 'O',
OLD.*;
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO data_update_audit SELECT 'U', now(), user, 'O',
OLD.*;
INSERT INTO data_update_audit SELECT 'U', now(), user, 'N',
NEW.*;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
--INSERT INTO data_update_audit SELECT 'I', now(), user,
'O', OLD.*;
INSERT INTO data_update_audit SELECT 'I', now(), user, 'N',
NEW.*;
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER
trigger
END;
$data_update_audit$ LANGUAGE plpgsql;
create sequence togo_seq;
create table togo(id int not null default nextval('togo_seq'),sname
text,hours int);
create trigger trig_process_data_update_audit after insert or update or
delete on togo
for each row execute procedure process_data_update_audit()
;

--now when I attempt to issue the command:
insert into togo(sname,hours)values('Allan',8);

--I get the error below
ERROR: invalid input syntax for integer: "I"
CONTEXT: SQL statement "INSERT INTO data_update_audit SELECT 'I',
now(), user, 'N', $1 .*"
PL/pgSQL function "process_data_update_audit" line 18 at SQL statement
test2=>

-- a quick look at the logs yields the statement below:
<2008-01-10 12:20:58.241 SAST-test-test2-INSERT>NOTICE:
TG_TABLE_SCHEMA:public, TG_TABLE_NAME:togo
<2008-01-10 12:20:58.241 SAST-test-test2-INSERT>ERROR: invalid input
syntax for integer: "I"
<2008-01-10 12:20:58.241 SAST-test-test2-INSERT>CONTEXT: SQL statement
"INSERT INTO data_update_audit SELECT 'I', now(), user, 'N', $1 .*"
PL/pgSQL function "process_data_update_audit" line 18 at SQL
statement
<2008-01-10 12:20:58.241 SAST-test-test2-INSERT>STATEMENT: insert into
togo(sname,hours)values('Allan',8);

____________________________________________________________________________________
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs

---------------------------(end of
broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

____________________________________________________________________________________
Looking for last minute shopping deals?
Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping