CODEWITHSUNDEEP
sqlpostgresqlloops
Joshua
Joshua

Reputation: 752

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

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;

Upvotes: 0

Views: 135

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45750

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

Upvotes: 2

Related Questions