pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedCOPY 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