plpgsql: concatenation of variable into FROM clause

Question

I'm new to Postgresql and struggling to build a function for looping over a series of CSV files and loading them. I can make the COPY work just fine with a single file, but I'm unable to get the FOR LOOP syntax correct. I'm trying to substitute a year number as my flies are named /path/tmp.YEAR.out.csv

This is what I've hacked up:

CREATE OR REPLACE FUNCTION test() RETURNS void as $$
BEGIN
  FOR i IN 1982..1983 LOOP
    COPY myTable 
    FROM '/path/tmp.' || i::VARCHAR || '.out.csv'
    delimiters ','
  END LOOP;
END;
$$ LANGUAGE 'plpgsql';

This throws an error at the first ||. So I suspect I'm managing the concat of the variable i improperly. Any tips?

Answer 1

CREATE OR REPLACE FUNCTION test() RETURNS void as $$
BEGIN
FOR i IN 1982..1983 LOOP
  EXECUTE 'COPY myTable FROM ''/path/tmp.' || i::text
                                           || '.out.csv'' DELIMITERS '',''; ';
END LOOP;
END;
$$ language plpgsql;

Answer 2

I don't think I'd use plpgsql for that. A shell script could be much more generally useful:

#!/bin/sh

DBHOST=mydbhost
DBNAME=mydbname

files=$1
target=$2

for file in ${files}; do
    psql -h ${DBHOST} ${DBNAME} -c "\copy ${target} FROM '${file}' delimiters ','"
done

example usage:

csv2psql "$(ls *.out.csv)" someschema.tablename

Note: This way you also sidestep the problem of reading files with COPY which requires the postgres server user to have read permissions on the file.