[SOLVED] COPY with dynamic file name

COPY with dynamic file name

I am trying to write a function to load csv data into a table. I want the input argument to be the path to the file.

CREATE OR REPLACE FUNCTION public.loaddata(filepathname varchar)
  RETURNS void AS
$BODY$
BEGIN
COPY climatedata(
    climatestationid, 
    date,
    prcp,
    prcpqflag,
    prcpmflag,
    prcpsflag,
    tmax,
    tmaxqflag,
    tmaxmflag,
    tmaxsflag,
    tmin,
    tminqflag,
    tminmflag,
    tminsflag)
  FROM $1
  WITH csv header;
END;
$BODY$
  LANGUAGE plpgsql;

When I try to create this function I get:

syntax error at $1

What's wrong with it?

Solution

COPY does not allow variable substitution. That's only possible with the core DML commands SELECT, INSERT, UPDATE, and DELETE. See:

Error when setting n_distinct using a plpgsql variable

You need dynamic SQL with EXECUTE:

CREATE OR REPLACE FUNCTION loaddata(filepathname text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format ('
   COPY climatedata(climatestationid, date, ..., tminsflag)  -- more columns 
   FROM %L (FORMAT CSV, HEADER)'  -- modern syntax
           -- WITH CSV HEADER'    -- tolerated legacy syntax
   , $1);  -- pass 1st function parameter (filepathname) to format() 
END
$func$;

format() requires PostgreSQL 9.1+.
Pass the file name without additional (escaped) single-quotes:

SELECT loaddata('/absolute/path/to/my/file.csv')

format() with %L quotes the file name safely. Would be susceptible to SQL injection without it.