Setting up functions in psql.

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

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

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