Re: Error with temporary tables - Mailing list pgsql-general

Thanks.

Actually, I had read the FAQ, and was correctly using the 'EXECUTE'
form of creating a temporary table. (If that had been the problem, it
would not have been working for a while.)

It turns out, the problem was a strange one. The function was
returning a set of records and one of the elements in the record was
being set like:

  R.Field := E.Value;

Where R is the returned record and E.Value was obtained from the
temporary table. The type of both R.Field and E.Value is varchar.

This was all working fine until E.Value became some large strings
with some occasional funny characters.

The hack that solved the problem was:

  R.Field := substring(E.Value from 1);

  This is a complete hack, so I'd like to find out what is going
wrong and why this worked. For now, it keeps the system functional.

Claire


On Feb 3, 2006, at 6:01 PM, Bruce Momjian wrote:

>
> Read the FAQ.
>
> ----------------------------------------------------------------------
> -----
>
> Claire McLister wrote:
>> Hi,
>>
>>   I'm using a pgsql function  that begins by creating a temporary
>> table, does some processing, and then drops the temporary table just
>> before exiting. It has been working fine for a while now, but
>> suddenly complains for some calls that "Relation with OID" does not
>> exist, at the point when it is executing the DROP table command.
>>
>>   The general scheme is as follows:
>>
>>   CREATE FUNCTION Foo(Integer) AS
>>   '
>>     BEGIN
>>        CREATE Temporary Table Bar AS (a left outer join of two
>> tables)
>>            Do processing
>>        DROP Table Bar;
>>        RETURN 1;
>>     END
>>   '
>>
>>   This is for Postgresql version 7.4.8
>>
>>   Can someone tell me what I'm doing wrong? Should I try to use 'ON
>> COMMIT DROP' instead?
>>
>>   Thanks
>>
>> Claire
>>
>>   --
>>   Claire McLister                        mclister@zeesource.net
>>   1684 Nightingale Avenue     Suite 201
>>   Sunnyvale, CA 94087            408-733-2737(fax)
>>
>>                       http://www.zeemaps.com
>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>        choose an index scan if your joining column's datatypes do not
>>        match
>>
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square,
> Pennsylvania 19073
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster