PostgreSQL loop gives error when accessing result table produced by nested function

Question

I have the following situation which generates a OID error when retriving data from the table table_generated_by_my_other_function that is generated by the first part of the loop when the nested function my_other_function() is called. How can this be fixed?

The function function my_other_function() generates a table with just one row of data with 7 columns, so could this results be stored or returned in some other way? Do I get the error becuase the table doesn't exists without a commit?

function my_other_function() works OK on its own.

CREATE OR REPLACE FUNCTION my_function() RETURNS varchar AS  $$
    DECLARE 
        row_data RECORD;
        helper int;
    BEGIN 
        EXECUTE '
            DROP TABLE IF EXISTS my_table_loop_result;
        ';
        EXECUTE '
            CREATE TABLE my_table_loop_result (
                var1 varchar, var2 varchar, var3 varchar, var4 int, 
                var5 varchar, var6 varchar, var7 int)
        ';

        FOR row_data IN SELECT var_x1, var_x2 FROM my_table_too
        LOOP
                SELECT my_other_function(row_data.var_x1, row_data.var_x2) INTO helper; --Returns "1" if OK    

                INSERT INTO my_table_loop_result(var1,var2,var3,var4,var5,var6.var7)
                SELECT 
                    var1,var2,var3,var4,var5,var6,var7
                FROM table_generated_by_my_other_function --OID ERROR!
                WHERE var1 = row_data.var1;
        END LOOP;
    RETURN 1;
    END;
$$ LANGUAGE plpgsql;

Pavel Stehule · Accepted Answer · 2012-07-19 19:20:58Z

2

8.2 is really obsolete - in this version, you should not drop temporary table ever.

PlpgSQL uses prepared statements, that contains execution plans with references to related tables. When you drop table, then saved plan is invalid - because reference is broken. New versions release plan in this case - but this is not true in 8.2. So - do upgrade or check if your table exists first and if exists then truncate it instead drop. When table doesn't exists, then create it. Other solution - use only dynamic SQL.

Some comments: you don't need dynamic SQL for CREATE TABLE or DROP TABLE.

http://archives.postgresql.org/pgsql-bugs/2002-07/msg00051.php

answered Jul 19, 2012 at 19:20

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joshua Over a year ago

I'm not quite sure what you are suggesting. However, I just tried to remove the DROP and CREATE executions entirely from the function. That is, I create and truncate the "table my_table_loop_result" before the function is run. I get the same OID error. Also, it is not a temporary table, nor is the table "table_generated_by_my_other_function".

Joshua Over a year ago

Just realized that I had one more of those DROP-CREATE executions inside the other function. Replaced it with a simple truncate and it now works OK.

Collectives™ on Stack Overflow

PostgreSQL loop gives error when accessing result table produced by nested function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related