Tom Lane wrote:
Paul Lambert <paul.lambert@xxxxxxxxxxxxxxxxxx> writes:
What I am confused about is: Why does the creation of a function fail if
a table it uses does not exist when the function itself is creating the
table further up to where it references it?
Because the function isn't actually being *executed*, only
syntax-checked.
The syntax precheck isn't completely reliable, for this reason among
others, so you can turn it off via check_function_bodies = off.
However, I'm not sure but what the function would fail anyway at runtime
for the same reason. I think in a SQL function, it all gets parsed
before any is executed. (This could probably get fixed, if we thought
it was worth the trouble.)
Secondly, and here's the obviously easy one that I'm having a mental
blank trying to figure out... How would I execute a function (such as
the above) from psql?
select "fnLoadAppraisals"();
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
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.
--
Paul Lambert
Database Administrator
AutoLedgers
