PostgreSQL FOUND Problem
FOUND
is a global variable which exists in the plpgsql procedural language used in the PostgreSQL database. Last time I was writing some complicated procedures for moving many records to archive schema, just for having only the fresh data the main schema so it would be faster. During that I noticed a very strange error which suddenly turned out just to be a stupid mistake.
The manual says that:
A SELECT INTO statement sets FOUND true if a row is assigned, false if no row is returned.
So I used it… that way, but not with a simple query, I used EXECUTE
for some custom string created by some arguments concatenation.
Here is a very simple test sql showing the problem:
BEGIN;
CREATE TABLE test (
t TEXT NOT NULL
);
INSERT INTO test(t) VALUES ('10');
CREATE OR REPLACE FUNCTION make_test_1() RETURNS VOID AS $_$
DECLARE
temprec RECORD;
BEGIN
SELECT * FROM test INTO temprec;
IF FOUND THEN
raise notice '1 FOUND';
ELSE
raise notice '1 NOT FOUND';
END IF;
END; $_$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION make_test_2() RETURNS VOID AS $_$
DECLARE
temprec RECORD;
BEGIN
EXECUTE 'SELECT * FROM test' INTO temprec;
IF FOUND THEN
raise notice '2 FOUND';
ELSE
raise notice '2 NOT FOUND';
END IF;
END; $_$ LANGUAGE plpgsql;
select * from make_test_1();
select * from make_test_2();
ROLLBACK;
The output was amazingly terrible:
test=# select * from make_test_1();
NOTICE: 1 FOUND
make_test_1
-------------
(1 row)
test=# select * from make_test_2();
NOTICE: 2 NOT FOUND
make_test_2
-------------
(1 row)
What’s the problem?
- First function: used normal
SELECT
- Second function: used
EXECUTE
Turned out that the EXECUTE
statement doesn’t set FOUND
.
I started to think why oh why I made such an assumption that FOUND
should be set. After thinking for a long time (that wasn’t easy, I had to find in my mind why I thought that it should work).
Another example:
BEGIN;
CREATE TABLE test (
t TEXT NOT NULL
);
INSERT INTO test(t) VALUES ('10');
CREATE OR REPLACE FUNCTION make_test_3() RETURNS VOID AS $_$
DECLARE
temprec RECORD;
tmpint INTEGER := 0;
BEGIN
SELECT * FROM test INTO temprec;
GET DIAGNOSTICS tmpint = ROW_COUNT;
raise notice 'Found rows: %', tmpint;
END; $_$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION make_test_4() RETURNS VOID AS $_$
DECLARE
temprec RECORD;
tmpint INTEGER := 0;
BEGIN
EXECUTE 'SELECT * FROM test' INTO temprec;
GET DIAGNOSTICS tmpint = ROW_COUNT;
raise notice 'Found rows: %', tmpint;
END; $_$ LANGUAGE plpgsql;
select * from make_test_3();
select * from make_test_4();
ROLLBACK;
Output:
test=# select * from make_test_3();
NOTICE: Found rows: 1
make_test_3
-------------
(1 row)
test=# select * from make_test_4();
NOTICE: Found rows: 1
make_test_4
-------------
(1 row)
So… GET DIAGNOSTICS ROW_COUNT
works even when the previous query was EXECUTE
. FOUND
doesn’t work that way though it is described in the manual next to GET DIAGNOSTICS
so it was obvious for me that it should work like this.
Already checked all examples in PostgreSQL 8.4 - effect the same.