Re: raw output from copy

2015-07-27 10:41 GMT+02:00 Heikki Linnakangas <hlinnaka(at)iki(dot)fi>:

> On 07/27/2015 06:55 AM, Craig Ringer wrote:
>
>> On 7 July 2015 at 14:32, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>>
>>> Hi
>>>
>>> previous patch was broken, and buggy
>>>
>>> Here is new version with fixed upload and more tests
>>>
>>
>> I routinely see people trying to use COPY ... FORMAT binary to export
>> a single binary field (like an image, for example) and getting
>> confused by the header PostgreSQL adds. Or using text-format COPY and
>> struggling with the hex escaping. It's clearly something people have
>> trouble with.
>>
>> It doesn't help that while lo_import and lo_export can read paths
>> outside the datadir (and refuse to read from within it),
>> pg_read_binary_file is superuser only and disallows absolute paths.
>> There's no corresponding pg_write_binary_file. So users who want to
>> import and export a single binary field tend to try to use COPY. We
>> have functionality for large objects that has no equivalent for
>> 'bytea'.
>>
>> I don't love the use of COPY for this, but it gets us support for
>> arbitrary clients pretty easily. Otherwise it'd be server-side only
>> via local filesystem access, or require special psql-specific
>> functionality like we have for lo_import etc.
>>
>
> COPY seems like a strange interface for this. I can see the point that the
> syntax is almost there already, for both input and output. But even that's
> not quite there yet, we'd need the new RAW format. And as an input method,
> COPY is a bit awkward, because you cannot easily pass the file to a
> function, for example. I think this should be implemented in psql, along
> the lines of Andrew's original \bcopy patch.
>
> There are a couple of related psql-features here actually, that would be
> useful on their own. The first is being able to send the query result to a
> file, for a single query only. You can currently do:
>
> \o /tmp/foo
> SELECT ...;
> \o
>
> But more often than not, when I try to do that, I forget to do the last
> \o, and run another query, and the output still goes to the file. So it'd
> be nice to have a \o option that only affects the next query. Something
> like:
>
> \O /tmp/foo
> SELECT ...;
>
> The second feature needed is to write the output without any headers, row
> delimiters and such. Just the datum. And the third feature is to write it
> in binary. Perhaps something like:
>
> \O /tmp/foo binary
> SELECT blob FROM foo WHERE id = 10;
>
> What about input? This is a whole new feature, but it would be nice to be
> able to pass the file contents as a query parameter. Something like:
>
> \P /tmp/foo binary
> INSERT INTO foo VALUES (?);
>

The example of input is strong reason, why don't do it via inserts. Only
parsing some special "?" symbol needs lot of new code.

In this case, I don't see any advantage of psql based solution. COPY is
standard interface for input/output from/to files, and it should be used
there.

Regards

Pavel

>
>
> - Heikki
>
>