Re: Postgres Stored Procedure Call Function Return Type OID Caching Problem

Hi Tom,

Thanks for your comment!

We do use EXECUTE in the functions for all the sql commands, things like

EXECUTE 'CREATE TEMP TABLE temp_foo
(
"foo" text
) ';

select_statement = '...';
EXECUTE select_statement' ;

insert_statement = '...';
EXECUTE select_statement' ;

However, there is one temp table we do not explicitly drop in one
function for technical reasons. And we have another function that uses a
different temp table we drop at the end of that function. However, we
did not use the cascade keyword when dropping it.

Could the usage of either or both of the temp tables cause this return
type mismatch problem? And would dropping the temp tables every time
inside the function with the cascade keyword fix the problem?

Thank you!

FC

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, January 24, 2007 5:16 PM
To: Feng Chen
Cc: imad; pgsql-odbc(at)postgresql(dot)org
Subject: Re: [ODBC] Postgres Stored Procedure Call Function Return Type
OID Caching Problem

"Feng Chen" <fchen(at)covergence(dot)com> writes:
> The problem is that I should not have to and cannot re-load the
> functions every now and then. Why would the type id change and the
> function still references to the old type id thus fails to get the
right
> results?

It's not possible in any modern version of PG to drop the function's
return type without dropping the function too. I suspect the problem
is not with the return type at all, but with some table or other type
referenced within the function body. Currently the only real solution
to that is to use EXECUTE for every SQL command that touches a transient
object, so that plpgsql won't try to cache a plan for the command.

regards, tom lane