pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
using trigger (plpgsql) on table with default value in not null field.
2+ messages / 1 participants
[nested] [flat]

* using trigger (plpgsql) on table with default value in not null field.
@ 2008-01-10 10:30  Allan Kamau <kamauallan@yahoo.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Allan Kamau @ 2008-01-10 10:30 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: using trigger (plpgsql) on table with default value in not null field.(solved)
@ 2008-01-10 12:32  Allan Kamau <kamauallan@yahoo.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Allan Kamau @ 2008-01-10 12:32 UTC (permalink / raw)
  To: 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 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@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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2008-01-10 12:32 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-10 10:30 using trigger (plpgsql) on table with default value in not null field. Allan Kamau <kamauallan@yahoo.com>
2008-01-10 12:32 Re: using trigger (plpgsql) on table with default value in not null field.(solved) Allan Kamau <kamauallan@yahoo.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox