Re: Setting up functions in psql.

Tomas Vondra wrote:
>
>>
>> AutoDRS=# select "fnLoadAppraisals"();
>> ERROR: relation with OID 18072 does not exist
>> CONTEXT: SQL function "fnLoadAppraisals" statement 5
>>
>> 18072 is the OID of table appraisals_temp_load
>>
>> If I run the code within the function by itself, i.e. copy and paste
>> the 6 lines of SQL int psql it runs fine... What precisely is this
>> error telling me? It's not entirely clear to me.
>>
> This is caused by the fact that the function remembers OIDs once it's
> parsed. So once it reaches the COPY, the original table (with the OID
> 18072) does not exist (the new table has a different one). This is a
> feature, not a bug! You can bypass this using dynamic SQL, ie. use
>
> EXECUTE 'DROP ...';
> EXECUTE 'CREATE ...';
>
> instead of plain DROP / CREATE. Dynamic SQL could be a performance
> issue in some cases (as the query has to be parsed each time it's
> executed) but this probably is not the case.
>
> Tomas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>

I've got 35 tables that need to be reloaded in this way and I'd rather
not have to leave 35 extra tables lying around, (per someone else's
suggestion of leaving them there) I'll give execute a try on Monday when
I'm back in work and see if that solves my problems. These functions
will only need to be run once every six to nine months (if even that
often) and will be done whilst database access is removed so performance
is not a problem during the loading process.

Cheers for the pointer.

P.

--
Paul Lambert
Database Administrator
AutoLedgers