Re: Pipelined functions in Postgres

I dont think so that will be possible using SETOF function ...

You might have to partition the current query and this way can distribute
the full load of the query if there is too much data invovled.

Thanks,
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 9/20/06, Milen Kulev <makulev(at)gmx(dot)net> 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 ?
>
> Best Regards
> Milen
>
> -----Original Message-----
> *From:* pgsql-performance-owner(at)postgresql(dot)org [mailto:
> pgsql-performance-owner(at)postgresql(dot)org] *On Behalf Of *Shoaib Mir
> *Sent:* Tuesday, September 19, 2006 11:05 PM
> *To:* Milen Kulev
> *Cc:* pgsql-performance(at)postgresql(dot)org
> *Subject:* Re: [PERFORM] Pipelined functions in Postgres
>
> I think pipelined functions are code you can pretend is a database table.
>
> For example you can do it like this in Oracle:
>
> select * from PLSQL_FUNCTION;
>
> You can achieve something similar in PostgreSQL using RETURN SETOF
> functions like this:
>
> CREATE OR REPLACE FUNCTION test_pipe (int)
> RETURNS SETOF RECORD AS
> $$
> DECLARE
> v_rec RECORD;
> BEGIN
> FOR temp_rec IN (SELECT col FROM table where col > 10)
> LOOP
> RETURN NEXT v_rec;
> END LOOP;
> RETURN;
> END;
> $$ LANGUAGE plpgsql;
>
> This function can be called like this:
>
> SELECT * FROM test_pipe(10) AS tbl (col int);
>
> Hope this helps...
>
> Thanks,
> --
> Shoaib Mir
> EnterpriseDB (www.enterprisedb.com)
>
>
> On 9/20/06, Milen Kulev <makulev(at)gmx(dot)net> wrote:
> >
> > Hello Lister,
> > I am curios whether I can emulate the Oracle pipelined functions
> > functionality in PG too (using RETURN NEXT ). For more
> > information and examples about Oracle pipelined functions see:
> >
> > http://asktom.oracle.com/pls/ask/f?p=4950:8:8127757633768425921::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:4447489221109
> >
> > I have used pipeline functions in DWH enviromnent with success and
> > would like
> > To use similar concept in PG too.
> >
> > Any help, examples , links and shared experiences would be greately
> > appreciated.
> >
> > Best Regards.
> > Milen
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
>
>
>
>
>