i have the following utility function, which I use to easily return the OID
of an the new row created by an INSERT query:
---
CREATE FUNCTION insert_record_return_oid(text) RETURNS int4 AS
' DECLARE
s_query ALIAS FOR $1;
oid int4;
BEGIN
EXECUTE s_query;
GET DIAGNOSTICS oid = RESULT_OID;
RETURN oid;
END;
' LANGUAGE 'plpgsql' with (ISSTRICT);
---
Which correctly returns the OID of the inserted row.
usage example:
---
insert_record_return_oid('insert into sys_states (s_state) values(''po'')
');
---
However, if I get tricky, and imbed this into a select to return the
inserted row, I get an error:
---
select * from sys_states
where oid= insert_record_return_oid('insert into sys_states (s_state)
values(''po'') ');
---
"Cannot insert duplicate key" and the insert query never happens.
This is not a problem - I just do things another way, but I was wondering
what caused this?
Cheers,
Mathew
postgresql 7.2, btw