I'm doing an EXECUTE/PERFORM to update a table, then testing FOUND to
see if the record didn't exist in which case I do an insert. Problem is,
FOUND isn't updated on an EXECUTE, and PERFORM doesn't actually run the
query!
Here's the code snippet:
statement := ''UPDATE '' || quote_ident(table_name) || '' SET last_date = ''
|| quote_literal(update_date)
|| '' WHERE project_id = '' || project_id
;
PERFORM statement;
GET DIAGNOSTICS rows = ROW_COUNT;
raise notice ''%: %'', rows, statement;
-- If no rows were modified then do the insert
IF NOT FOUND THEN
statement := ''INSERT INTO '' || quote_ident(table_name)
|| ''(project_id, last_date) VALUES('' || project_id
|| '', '' || quote_literal(update_date) || '')''
;
PERFORM statement;
GET DIAGNOSTICS rows = ROW_COUNT;
raise notice ''%: %'', rows, statement;
As shown, if I do
stats=> select stats_set_last_update(:ProjectID, 'ec', '2/2/03');
NOTICE: 1: UPDATE email_contrib_last_update SET last_date = '2003-02-02' WHERE project_id = 8
The 1: indicates that 1 row was modified, but in fact no row was
modified. If I change the PERFORMS to EXECUTES:
stats=> select stats_set_last_update(:ProjectID, 'ec', '2/2/03');
NOTICE: 1: UPDATE email_contrib_last_update SET last_date = '2003-02-02' WHERE project_id = 8
WARNING: Error occurred while executing PL/pgSQL function stats_set_last_update
WARNING: line 55 at execute statement
ERROR: Cannot insert a duplicate key into unique index email_contrib_last_update_pkey
So it ran the update statement but doesn't think anything happened (even
though the rowcount indicates otherwise).
Finally, if I code it so that the update is done by an EXECUTE and the
insert by a PERFORM, I get this:
stats=> select stats_set_last_update(:ProjectID, 'ec', '2/2/03');
NOTICE: 1: UPDATE email_contrib_last_update SET last_date = '2003-02-02' WHERE project_id = 8
NOTICE: 1: INSERT INTO email_contrib_last_update(project_id, last_date) VALUES(8, '2003-02-02')
And the update actually happens.
On a related note, is FOUND a safe way to do this anyway? In my old
code, I did the insert first but used a SELECT WHERE NOT EXISTS so that
nothing would be inserted if a row already existed... I thought doing it
this way might be more efficient, but I suspect I'm just out-smarting
myself.
--
Jim C. Nasby (aka Decibel!) jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"