Re: RETURNING syntax for COPY

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

> Heikki Linnakangas <hlinnakangas@vmware.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
WHEREtable.id = new_data.id ANDnew_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
importingexternal files from other sources where I may not necessarily want all of the rows or some of the rows contain
baddata. 

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
onthis message). 

Jonathan