Hi,
Currently \copy cannot span multiple lines (like any meta-command)
and cannot use psql variables whereas \g can do both.
The POC patch attached implements two meta-commands \copyfrom
and \copyto that are to COPY what \g is to any other query:
- they take the COPY query already var-expanded from the query buffer,
which must mention FROM STDIN or TO STDOUT.
- they accept an argument declaring the local data source or destination,
either a filename or a program (|command args) or empty for stdin/stdout.
By contrast \copy has a specific parser to extract the data source
or dest from its line of arguments, plus whether it's a COPY FROM or TO,
and build a COPY query from that.
Examples of use
1. $ psql -v filename="/path/data-$(date -I).csv"
COPY (SELECT *
FROM table
WHERE ...)
TO STDOUT (FORMAT csv) \copyto :filename
2. -- copy only the first 100 lines
COPY table FROM stdin \copyfrom |head -n 100 /data/datafile.txt
3. $ cat script.sql
COPY table1 FROM stdin; -- copy inline data
data line
data line
\.
-- copy data from psql's stdin
COPY table2 FROM stdin \copyfrom
# copy both in-script and out-of-script data
$ psql -f script.sql < table2.data
Comments? Does that look useful as an alternative to \copy ?
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite