I seem to have some troubles with the use of temporary tables inside function. This is a simplified version of my function. In reality it has several in parameters and returns a set of records being the records in the temp table.

CREATE OR REPLACE FUNCTION func_stock_forecast (...) AS
$body$
DECLARE
my_rec record;
all_rec refcursor;
BEGIN
CREATE TEMPORARY TABLE tbl_temp (field1...) ON COMMIT DROP;
EXECUTE 'INSERT INTO tbl_temp (...) SELECT ...';
OPEN all_rec FOR EXECUTE 'SELECT * FROM tbl_Prod';
LOOP
FETCH all_rec INTO my_rec;
EXIT WHEN NOT FOUND;
UPDATE tbl_temp SET ... WHERE field1 = my_rec.fieldx;
END LOOP;
CLOSE all_rec;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

I use the function like this:
SELECT * from func_stock_forecast(...) AS (...)

If I call my function like this twice in a row, the first time gives me the expected result. The second time however results in an error message saying:
ERROR: relation with OID 52267707 does not exist
CONTEXT: SQL statement "UPDATE tbl_temp SET ... WHERE ..."
PL/pgSQL function "func_stock_forecast" line 11 at SQL statement

I would have thought that my temporary table exists from the moment it is created until the function ends. That seems to be true, because otherwise creating the temp table in the second call would trigger an error. But why doesn't the table exist anymore when I want to update it further on. And most of all why does it exist in the first call.

Recompiling the function between calls is a solution, but obviously not a good one. Can anyone explain me what is causing this error and how to solve it.

I use PostgreSQL 8.0.3 on i686-pc-linux-gnu
I test my queries from EMS Manager as I don't have command line access to the database

Thanks.

Roebie <Roebie.2dgii0 (AT) no-mx (DOT) forums.yourdomain.com.au> wrote:
The reason for this behaviour is that PL/pgSQL uses prepared SQL
statements. That means that the second time an SQL statement is executed,
it is not parsed and prepared again.

In particular that means that all object names will be resolved to
object IDs (OIDs) when the statement runs for the first time, and these
OIDs are referenced in all future executions of the statement.

The second time you run the function, there is an object "tbl_temp" in
existence, but it is a different object from the first time.
The update statement is not aware of this (it was prepared when the
first "tbl_temp" was alive) and references the old temporary table
by its OID, which causes the error.

You can verify that by selecting
SELECT DISTINCT tableoid FROM tbl_temp;
after the first successful run. This will be the OID in the error message
during the second run.

So that's why it happens. But what can you do?

First, you can try to avoid using a temporary table from PL/pgSQL.
This is probably not what you want.

Second, you can turn the offending SQL statement into dynamic SQL
by using EXECUTE:

EXECUTE 'UPDATE tbl_temp SET ...
WHERE field1 = ''' || my_rec.fieldx || '''';

You will notice that you already have a dynamic SQL statement in your
function, this is the reason why there is no error in this line.

Dynamic SQL statements are prepared each time they are executed (hence
the name), which usually is undesirable because the performace suffers,
but in this case is exactly what you need.

Yours,
Laurenz Albe

But of course. Why didn't I think about that?

How did you guess?

I copied the idea for part of this function from another function I wrote a long time ago. Now I remember why I used EXECUTE in that function in the first place. Thanks for bringing it back to memory. I think I'm going to add some comment to my function...

Thank you very much, Laurenz!

I keep having a problem with dynamic SQL commands.
Just before the update command in my example I also need to do a select on my temporary table.
When I try then I get a "relation with OID xxx does not exist" error.
So, just like before I change it to dynamic SQL. According to the manual (http://www.postgresql.org/docs/8.1/i...-EXECUTING-DYN) the syntax should be So my statement becomes This gives me a new error however I suppose this has to do with the remark in the PostgreSQL manual. What is the mentionned good for then and most important how do I get around this limitation.

Roebie <Roebie.2dlrdz (AT) no-mx (DOT) forums.yourdomain.com.au> wrote:
There is two things you are mixing up:

EXECUTE 'SELECT col FROM tab WHERE booboo INTO v1';

and

EXECUTE 'SELECT col FROM tab WHERE booboo' INTO v1;

The first of the two is 'currently not supported withing EXECUTE'
according to the documentation, and there's little need for it because
of EXECUTE ... INTO.

The problem you encounter is that EXECUTE ... INTO was added in 8.1, and
you are running 8.0.3. Take a look at the documentation for 8.0:

http://www.postgresql.org/docs/8.0/s...-EXECUTING-DYN

and compare this to the current documentation.

Again, two solutions:
- Upgrade to 8.1 (again, probably not what you want).
- Use one of the workarounds described in the 8.0 documentation.

Yours,
Laurenz Albe

It seems I'm mixing up more than that...
I use 2 postgre servers : one with 8.1.x and one with 8.0.3.
I am however only using one version of the documentation through my browser favorites : the 8.1 version. This version doesn't mention any workarounds since there is nothing to work around in this version, add my syntax mixup to that and... well... here we are.

Thanks for helping me out again, Laurenz.

I've learned 2 important lessons the last few days:

1. always use the docs matching the version of your server!
2. read the docs thouroughly, the answer is somewhere in there!