Re: Allow COPY to use parameters
| От | Craig Ringer |
|---|---|
| Тема | Re: Allow COPY to use parameters |
| Дата | |
| Msg-id | CAMsr+YFE2uOUvFbOh-B4BV_93RHf9xrtdxWZau_vf_+6KFdWmA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Allow COPY to use parameters (Andrew Gierth <andrew@tao11.riddles.org.uk>) |
| Ответы |
Re: Allow COPY to use parameters
("David G. Johnston" <david.g.johnston@gmail.com>)
|
| Список | pgsql-hackers |
On 27 May 2016 at 15:17, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
>>>>> "Merlin" == Merlin Moncure <mmoncure@gmail.com> writes:
Merlin> Note, the biggest pain point I have with COPY is not being able
Merlin> to parameterize the filename argument.
Second proof of concept attached. This goes so far as to allow
statements like:
do $$
declare t text := 'bar'; f text := '/tmp/copytest.dat';
begin copy (select t, now()) to (f) csv header; end;
$$;
Also "copy foo to $1" or "copy (select * from foo where x=$1) to $2" and
so on should work from PQexecParams or in a plpgsql EXECUTE.
(I haven't tried to parameterize anything other than the filename and
query. Also, it does not accept arbitrary expressions - only $n, '...'
or a columnref. $n and '...' can have parens or not, but the columnref
must have them due to conflicts with unreserved keywords PROGRAM, STDIN,
STDOUT. This could be hacked around in other ways, I guess, if the
parens are too ugly.)
In addition to it being generally nice to be able to send parameters to COPY (SELECT ...), I'd personally like very basic and limited parameter support for all utility statements so clients can use the v3 protocol and parameter binding without having to figure out the type of statement.
Currently users have to know "Oh, this is a utility statement and can't be parameterised, so instead of using my client driver's parameter binding I have to do string interpolation".
SQL injection detection static analysis and trace tools might complain, and it means we have to tell users to do exactly what they should otherwise never do, but there's not really a way around it right now.
To make things more complicated, some client drivers use the simple-query protocol and do client-side in-driver parameter interpolation. Like psycopg2. Such drivers cannot easily enable use of server-side binding and extended query protocol because right now they have to look at the SQL and figure out which statements can be bound server-side and which require client-side interpolation.
For drivers like PgJDBC that do support parameter binding the application programmer has to know they can't use it for some kinds of statement, and have to do string interpolation on the SQL string. Carefully, if they're doing anything with client supplied data.
IMO this is a bit of a wart in Pg, and it'd be nice to get rid of it... but I'm aware it might not be worth the server-side complexity of handling parameter binding in utility statements.
But.
People will want to be able to parameterise identifiers too. There's just no way to do that. For plannable or utility statements. You can't write
SELECT ... FROM $1;
or
COPY FROM $1 TO 'myfilename'
... and users have to know that and deal with it. By string interpolation. So even if parameter binding for literals in utility statements becomes possible it doesn't mean users never have to interpolate stuff.
I don't think it's much worse to say "you can't use parameter binding in this statement type" than it is to say "you can't use paremeter binding for identifiers". So I'm not really that excited to solve this unless there's a way to solve it _just_ for SQL expressions within utility statements like COPY (SELECT ...).
В списке pgsql-hackers по дате отправления:
Следующее
От: Emre HasegeliДата:
Сообщение: Floating point comparison inconsistencies of the geometric types