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
