COPY with dynamic file name

dynamic-sql, plpgsql, postgresql, postgresql-copy, sql

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.

Problem

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?

Original source