FOUND in plpgsql - Mailing list 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@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?"