On Sun, Oct 16, 2016 at 9:01 AM, Craig Ringer <craig.rin...@2ndquadrant.com> wrote:
> On 15 Oct. 2016 04:56, "Corey Huinker" <corey.huin...@gmail.com> wrote: > > > I would like to 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. > > > > The syntax would probably look like this (new bits in bold): > > > >> WITH my_copy AS ( > >> COPY FROM 'example.csv' TO RESULT SET(c1 text, c2 integer, dummy1 > text, dummy2 text, c5 date) WITH (FORMAT CSV) > >> RETURNING c1, c2, c3 > >> ) > > Strong -1 from me on this approach. Our CTE implementation materializes > everything so this is no better than COPYing to a temp table. > > Not unless you plan to fix that (and figure out the backward compatibility > issues since the bug is documented as a feature) or implement RETURNING in > subqueries... I'd go for the function. > Well, it saves burning the oid and the pg_attribute rows. A few long running transactions can cause pg_attribute to bloat to 400GB on one of our systems - hence my wanting something like this function. If it does stay 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, neither of which mean more options for this proposed function. Would the best approach be to build in a core srf-returning function that might be deprecated once COPY is set-returning AND CTEs don't have to materialize, or to refactor what's in copy.c such that a contrib module can easily plug into it, and have copy_srf live there?
