I figured out the answer last night after sending the email but I have
to wonder whether it would make more sense for plpgsql to treat
temporary tables differently then other object due to their temporary
nature and keep any code referencing these tables dynamic.
Another related question. To do something like the following static
select
select into
v_price_selected_min, v_price_selected_max
min(conf_price), max(conf_price)
from tmp_price;
I had to use the following dynamic code:
declare r record;
...
for r in execute ''select min(conf_price) as price_selected_min,
max(conf_price) as price_selected_max from tmp_price'' loop
v_price_selected_min := r.price_selected_min;
v_price_selected_max := r.price_selected_max;
end loop;
Is there a better way to do that when I know that the result is a
singleton select (i.e. only one row is returned)? Doing the whole loop
thing is way too ugly :-)
Avi
On Sunday, Jun 8, 2003, at 14:54 America/Chicago, Ian Barwick wrote:
> On Sunday 08 June 2003 01:50, Avi Schwartz wrote:
> (...)
>> It seems that the error occurs not when creating the temporary tables,
>> but rather when attempting to insert into them.
>>
>> I am sure there is a logical explanation, although I can find it. Any
>> idea what is the problem and how I can fix it?
>
> Possibly this item from the FAQ might help:
> http://www.postgresql.org/docs/faqs/FAQ.html#4.26
>
> 4.26) Why can't I reliably create/drop temporary tables in PL/PgSQL
> functions?
>
> PL/PgSQL caches function contents, and an unfortunate side effect is
> that if a
> PL/PgSQL function accesses a temporary table, and that table is later
> dropped
> and recreated, and the function called again, the function will fail
> because
> the cached function contents still point to the old temporary table.
> The
> solution is to use EXECUTE for temporary table access in PL/PgSQL.
> This will
> cause the query to be reparsed every time