Re: Pipelined functions in Postgres - Mailing list pgsql-performance

From Jeff Davis
Subject Re: Pipelined functions in Postgres
Date
Msg-id 1158710041.30652.116.camel@dogma.v10.wvs
Whole thread Raw
In response to Re: Pipelined functions in Postgres  ("Milen Kulev" <makulev@gmx.net>)
List pgsql-performance
On Tue, 2006-09-19 at 23:22 +0200, Milen Kulev wrote:
> Hello Shoaib,
> I know the SETOF funcitons. I want to  simulate (somehow)
> producer/consumer relationship with SETOF(pipelined) functions.  The
> first  (producer )function generates records (just like your test_pipe
> function), and the second function consumers the records , produced by
> the first function. The second function can be rows/records producer
> for another consumer functions e.g. it should looks like(or similar)
> select * from consumer_function(  producer_function(param1,
> param2, ...));
>
> What I want to achieve is to impelement some ETL logic
> in consumer_functions (they could be chained, of course).
> The main idea is to read source  DWH tables once (in
>  producer_function, for example), and to process the rowsets
> in the consumer functions. I want to avoid writing to intermediate
> tables while performing ETL processing .
> Is this possible with SETOF functions ?
>

Functions cannot take a relation as a parameter.

Why not create a single function that does what you need it to do? You
can write such a function in the language of your choice, including C,
perl, PL/pgSQL, among others. That gives you a lot of power to do what
you need to do in a single pass, without passing the results on to other
functions.

If you provide an example of what you need to be able to do maybe
someone on this list knows a way to do it with one function call.

Also, I'll point out that what you want to do is very similar to using
typical relational constructs. Consider whether sub-selects or
aggregates in conjunction with set-returning functions can achieve what
you want. PostgreSQL is smart enough to only read the big table once if
possible.

Regards,
    Jeff Davis





pgsql-performance by date:

Previous
From: "Shoaib Mir"
Date:
Subject: Re: Pipelined functions in Postgres
Next
From: Markus Schaber
Date:
Subject: Re: Large tables (was: RAID 0 not as fast as