PostgreSQL General Bits: "You can create and use temporary tables inside of pl/pgsql functions, however, there is a catch. pl/pgsql functions are parsed when they are first run. Subsequent calls use the same query structures to run. If one of those queries accesses a temporary table created and dropped in the function it will work the first time, but not the second. The second time it is called, it will still be trying to access the first invocation's temporary table, not the newly created one.

The workaround/proper way to handle this situation is to perform queries which access temporary tables in plpgsql functions by using EXECUTE. By using EXECUTE, the query is reparsed and the proper (new) temp table is accessed.

-- This will not work
...
CREATE TEMP TABLE foo AS SELECT ....;
FOR r IN SELECT * FROM foo
LOOP
....
END LOOP;
DROP TABLE foo;

-- This will work
...
CREATE TEMP TABLE foo AS SELECT ....;
FOR r IN EXECUTE 'SELECT * FROM foo'
LOOP
....
END LOOP;
DROP TABLE foo;
..."