Re: Allow COPY to use parameters

On 27 May 2016 at 15:17, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> wrote:

> >>>>> "Merlin" == Merlin Moncure <mmoncure(at)gmail(dot)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 ...).

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services