Re: raw output from copy

2016-12-05 22:45 GMT+09:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>
> There are more goals:
>
> 1. user friendly import of text or binary data - import text data (with
> psql) from file is possible - but you have to load a content to psql
> variable. For binary data you should to use workaround based on LO and
> transformation from LO to bytea.
>
> 2. user friendly export text or binary data - now, the binary data can be
> exported only via transformation to LO. The XML has very interesting
> features when is passing from/to client binary. This feature is impossible
> in psql now.
>
:
<snip>
:
>> It seems to me extend of COPY statement for this optimization is a bit
>> overkill
>> solution. Do we find out an alternative solution that we can build on
>> the existing
>> infrastructure?
>
> The advantage and sense of COPY RAW was reusing existing interface. The
> question was: How I can export/import binary data simply from psql console?
>
OK, I could get your point.

Likeky, we can implement the feature without COPY statement enhancement
by adding a special purpose function and \xxx command on psql.

Let's assume the two commands below on psql:

\blob_import <table_name> <column_name> (STDIN|<filename>)
\blob_export <query> (STDOUT|<filename>)

On \blob_import, the psql command reads the binary contents from either
stdin or file, than call a special purpose function that takes three
arguments; table name, column name and a binary data chunk.
PQexecParams() of libpq allows to deliver the data chunk with keeping
binary data format, then the special purpose function will be able to
lookup the destination table/column and construct a tuple that contains
the supplied data chunk. (I think xxxx_recv handler shall be used for
data validation, but not an element of this feature.)

On \blob_export, the psql command also set up a simple query as follows:
SELECT blob_export((<user's supplied query))
For example,
\blob_export SELECT binary_data FROM my_table WHERE id = 10 /tmp/aaa
shall be transformed to
SELECT blob_export((SELECT binary_data FROM my_table WHERE id = 10))

This function is declared as:
blob_export(anyelement) RETURNS bytea
So, as long as the user supplied query returns exactly one column and
one row, it can transform the argument to the binary stream, then psql
command receive it and dump somewhere; stdout or file.

How about your thought?

Thanks,
--
KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>