pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
COPY TO (query) allows no function arguments
4+ messages / 4 participants
[nested] [flat]

* COPY TO (query) allows no function arguments
@ 2021-03-10 14:48  Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
  0 siblings, 1 reply; 4+ messages in thread

From: Färber, Franz-Josef (StMUK) @ 2021-03-10 14:48 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

Hello all,

it is as simple as the subject says:

The COPY TO (query) statement does not allow the passing of function arguments - is this by intent or a bug?

Minimal working example:

CREATE OR REPLACE FUNCTION a(myparam text)
RETURNS void LANGUAGE plpgsql AS $func$
BEGIN
       COPY (SELECT myparam) TO '/tmp/a.txt';
END;
$func$

SELECT a('b');

... will fail with

SQL-Fehler [42703]: FEHLER: Spalte »myparam« existiert nicht
  Wobei: PL/pgSQL-Funktion a(text) Zeile 3 bei SQL-Anweisung

Sorry, my installation is German. This roughly translates to "column myparam does not exist".

I am using Postgres 13.


Regards,
Franz-Josef Färber

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: COPY TO (query) allows no function arguments
@ 2021-03-10 15:17  Joe Conway <mail@joeconway.com>
  parent: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
  0 siblings, 1 reply; 4+ messages in thread

From: Joe Conway @ 2021-03-10 15:17 UTC (permalink / raw)
  To: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

On 3/10/21 9:48 AM, Färber, Franz-Josef (StMUK) wrote:
> it is as simple as the subject says:
> 
> The COPY TO (query) statement does not allow the passing of function arguments –
> is this by intent or a bug?

This is not a bug -- next time if you are uncertain please try the general list
first.

In any case, you cannot use the passed in argument text as part of a simple SQL
command -- you have to build the command from text and then execute it. For example:

8<------------------------
CREATE TABLE b(id int);
INSERT INTO b VALUES(1),(2),(42);

CREATE OR REPLACE FUNCTION a(myparam text)
RETURNS void LANGUAGE plpgsql AS $func$
BEGIN
  EXECUTE $$COPY (SELECT * FROM $$ || myparam || $$) TO '/tmp/a.txt'$$;
END;
$func$;

SELECT a('b');
SELECT pg_read_file('/tmp/a.txt');
 pg_read_file
--------------
 1           +
 2           +
 42          +

(1 row)
8<------------------------

HTH,

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: COPY TO (query) allows no function arguments
@ 2021-03-10 15:45  David G. Johnston <david.g.johnston@gmail.com>
  parent: Joe Conway <mail@joeconway.com>
  0 siblings, 1 reply; 4+ messages in thread

From: David G. Johnston @ 2021-03-10 15:45 UTC (permalink / raw)
  To: Joe Conway <mail@joeconway.com>; +Cc: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

On Wed, Mar 10, 2021 at 8:17 AM Joe Conway <mail@joeconway.com> wrote:

>   EXECUTE $$COPY (SELECT * FROM $$ || myparam || $$) TO '/tmp/a.txt'$$;
>

This is not the same command as originally presented, in particular this
uses myparam as an identifier (which can never be a variable) while the
original used myparam as a value (which usually can be a variable).  That
said I'm not sure at the moment where exactly the breakdown is that results
in myparam being checked as a column name (and thus not found) instead of
as a variable name (and thus found and replaced with a literal, which
SELECT can handle just fine).

If COPY itself can be parameterized/prepared (I'm unsure and not able to
test right now) then it should be able to accept variables when used in a
function - within the rules for variable substitution.

David J.

As an aside, the format() combined with EXECUTE in pl/pgsql is much more
readable as well as makes preventing SQL injection trivial.

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: COPY TO (query) allows no function arguments
@ 2021-03-10 16:46  Tom Lane <tgl@sss.pgh.pa.us>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2021-03-10 16:46 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Joe Conway <mail@joeconway.com>; Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> If COPY itself can be parameterized/prepared (I'm unsure and not able to
> test right now) then it should be able to accept variables when used in a
> function - within the rules for variable substitution.

COPY is a utility command, so it doesn't accept parameters.  Basically
only the big four DML commands (SELECT/INSERT/UPDATE/DELETE) can take
parameters.  For everything else, you've got to build a string and
EXECUTE it, as Joe illustrated.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2021-03-10 16:46 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-10 14:48 COPY TO (query) allows no function arguments Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
2021-03-10 15:17 ` Joe Conway <mail@joeconway.com>
2021-03-10 15:45   ` David G. Johnston <david.g.johnston@gmail.com>
2021-03-10 16:46     ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox