Thread: Re: Parameter binding for COPY TO queries

Jens-Wolfhard Schicke-Uffmann <drahflow@gmx.de> writes:
> I'd like some input on the idea of adding parameter binding support to
> queries executed as part of a COPY TO command. Is there a technical
> or philosophical reason why these queries should not contain bindable
> parameters?

It would require some rethinking of system structure.  The current
design is that plannable statements (SELECT/INSERT/UPDATE/DELETE/MERGE)
accept parameters while utility statements (everything else) don't.
This is not unrelated to the fact that plannable statements all go
through a standard parse analysis/plan/execute pipeline while
utility statements don't.

There are reasons to be skeptical of parameters in something like

    ALTER TABLE t ADD COLUMN c integer DEFAULT $1;

one being that it feels a little action-at-a-distance-y for a Param's
value to become embedded in system catalogs (and indeed the user who
wrote that might not fully grasp when the Param is going to get
evaluated).  Another is that we just don't have any infrastructure
for passing such Params down to utility statement execution.

None of those arguments apply to the sub-SELECT of a "COPY (query) TO"
command, but there's a practical matter of how to get the parameters
passed through the COPY to the sub-SELECT without opening Pandora's
box for every other kind of utility statement.

Also, if we ever did want to open that box, would something we do
specially for COPY get in the way of such a larger redesign?

So I think an actual patch for this might not be terribly large,
but it'd require a fairly deep understanding of system structure
to propose something that doesn't create a mess.

            regards, tom lane

Hi,

On 2025-02-14 10:06:13 -0500, Tom Lane wrote:
> None of those arguments apply to the sub-SELECT of a "COPY (query) TO"
> command, but there's a practical matter of how to get the parameters
> passed through the COPY to the sub-SELECT without opening Pandora's
> box for every other kind of utility statement.

I think there's already precedent - CREATE TABLE AS does accept parameters
today, and it's a utility command too:

DROP TABLE IF EXISTS foo;
CREATE TABLE foo AS SELECT $1 as a, $2 as b \bind fooval, barval \g \d foo

Is there anything stopping us from implementing COPY along the same lines as
CTAS?  There's some special case code for it, but it seems within reason,
unless we want to make dozens of commands accepting parameters...

Greetings,

Andres Freund

Andres Freund <andres@anarazel.de> writes:
> On 2025-02-14 10:06:13 -0500, Tom Lane wrote:
>> None of those arguments apply to the sub-SELECT of a "COPY (query) TO"
>> command, but there's a practical matter of how to get the parameters
>> passed through the COPY to the sub-SELECT without opening Pandora's
>> box for every other kind of utility statement.

> I think there's already precedent - CREATE TABLE AS does accept parameters
> today, and it's a utility command too:

Hmm ... yeah, now that I look, there's more pre-existing plumbing
in ProcessUtility than I remembered.  So maybe this wouldn't be too
hard after all.

            regards, tom lane