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
