Alternative to \copy in psql modelled after \g

From: "Daniel Verite" <daniel(at)manitou-mail(dot)org>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Alternative to \copy in psql modelled after \g
Date: 2018-11-09 11:18:51
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


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"
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 <

Comments? Does that look useful as an alternative to \copy ?

Best regards,
Daniel Vérité
PostgreSQL-powered mailer:
Twitter: @DanielVerite

Attachment Content-Type Size
poc-patch-psql-copy-like-g.diff text/x-patch 8.3 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Haribabu Kommi 2018-11-09 11:48:02 Re: Libpq support to connect to standby server as priority
Previous Message Daniel Verite 2018-11-09 10:40:44 Re: csv format for psql