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

On Sat, Dec 5, 2009 at 3:32 AM, Daniel Farina <drfarina(at)acm(dot)org> wrote:
> On Mon, Nov 30, 2009 at 12:14 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
>> Jeff Davis wrote:
>>
>> COPY target FROM FUNCTION foo() WITH RECORDS;
>>
>>
>> In what format would the records be?
>
> As a not-quite aside, I think I have a better syntax suggestion.  I
> have recently been digging around in the grammar with the changes made
> in the following commit:
>
> commit a6833fbb85cb5212a9d8085849e7281807f732a6
> Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Date:   Mon Sep 21 20:10:21 2009 +0000
>
>    Define a new, more extensible syntax for COPY options.
>
>    This is intentionally similar to the recently revised syntax for EXPLAIN
>    options, ie, (name value, ...).  The old syntax is still supported for
>    backwards compatibility, but we intend that any options added in future
>    will be provided only in the new syntax.
>
>    Robert Haas, Emmanuel Cecchet
>
> As it turns out, the following syntax may work pretty well:
>
>  COPY y TO FUNCTION (setup_function the_setup_function('some arg', 3, 7, 42))
>
> Basically the func_expr reduction fits very neatly into the
> copy_generic_opt_elem reduction:
>
>    copy_generic_opt_elem:
>                            ColLabel copy_generic_opt_arg
>                                    {
>                                            $$ = (Node *) makeDefElem($1, $2);
>                                    }
>                            | ColLabel func_expr
>                                    {
>                                            $$ = (Node *) $2;
>                                    }
>                    ;
>
> Now we can use more or less any reasonable number of symbol names and
> function calls we desire.  This makes life considerably easier, I
> think...
>
> We can also try to refactor COPY's internals to take advantage of
> these features (and potentially reduce the number of mechanisms.  For
> example, the legacy "COPY ... TO '/place' WITH CSV" perhaps can be
> more verbosely/generically expressed as:
>
>  COPY ... TO FUNCTION (setup_function to_file('/place'),
>                        record_converter csv_formatter,
>                        stream_function fwrite
>                        end_function fclose);
>
> We can also add auxiliary symbols for error handling behavior.  For
> example, were the COPY to fail for some reason maybe it would make
> sense "on_error" to call "unlink" to clean up the partially finished
> file.
>
> I also have what I think is a somewhat interesting hack.  Consider
> some of the functions up there without arguments (presumably they'd be
> called with a somewhat fixed contract the mechanics of COPY itself):
> how does one disambiguate them?  Ideally, one could sometimes use
> literal arguments (when the return value of that function is desired
> to be threaded through the other specified functions) and other times
> it'd be nice to disambiguate functions via type names.  That would
> look something like the following:
>
>  COPY ... TO FUNCTION (setup_function to_file('/place'),
>                        record_converter csv_formatter(record),
>                        stream_function fwrite(bytea),
>                        end_function fclose(text));
>
> I think this is possible to implement without much ambiguity, drawing
> on the observation that the COPY statement does not have -- and
> probably will never have -- references via Var(iable) node, unlike
> normal SQL statements such as SELECT, INSERT, et al.  That means we
> might be able disambiguate using the following rules when scanning the
> funcexpr's arguments during the semantic analysis phase to figure out
> what to do:
>
>  * Only literal list items found: it's a function call with the types
>    of those literals.  Ex: my_setup_function('foo'::text, 3)
>
>  * Only non-literal list items found: it's type specifiers.  Ex:
>    csv_formatter(record).
>
>  * Both literal and non-literal values found: report an error.
>
> This only works because no cases where a non-literal quantity could be
> confused with a type name come to mind.  If one could name a type "3"
> and being forced to double-quote "3" to get your type disambiguated
> was just too ugly, then we are at an impasse.  But otherwise I think
> this may work quite well.
>
> Common constellations of functions could perhaps be bound together
> into a DDL to reduce the amount of symbol soup going on here, but that
> seems like a pretty clean transition strategy at some later time.
> Most of the functionality could still be captured with this simple
> approach for now...
>
> Also note that factoring out high-performance implementations of
> things like csv_formatter (and friends: pg_binary_formatter) will
> probably take some time, but ultimately I think all the existing
> functionality could be realized as a layer of syntactic sugar over
> this mechanism.

I am very fuzzy on where we stand with this patch. There's a link to
the initial version on the commitfest application, but there has been
so much discussion since then that I think there are probably some
revisions to be made, though I can't say I know exactly what they are.

I did also check the git branch, but it does not merge cleanly with the master.

Is there a more recent version? Is this still under development? Or
have we decided to go a different direction with it?

Thanks,

...Robert