Re: Setting up functions in psql.

On Feb 16, 12:06 am, paul(dot)lamb(dot)(dot)(dot)(at)autoledgers(dot)com(dot)au (Paul Lambert)
wrote:
> Tom Lane wrote:
> > Paul Lambert <paul(dot)lamb(dot)(dot)(dot)(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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

Hi Paul,
Already have that problem.

If I remember correctly, when you first call a function, it's compiled
so the server know exactly what/where find tables.

The function know what is the OID needed.
If you drop/create the same table name, the OID change and then the
function is not able to work anymore.

So you have 2 choices :
1- Drop/recreate the function each time. (So the function will be
recompiled each time) ...
2- Put EXECUTE in your function. ( EXECUTE will be compiled at runtime
& the function will always know what is the good OID)

anthony