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 VOLATILE
  COST 100;
ALTER FUNCTION public.filltmaxa(character varying)
  OWNER TO postgres;

When I try to create this function I get

syntax error at $1

What's wrong with it?

回答1:

First off, you have a function name mismatch:

CREATE OR REPLACE FUNCTION public.loaddata(filepathname varchar)
...
ALTER FUNCTION public.filltmaxa(character varying)

But that's an additional problem.

Answer

You need dynamic SQL:

CREATE OR REPLACE FUNCTION loaddata(filepathname text)
  RETURNS void AS
$func$
BEGIN

EXECUTE format ('
COPY climatedata(
      climatestationid
     ,date
     ,prcp
     ,prcpqflag
     ,prcpmflag
     ,prcpsflag
     ,tmax
     ,tmaxqflag
     ,tmaxmflag
     ,tmaxsflag
     ,tmin
     ,tminqflag
     ,tminmflag
     ,tminsflag)
FROM %L
(FORMAT CSV, HEADER)', $1);  -- current syntax
--- WITH CSV HEADER', $1);   -- tolerated legacy syntax

END
$func$ LANGUAGE plpgsql;

format() requires PostgreSQL 9.1+.
This way, we can supply the file name without an extra set of (escaped) single quotes. Call:

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

This is highly susceptible to SQL injection. To safeguard against it I use format() with %L to sanitize the file name. This also takes care of the necessary enclosing single quotes.