Checking Status of EXECUTE Statements in Plpgsql

Was hacking around in some [ 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. .