How do I automate multiple CSV exports with FOR loop in PostgreSQL?

Question

I'm trying to export a series of csv files by symbol

CREATE OR REPLACE FUNCTION exportcrypto()
  RETURNS void AS
$func$
  declare
    irec record;
BEGIN
FOR irec IN
    SELECT DISTINCT symbol FROM daily
    LOOP
   EXECUTE 
   'COPY (SELECT o as open, h as high, l as low, c as close, v as volume, 0.0 as dividend, 1.0 as split FROM daily WHERE symbol =' || irec.symbol || ')
   TO "C:/Users/d/Documents/sdata/db_exports/crypto/' || irec.symbol || '.csv" WITH DELIMITER "," CSV HEADER;'
END LOOP;
END
$func$  
LANGUAGE plpgsql;

I'm getting a variety of errors like syntax at END LOOP or at TO and have tried variations of the copy string.

Bohemian · Accepted Answer · 2022-04-06 18:40:25Z

1

Replace double quotes with single quotes and delete the semicolon, and unless symbol is numeric (assumed not), add quotes around that too:

EXECUTE 
'COPY (SELECT o as open, h as high, l as low, c as close, v as volume, 0.0 as dividend, 1.0 as split FROM daily WHERE symbol = ''' || irec.symbol || ''') TO ''C:/Users/d/Documents/sdata/db_exports/crypto/' || irec.symbol || '.csv'' WITH DELIMITER '','' CSV HEADER';

A literal single quote ' is coded as a doubled single quote ''.

Also rather than function that returns void, define it as a procedure, and LANGUAGE goes first too, so the whle thing should be:

CREATE OR REPLACE PROCEDURE exportcrypto()
LANGUAGE plpgsql
AS $func$
  declare irec record;
BEGIN
FOR irec IN SELECT DISTINCT symbol FROM daily LOOP
    EXECUTE 
'COPY (SELECT o as open, h as high, l as low, c as close, v as volume, 0.0 as dividend, 1.0 as split FROM daily WHERE symbol = ''' || irec.symbol || ''') TO ''C:/Users/d/Documents/sdata/db_exports/crypto/' || irec.symbol || '.csv'' WITH DELIMITER '','' CSV HEADER';
END LOOP;
END
$func$

edited Apr 6, 2022 at 18:40

answered Apr 6, 2022 at 17:40

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Belayer Over a year ago

@Bohemain You might want to mention that procedures are valid only in Postgres v11 or higher. The language specification can go either before or after. It is basically a preference

Collectives™ on Stack Overflow

How do I automate multiple CSV exports with FOR loop in PostgreSQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related