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
