>
>
> For the following pretend that "STRING" has the same behavior as the
> "format(...)" function.
>
> EXECUTE STRING('COPY %I TO %L', 'testtable', 'testfile.txt');‚Äč
>

+1
We should make string sanitization easy so that people use it by default.

In the mean time, if you're just using psql, the new \gexec command will
cover that

select format('COPY %I TO %L', 'testtable', 'testfile.txt')
\gexec


but it won't help with any \-commands. And it won't work for
schema-qualified table names, and if you're using COPY tab FROM PROGRAM,
you're going to have cases where %L finds an escape-y character in the
command string (like using head -n 1 and sed to unpivot a header row) which
results in an E'...' string that COPY can't handle.

For \copy, I end up doing something like

select format('\\copy %I from program %L',:'table_name','pigz -cd ' ||
:'file_name') as copy_command
\gset
:copy_command


Which won't win any beauty contests, and suffers from all the limitations I
listed earlier, but works for me.

I'm indifferent to whether these commands need to be PREPARE-able so long
as sanitization becomes a solved problem.

Reply via email to