Re: COPY as a set returning function

<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On Sun, Oct 16, 2016 at 9:01 AM, Craig Ringer <span
dir="ltr"><<ahref="mailto:craig.ringer@2ndquadrant.com" target="_blank">craig.ringer@2ndquadrant.com</a>></span>
wrote:<br/><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc
solid;padding-left:1ex"><span><pdir="ltr">On 15 Oct. 2016 04:56, "Corey Huinker" <<a
href="mailto:corey.huinker@gmail.com"target="_blank">corey.huinker@gmail.com</a>> wrote:<p dir="ltr">> I would
liketo make COPY itself a SRF. That's a bit beyond my capabilities, so if that is the route we want to go, I will need
help.<br/> ><br /> > The syntax would probably look like this (new bits in bold):<br /> ><br /> >> WITH
my_copy AS (<br /> >>     COPY FROM 'example.csv' TO RESULT SET(c1 text, c2 integer, dummy1 text, dummy2 text, c5
date)WITH (FORMAT CSV)<br /> >>     RETURNING c1, c2, c3<br /> >> )</span><p dir="ltr">Strong -1 from me on
thisapproach. Our CTE implementation materializes everything so this is no better than COPYing to a temp table.<p
dir="ltr">Notunless you plan to fix that (and figure out the backward compatibility issues since the bug is documented
asa feature) or implement RETURNING in subqueries... I'd go for the function.</blockquote></div><br /></div><div
class="gmail_extra">Well,it saves burning the oid and the pg_attribute rows. A few long running transactions can cause
pg_attributeto bloat to 400GB on one of our systems - hence my wanting something like this function.<br /><br />If it
doesstay a function, we only need to implement 8 of the 12 options as parameters (FREEZE and FORCE* options don't
apply).My guess is that future options added to COPY will be more about handling output or optimizing table inserts,
neitherof which mean more options for this proposed function.<br /><br />Would the best approach be to build in a core
srf-returningfunction that might be deprecated once COPY is set-returning AND CTEs don't have to materialize, or to
refactorwhat's in copy.c such that a contrib module can easily plug into it, and have copy_srf live there?</div><div
class="gmail_extra"><br/></div><div class="gmail_extra"><br /></div><div class="gmail_extra"><br /></div></div>