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
