0

I have written a stored procedure that uses the Postgres COPY () function to dump the contents of a table into a file. I want to parameter-ize the path/filename of the target but the COPY function resists my attempts to use locally defined varchar filepath variables.

I get a syntax error on:

-- this works with a literal filepath
COPY (
    SELECT '{ "header":{"processingDate":"' || (SELECT DATE("mydate") FROM synthdata.Schedule WHERE "OBJECTID" = 1) || '" },'
) TO 'C:/Users/Public/test.json';

-- but this fails with a local varchar parameter (or variable)
DECLARE     targetFilename      varchar(200);
COPY (
    SELECT '{ "header":{"processingDate":"' || (SELECT DATE("mydate") FROM synthdata.Schedule WHERE "OBJECTID" = 1) || '" },'
) TO targetFilename;

Similarly, I am struggling to put my locally defined variables inside the COPY structure - it's almost like COPY loses the the context of the stored procedure it lives in

I am using a recent version of Postgres (downloaded July 2023) and pgadmin4

Jake Smith
  • 1
  • 1
  • Use format() to create the correct SQL string and execute this string as a piece of SQL. Because of the usage of format() can avoid the risk of SQL injection – Frank Heikens Aug 01 '23 at 19:26
  • Ok so I can use FORMAT like so to create the SQL: DECLARE targetDate date; DECLARE executeString varchar(200); executeString := FORMAT( 'COPY ( SELECT ''{ "header":{"processingDate":"%s"'' ) TO ''C:/Users/Public/test.json''; ' , targetDate); But then how do I run that SQL – Jake Smith Aug 03 '23 at 11:30

0 Answers0