From: | "Jim C(dot) Nasby" <jim(at)nasby(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | FOUND in plpgsql |
Date: | 2003-04-22 18:41:22 |
Message-ID: | 20030422134122.B63828@flake.decibel.org |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)nasby(dot)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?"
From | Date | Subject | |
---|---|---|---|
Next Message | Jonathan Bartlett | 2003-04-22 19:29:55 | Re: Regexps and Indices. |
Previous Message | Brian Piatkus | 2003-04-22 18:17:55 | Regexps and Indices. |