Checking status of EXECUTE statements in plpgsql

Was hacking around in some [http://www.postgresql.org/docs/8.1/interactive/plpgsql.html plpgsql] today when I was reminded of something that, to me, seems rather counter-intuitive. It has to do with the setting of the FOUND variable VS. the GET DIAGNOSTIC information by way of EXECUTE statements. Generally you would think that if PostgreSQL can determine the GET DIAGNOSTIC information (like ROW COUNT) that it could also figure out which way the FOUND variable should be set. Apparently not though.



CREATE OR REPLACE function diag_vs_found() 

RETURNS boolean 

LANGUAGE plpgsql AS $$

DECLARE

    was_found boolean := false;

    numrows  integer;

    rndsql text;

BEGIN

    rndsql := 'UPDATE actor SET actor_id = actor_id';

    EXECUTE rndsql;

    IF FOUND THEN

        was_found := true;

    END IF;



    GET DIAGNOSTICS numrows := row_count;

    RAISE NOTICE '%',numrows;



    RETURN was_found;

END

$$;



pagila=# select diag_vs_found();

NOTICE:  200

 diag_vs_found

---------------

 f

(1 row)

As you can see, the RAISE NOTICE successfully gives us the right number of rows updated, but FOUND is never set to TRUE. I’m not sure why it works like that, but it does, so it’s something to watch out for if you’re trying to test the success of your EXECUTEd statements. .