Hi all,
I have found maybe buggy behaviour (of psql parser?) when using psql \copy with psql variable used for filename.
SQL copy is working fine:
contrib_regression=# \set afile '/writable_dir/out.csv'
contrib_regression=# select :'afile' as filename;
filename
-----------------------
/writable_dir/out.csv
(1 row)
contrib_regression=# copy (select 1) to :'afile';
COPY 1
but psql \copy is returning error:
contrib_regression=# \copy (select 1) to :'afile';
ERROR: syntax error at or near "'afile'"
LINE 1: COPY ( select 1 ) TO STDOUT 'afile';
^
when used without quotes it works, but it will create file in actual directory and name ':afile'
contrib_regression=# \copy (select 1) to :afile;
COPY 1
vagrant@nfiesta_dev_pg:~/npg$ cat :afile
1
workaround (suggested by Pavel Stěhule) is here:
contrib_regression=# \set afile '/writable_dir/out2.csv'
contrib_regression=# \set cmd '\\copy (SELECT 1) to ':afile
contrib_regression=# :cmd
COPY 1
My PG versin:
contrib_regression=# select version();
version
-------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 12.10 (Debian 12.10-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)
Best regards, Jiří Fejfar.