Re: Pipelined functions in Postgres

["Shoaib Mir" <shoaibmir@xxxxxxxxx>]

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@xxxxxxx> 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@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Shoaib Mir
Sent: Tuesday, September 19, 2006 11:05 PM
To: Milen Kulev
Cc: pgsql-performance@xxxxxxxxxxxxxx
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@xxxxxxx> 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@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly