Re: [PATCH 4/4] Add tests to dblink covering use of COPY TO FUNCTION

On Tue, Dec 29, 2009 at 9:56 PM, Daniel Farina <drfarina(at)acm(dot)org> wrote:
> On Tue, Dec 29, 2009 at 6:48 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> I think there's clear support for a version of COPY that returns rows
>> like a SELECT statement, particularly for use with CTEs.  There seems
>> to be support both for a mode that returns text[] or something like it
>> and also for a mode that returns a defined record type.  But that all
>> seems separate from what you're proposing here, which is a
>> considerably lower-level facility which seems like it would not be of
>> much use to ordinary users, but might be of some value to tool
>> implementors - or perhaps you'd disagree with that characterization?
>>
>
> This is in the other direction: freeing COPY from the restriction that
> it can only put bytes into two places:
>
> * A network socket (e.g. stdout)
> * A file (as supseruser)

Oh, duh. Actually, that seems like a pretty solid idea.

I fear that to make this really useful we would need to define some
new SQL syntax, like:

CREATE [OR REPLACE] COPY TARGET name (STARTUP function_name, STREAM
function_name, SHUTDOWN function_name);
DROP COPY TARGET name;
GRANT USAGE ON COPY TARGET TO ...;

COPY ... TO/FROM TARGET name (generic_option_list) WITH (options);

We could define the startup function to get the parameter list as a
list of DefElems and return an internal structure of its own devising
which would then be passed to the stream and shutdown functions.

It might be possible to do this without introducing a notion of an
explicit object, but there are a couple of problems with that. First,
it requires the user to specify a lot of details on every COPY
invocation, which is awkward. Second, there's a security issue to
think about here. If we were just copying to a UDF that took a string
as an argument, that would be fine, but it's not safe to let
unprivileged users to directly invoke functions that take a
type-internal argument. Introducing an explicit object type would
allow the creation of copy targets to be restricted to super-users but
then granted out to whom the super-user chooses.

Thoughts?

...Robert