Plpgsql: variable concatenation in FROM clause

Question

I am new to Postgresql and struggling to create a function to loop through a variety of CSV files and load them. I can make COPY work fine with a single file, but I cannot get the FOR LOOP syntax correctly. I am trying to substitute the year number as my flies are called /path/tmp.YEAR.out.csv

Here is what I hacked:

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 generates an error with the first value ||. Therefore, I suspect that I am managing the concatenation of 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 do not think that I will use plpgsql for this. A shell script could be much more 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 

usage example:

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

Note. This way you will also get around the problem of reading files with COPY, which requires the postgres server user to have permission to read the file.