From: Jeff Davis <pgsql@j-davis.com>
To: Milen Kulev <makulev@gmx.net>
Cc: Shoaib Mir <shoaibmir@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Pipelined functions in Postgres
Date: Tue, 19 Sep 2006 16:54:01 -0700
Message-ID: <1158710041.30652.116.camel@dogma.v10.wvs> (raw)
In-Reply-To: <007b01c6dc31$ae395920$0a00a8c0@trivadis.com>
References: <007b01c6dc31$ae395920$0a00a8c0@trivadis.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pgsql@j-davis.com, makulev@gmx.net, shoaibmir@gmail.com
Subject: Re: Pipelined functions in Postgres
In-Reply-To: <1158710041.30652.116.camel@dogma.v10.wvs>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox