Search Postgresql Archives

Re: Setting up functions in psql.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Feb 16, 12:06 am, paul.lamb...@xxxxxxxxxxxxxxxxxx (Paul Lambert)
wrote:
> Tom Lane wrote:
> > Paul Lambert <paul.lamb...@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
>
> ---------------------------(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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux