Attached is a _very_ rough patch implementing a proof-of-concept function copy_srf();
It allows you to do things like this:
# select a,c,e from copy_srf('echo 12,345,67,89,2016-01-01',true) as t(a integer, b text, c text, d text, e date);
Uses for this include:
- avoiding the pattern of creating a temp table just to select all the rows back out and then discard the table (avoidable disk activity, avoidable oid churn)
- avoiding the pattern of creating a file_fdw table in pg_temp just to drop it after one select (avoidable oid churn)
- filtering file/program input by the columns that are relevant to the user's needs.
This experiment arose from my realization that file_fdw just plugs into the externally visible portions of copy.c to do all of it's work. So why couldn't we do the same for a set returning function? Of course it wasn't as simple as that. The existing Begin/NextCopyFrom functions require the ->rel to be a valid Oid...which we won't have in this context, so I had to bypass that code and use CopyFromRawFields() directly...which isn't externally visible, hence this being a patch to core rather than an extension.
Currently the function only accepts two parameters, "filename" and "is_program". Header is always false and csv mode is always true. Obviously if we go forward on this, we'll want to add that functionality back in, but I'm holding off for now to keep the example simple and wait for consensus on future direction.
As for that future direction, we could either have:
- a robust function named something like copy_srf(), with parameters for all of the relevant options found in the COPY command
- a function that accepts an options string and parse that
- we could alter the grammar to make COPY RETURNING col1, col3, col5 FROM 'filename' a legit CTE.
Regardless of the path forward, I'm going to need help in getting there, hence this email. Thank you for your consideration.