NEW in after insert trugger contained incorrect data

From: Brilliantov Kirill Vladimirovich <brilliantov(at)byterg(dot)ru>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: NEW in after insert trugger contained incorrect data
Date: 2014-11-14 09:53:24
Message-ID: 5465D114.70002@byterg.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello!
I use Postgre-9.3.5 on windows7 x64.
I use simple trigger for store some statistic data, it code:
SELECT field IN variable FROM table WHERE ...;
IF FOUND THEN
UPDATE table SET field = ...;
ELSE
INSERT INTO table (field) VALUES(value);
END IF;
RETURN NULL;

This trigger added as FOR EACH ROW on table2.
In table2 I insert multiple data on one insert, e.g. INSERT INTO
table2(field) VALUES(value0),(value1),(value2).

Unfortunately trigger exit with error: ERROR: record "new" has no field
"value";
Ok, for debug I add messages and what I see:
NOTICE: *** START ***: (9994,5,"2014-11-14 13:38:04.94","1970-01-02
06:02:38",0 ,6)
// insert in table2
CONTEXT: SQL statement "INSERT INTO trassa.cpu_load (device,
device_timestamp, cpu, value) VALUES(5,'1970-01-02
06:02:38',0,6),(5,'1970-01-02 06:02:38',1,0),(5,'1970-01-02
06:02:38',255,3)"
PL/pgSQL function
update_cpu_load_list(integer,integer,smallint[],smallint[]) line 19 at
EXECUTE statement
// insert in table
NOTICE: *** INSERT ***: (9994,5,"2014-11-14 13:38:04.94","1970-01-02
06:02:38", 0,6)
CONTEXT: SQL statement "INSERT INTO trassa.cpu_load (device,
device_timestamp,
cpu, value) VALUES(5,'1970-01-02 06:02:38',0,6),(5,'1970-01-02
06:02:38',1,0),(5,'1970-01-02 06:02:38',255,3)"
PL/pgSQL function
update_cpu_load_list(integer,integer,smallint[],smallint[]) line 19 at
EXECUTE statement
NOTICE: *** START ***: (38,5,0,6,"1970-01-02 06:02:38","2014-11-14
13:38:04.94" ,6,"2014-11-14 13:38:04.94",6,"1970-01-02
06:02:38","2014-11-14 13:38:04.94",6,1)

Last START incorrect because NEW contained data from previews INSERT.
Why and how can I solve this problem?
Thank you and excuse me for my bad english.

--
Best regards,
Brilliantov Kirill Vladimirovich

Responses

Browse pgsql-general by date

  From Date Subject
Next Message dineshkaarthick 2014-11-14 09:57:23 Re: Two instances of Postgres with single data directory
Previous Message Sam Saffron 2014-11-14 03:31:39 Re: [GENERAL] Performance issue with libpq prepared queries on 9.3 and 9.4