Re: Setting up functions in psql.

2007/2/16, Paul Lambert <paul(dot)lambert(at)autoledgers(dot)com(dot)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