Subject: Re: Pipelined functions in Postgres
From: Jeff Davis <pgsql@j-davis.com>
To: Milen Kulev <makulev@gmx.net>
Cc: 'Shoaib Mir' <shoaibmir@gmail.com>, pgsql-performance@postgresql.org
In-Reply-To: <007b01c6dc31$ae395920$0a00a8c0@trivadis.com>
References: <007b01c6dc31$ae395920$0a00a8c0@trivadis.com>
Content-Type: text/plain
Date: Tue, 19 Sep 2006 16:54:01 -0700
Message-Id: <1158710041.30652.116.camel@dogma.v10.wvs>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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