From: | Corey Huinker <corey(dot)huinker(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Craig Ringer <craig(at)2ndquadrant(dot)com>, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>, Merlin Moncure <mmoncure(at)gmail(dot)com>, David Fetter <david(at)fetter(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PG Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Allow COPY to use parameters |
Date: | 2016-05-27 16:36:42 |
Message-ID: | CADkLM=fdVr0SLhpYwoT1JMucjLGivVehtCPCbrMaMisH3fgi8g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
>
>
> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Vladimir Borodin | 2016-05-27 16:57:34 | Re: 9.4 -> 9.5 regression with queries through pgbouncer on RHEL 6 |
Previous Message | Tom Lane | 2016-05-27 16:06:40 | Re: PATCH: pg_restore parallel-execution-deadlock issue |