Re: using trigger (plpgsql) on table with default value in not null field.(solved) - Mailing list pgsql-admin

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
functioncode which I edited. I have now rectified the code and all is well. I would like to apologize to the mailing
listfor not rechecking my work, bandwidth and time. 

Allan.

----- Original Message ----
From: Allan Kamau <kamauallan@yahoo.com>
To: pgsql-admin@postgresql.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