PostgreSQL FOUND Problem

FOUND is a global variable that exists in the plpgsql procedural language used in 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 notices a very strange error that 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 parameters concatenation, nevermind. Here is a very simple test sql that shows 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 very long thinking (that wasn’t easy, I had to find in my mind why I thought that it should work)… and suddenly there is:
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 that way.

Already checked all examples in PostgreSQL 8.4 – effect the same.

Bookmark It

Related posts:

1. How to Allow For Max 4 Rows in Update (PostgreSQL) The Problem Question found on the net: how to restrict...
2. PostgreSQL Strange Timings A very simple query. PostgreSQL 8.4. I have no idea...
3. How to check PostgreSQL privileges. Someone asked me how to list all tables that a...
4. PostgreSQL Collation – part 1 PostgreSQL is (IMHO) much better than MySQL but unfortunately it ...
5. Testing Database – Small Reply On Ovid’s blog I found lately this entry. First of...
6. PostgreSQL Collation – 8.4 In the last PostgreSQL version (8.4) there is a small...