Thread: Setting up functions in psql.
In setting up some functions to load data from a csv file, I'm doing the following in psql on Weendoze: AutoDRS=# CREATE OR REPLACE FUNCTION "fnLoadAppraisals"() AutoDRS-# RETURNS void AS AutoDRS-# $BODY$ AutoDRS$# DROP TABLE IF EXISTS appraisals_temp_load; AutoDRS$# CREATE TABLE appraisals_temp_load AS SELECT * FROM appraisals WHERE 1=0; AutoDRS$# TRUNCATE TABLE appraisals; AutoDRS$# COPY appraisals_temp_load FROM 'c:/temp/autodrs_appraisal.txt' WITH DELIMITER AS '^' CSV HEADER; AutoDRS$# INSERT INTO appraisals (SELECT DISTINCT ON (dealer_id,appraisal_id) * FROM appraisals_temp_load); AutoDRS$# DROP TABLE IF EXISTS appraisals_temp_load; AutoDRS$# $BODY$ AutoDRS-# LANGUAGE 'sql' VOLATILE; ERROR: relation "appraisals_temp_load" does not exist CONTEXT: SQL function "fnLoadAppraisals" AutoDRS=# ALTER FUNCTION "fnLoadAppraisals"() OWNER TO "AutoDRS"; ERROR: function fnLoadAppraisals() does not exist I can see why the error occurs, the table "appraisals_temp_load" is being created and then deleted - I don't leave it in the database. 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? Should I be doing this in a different way? (Yes I know it's easy enough to just create the table before creating the function, I'm just curious as to why it should fail) 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? -- Paul Lambert Database Administrator AutoLedgers
Paul Lambert <paul.lambert@autoledgers.com.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
Tom Lane wrote: > Paul Lambert <paul.lambert@autoledgers.com.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
2007/2/16, Paul Lambert <paul.lambert@autoledgers.com.au>: > In setting up some functions to load data from a csv file, I'm doing the > following in psql on Weendoze: > > AutoDRS=# CREATE OR REPLACE FUNCTION "fnLoadAppraisals"() > AutoDRS-# RETURNS void AS > AutoDRS-# $BODY$ > AutoDRS$# DROP TABLE IF EXISTS appraisals_temp_load; > AutoDRS$# CREATE TABLE appraisals_temp_load AS SELECT * FROM > appraisals WHERE 1=0; > AutoDRS$# TRUNCATE TABLE appraisals; > AutoDRS$# COPY appraisals_temp_load FROM > 'c:/temp/autodrs_appraisal.txt' WITH DELIMITER AS '^' CSV HEADER; > AutoDRS$# INSERT INTO appraisals (SELECT DISTINCT ON > (dealer_id,appraisal_id) * FROM appraisals_temp_load); > AutoDRS$# DROP TABLE IF EXISTS appraisals_temp_load; > AutoDRS$# $BODY$ > AutoDRS-# LANGUAGE 'sql' VOLATILE; > ERROR: relation "appraisals_temp_load" does not exist > CONTEXT: SQL function "fnLoadAppraisals" > AutoDRS=# ALTER FUNCTION "fnLoadAppraisals"() OWNER TO "AutoDRS"; > ERROR: function fnLoadAppraisals() does not exist > > I can see why the error occurs, the table "appraisals_temp_load" is > being created and then deleted - I don't leave it in the database. > > 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? > > Should I be doing this in a different way? > > (Yes I know it's easy enough to just create the table before creating > the function, I'm just curious as to why it should fail) In instead of droping and creating the table at each function execution you could create the table only once out of the function and then truncate it inside the function. Regards, -- Clodoaldo Pinto Neto
> > 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. > This is caused by the fact that the function remembers OIDs once it's parsed. So once it reaches the COPY, the original table (with the OID 18072) does not exist (the new table has a different one). This is a feature, not a bug! You can bypass this using dynamic SQL, ie. use EXECUTE 'DROP ...'; EXECUTE 'CREATE ...'; instead of plain DROP / CREATE. Dynamic SQL could be a performance issue in some cases (as the query has to be parsed each time it's executed) but this probably is not the case. Tomas
Tomas Vondra wrote: > >> >> 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. >> > This is caused by the fact that the function remembers OIDs once it's > parsed. So once it reaches the COPY, the original table (with the OID > 18072) does not exist (the new table has a different one). This is a > feature, not a bug! You can bypass this using dynamic SQL, ie. use > > EXECUTE 'DROP ...'; > EXECUTE 'CREATE ...'; > > instead of plain DROP / CREATE. Dynamic SQL could be a performance > issue in some cases (as the query has to be parsed each time it's > executed) but this probably is not the case. > > Tomas > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > > I've got 35 tables that need to be reloaded in this way and I'd rather not have to leave 35 extra tables lying around, (per someone else's suggestion of leaving them there) I'll give execute a try on Monday when I'm back in work and see if that solves my problems. These functions will only need to be run once every six to nine months (if even that often) and will be done whilst database access is removed so performance is not a problem during the loading process. Cheers for the pointer. P. -- Paul Lambert Database Administrator AutoLedgers
On Feb 16, 12:06 am, paul.lamb...@autoledgers.com.au (Paul Lambert) wrote: > Tom Lane wrote: > > Paul Lambert <paul.lamb...@autoledgers.com.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