Re: Setting up functions in psql.

>
> 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