PostgreSQL FOUND Problem

by Szymon Lipiński
tags: database postgresql

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?

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.