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(at)gmail(dot)com> wrote:
> > On Tue, Nov 24, 2009 at 2:50 AM, Hannu Krosing <hannu(at)2ndquadrant(dot)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