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

On Tue, 2009-11-24 at 02:56 -0800, Daniel Farina wrote:
> On Tue, Nov 24, 2009 at 2:51 AM, Daniel Farina <drfarina@gmail.com> wrote:
> > On Tue, Nov 24, 2009 at 2:50 AM, Hannu Krosing <hannu@2ndquadrant.com> wrote:
> >> Can't you use existing aggregate function design ?
> >>
> >> CREATE AGGREGATE name ( input_data_type [ , ... ] ) (
> >>    SFUNC = sfunc,
> >>    STYPE = state_data_type
> >>    [ , FINALFUNC = ffunc ]
> >>    [ , INITCOND = initial_condition ]
> >>    [ , SORTOP = sort_operator ]
> >> )
> >
> > Actually, yes.  I just thought that this was an idea so crazy that no
> > one would like it.

seems kind of natural choice for me - in essence this is an aggregate
function, aggregating over rows/tuples supplied to it.

> Oh, and the other elephant in the room: error handling.  How to handle
> error conditions...try/catch/finally type stuff. 

Same as current aggregates - either ignore the error, logi it and
continue, or bail out

>  Aggregates do not necessarily provide a slot for this one. 

Neither do ordinary funtions, we have no "ON ERROR DO ..." clause  for
function definitions

> I did consider using
> aggregates though, but somehow it felt to me like "I need at least a
> three-tuple, why not fish around for any random bundling of three
> functions..."

Why do you need three ? 

> After all, I would not want to actually call the nodeAgg stuff to
> apply the function anyway...so it'd basically be abused as a
> three-tuple of functions.

Actually it would be best if it could use straight generic funtions, so
you could do something like

COPY stdin TO filterfunc(int) TO avg(int);

You can bypass using nodeAgg in your own C functions as an optimisation.

> Also, what if you wanted, say, replace the mechanism for COPY TO
> 'file'?  It'd be nice to make the following interaction (which uses
> some implied global variables) not use such global variables:
> 
> BEGIN;
> select open_file('/tmp/file', 'w+');
> copy foo to function write_to_file;
> -- what happens here if COPY aborts?  Does the transaction being in
> the error state mean that files will not get closed?
> select close_file();
> COMMIT;

pass the file name in as an argument to SFUNC, open it on first call,
ignore later (if it stays the same ;)

for foreign connections use SQL-MED and pass the handle to "foreign
data"


-- 
Hannu Krosing   http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability   Services, Consulting and Training