TEMP tables in FUNCTION - 09-01-2006 , 07:03 AM
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
CREATE TEMPORARY TABLE tbl_temp (field1...) ON COMMIT DROP;
EXECUTE 'INSERT INTO tbl_temp (...) SELECT ...';
OPEN all_rec FOR EXECUTE 'SELECT * FROM tbl_Prod';
FETCH all_rec INTO my_rec;
EXIT WHEN NOT FOUND;
UPDATE tbl_temp SET ... WHERE field1 = my_rec.fieldx;
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
Last edited by Roebie : 09-01-2006 at 07:07 AM .
Re: TEMP tables in FUNCTION - 09-01-2006 , 10:23 AM
Roebie <Roebie.2dgii0 (AT) no-mx (DOT) forums.yourdomain.com.au> wrote:
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.
09-01-2006 , 11:19 AM
Thank you very much, Laurenz!
09-04-2006 , 03:05 AM
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
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
Re: TEMP tables in FUNCTION - 09-04-2006 , 06:26 AM
Roebie <Roebie.2dlrdz (AT) no-mx (DOT) forums.yourdomain.com.au> wrote:
EXECUTE 'SELECT col FROM tab WHERE booboo INTO v1';
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:
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.
09-04-2006 , 09:44 AM
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: