Re: RETURNING syntax for COPY

On May 8, 2013, at 1:16 PM, Tom Lane wrote:

> Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> writes:
>> On 08.05.2013 19:44, Tom Lane wrote:
>>> No there isn't; what you suggest would require FE/BE protocol
>>> extensions, making it several orders of magnitude more work than the
>>> other thing.
>
>> I'd imagine that the flow would go something like this:
>
>> BE FE
>
>> CopyInResponse
>> CopyData
>> CopyData
>> ...
>> CopyDone
>> RowDescription
>> DataRow
>> DataRow
>> CommandComplete
>
> That would require the backend to buffer the entire query response,
> which isn't a great idea. I would expect that such an operation would
> need to interleave CopyData to the backend with DataRow responses. Such
> a thing could possibly be built on COPY_BOTH mode, but it would be a lot
> of work (at both ends) for extremely debatable value.
>
> The general idea of COPY is to load data as fast as possible, so weighing
> it down with processing options seems like a pretty dubious idea even if
> the implementation were easy.

There are cases that I indeed want to load data very quickly, but I want to perform an operation on it immediately after, e.g. removing bad data that was immediately added from that copy. For instance, I do have this scenario:

WITH new_data AS (
COPY FROM ...
RETURNING id, field_to_check
)
DELETE FROM table
USING new_data
WHERE
table.id = new_data.id AND
new_data.field_to_check ~* 'bad data';

Now I can take care of that all in one step, and I know I'm only removing fields I just added. This comes up when I am importing external files from other sources where I may not necessarily want all of the rows or some of the rows contain bad data.

This also presumes that COPY works in a CTE, which I'm not sure it does (and I will do the TIAS test after I hit send on this message).

Jonathan