export import bytea from psql

Hi

This is topic, that I try to solve some longer time. I would to open
discussion before summer commitfest.

The work with binary data or long text fields (multilines) or long not only
utf8 encoded XML is not well supported by psql console. Is not hard to use
binary protocol and write custom application, but it needs some time and
some experience. Buildin support in psql can helps lot of users.

Export
=====
Last time the most conflict point is mapping bytea field to file. Has sense
or has not sense to push more bytea fields to one file? It is
implementation detail - just raise or not raise a exception when count > 1.
Mapping 1:1 is little bit cleaner, simpler from user perspective - but some
use cases are not possible and files should be joined on operation system
level. Can we find a agreement?

Design
----------

SELECT xxx
\gstore file -- use text protocol

SELECT xxx
\gbstore file -- use binary protocol

Theoretically we can support more target files in one command. But I am
thinking it is over engineering. If somebody need it, then he can write
small app in Perl, Python ...

Import
=====

There are more possible ways

1. using psql variables - we just need to write commands \setfrom
\setfrombf - this should be very simple implementation. The value can be
used more times. On second hand - the loaded variable can hold lot of
memory (and it can be invisible for user). Next disadvantage - when SQL
commands with this value fails, then the full SQL command can be written to
log (there is high risk of log bloating).

2. using psql variables with query variables - first part is same like @1.
This needs a parametric queries support. This is partially optimised first
variant - the risk of log bloating is reduced.

3. using special gexec command where query parameters can be replaced by
specified content. Some like

insert into foo values($1, $2)
\gusefiles xml:~/xx.xml bytea:~/avatar.jpg

This is one step command - so work can be faster. There is zero risk of
forgotten content in variable. On second hand the command is more complex.

The binary import with binary protocol is risky if content doesn't respect
a protocol. I don't think we need to support unknown (any) formats and
custom data types. Only known formats can be supported text, json, xml
(text or binary), bytea (text or binary). Using binary protocol for XML
format enforces automatic conversion.

Opinions? Notes?

Regards

Pavel