PostgreSQL version:
PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
OS:
Ubuntu 12.10
Linux machine0 3.5.0-26-generic #42-Ubuntu SMP Fri Mar 8 23:18:20 UTC
2013 x86_64 x86_64 x86_64 GNU/Linux
As for my case, I use this function as function in INSERT-rule of VIEW
vw_table0 (CREATE VIEW vw_table0 AS SELECT * FROM table0). For debug, I
output field 'id':
p_row (parameter of this function) - ROW of VIEW vw_table0
...
p_field0 := p_row.field0;
p_field1 := p_row.field1;
v_table0_id := NULL;
BEGIN
INSERT INTO table0 ( field0, field1 )
VALUES ( p_field0, p_field1 )
RETURNING id INTO v_table0_id;
EXCEPTION WHEN unique_violation THEN
SELECT id
INTO v_table0_id
FROM table0
WHERE field0 = p_field0 AND
field1 = p_field1;
END;
RAISE NOTICE 'id = %', v_table0_id;
...
I think, for any input data (values of p_row.field0 and p_row.field1) I
should getting v_table0_id != NULL.
But I try this code (many times):
DELETE FROM vw_table0 WHERE id = ( SELECT id FROM vw_table0 WHERE field0
= 0 AND field1 = '0' );
INSERT INTO vw_table0 ( field0, field1 ) VALUES ( 0, '0' );
and sometimes getting notice:
id = <NULL>
because (after debugging) exception of unique violation and following
SELECT id INTO v_table0_id returning NULL. That is extended description
of my trouble.
P.S. This is synthetic example, real table is big (in columns and in data).
