Re: Setting up functions in psql.

Tom Lane wrote:
> Paul Lambert <paul(dot)lambert(at)autoledgers(dot)com(dot)au> 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