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

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.

CodePudding user response：

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$