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?

I had certainly thought about that... but I needed the fast loading of the postgresql COPY command... I have a few dozen files ~500MB. — JD Long
@dirk, if I were to roll the query string in R could I then submit it as a query? I was thinking I could only do that with plain sql, not plpgsql, so I didn't even try. — JD Long
I'd just be very pedestrian, create the loop, and in each iteration read the corresponding file and then write (or append) it to db. — Dirk Eddelbuettel

Seth Robertson Seth Robertson · Accepted Answer · 2011-06-06T21:47:37

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;

plpgsql: concatenation of variable into FROM clause

2 Answers