X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 1CE599FB2A2 for ; Tue, 19 Sep 2006 18:22:33 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 10390-06 for ; Tue, 19 Sep 2006 18:22:20 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from mail.gmx.net (mail.gmx.de [213.165.64.20]) by postgresql.org (Postfix) with SMTP id 61DDA9FB25B for ; Tue, 19 Sep 2006 18:22:20 -0300 (ADT) Received: (qmail invoked by alias); 19 Sep 2006 21:22:16 -0000 Received: from dslb-084-057-016-185.pools.arcor-ip.net (EHLO ltmku) [84.57.16.185] by mail.gmx.net (mp037) with SMTP; 19 Sep 2006 23:22:16 +0200 X-Authenticated: #23352258 From: "Milen Kulev" To: "'Shoaib Mir'" Cc: Subject: Re: Pipelined functions in Postgres Date: Tue, 19 Sep 2006 23:22:15 +0200 Message-ID: <007b01c6dc31$ae395920$0a00a8c0@trivadis.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_007C_01C6DC42.71C22920" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 In-Reply-To: X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Importance: Normal X-Y-GMX-Trusted: 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.208 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HTML_50_60, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/260 X-Sequence-Number: 20946 This is a multi-part message in MIME format. ------=_NextPart_000_007C_01C6DC42.71C22920 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Shoaib Mir Sent: Tuesday, September 19, 2006 11:05 PM To: Milen Kulev Cc: pgsql-performance@postgresql.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 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@postgresql.org so that your message can get through to the mailing list cleanly ------=_NextPart_000_007C_01C6DC42.71C22920 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Nachricht
Hello=20 Shoaib,
I know=20 the SETOF funcitons. I want to  simulate (somehow) = producer/consumer=20 relationship with SETOF(pipelined) functions.  The first  = (producer )function generates records (just like your test_pipe = function),=20 and the second function consumers the records , produced by the = first=20 function. The second function can be rows/records producer for another = consumer=20 functions e.g. it should looks like(or similar)
select=20 * from consumer_function(  producer_function(param1, param2,=20 ...));
 
What I=20 want to achieve is to impelement some ETL logic=20 in consumer_functions (they could be chained, of=20 course).
The=20 main idea is to read source  DWH tables once (in =  producer_function,=20 for example), and to process the rowsets
in the=20 consumer functions. I want to avoid writing to intermediate = tables=20 while performing ETL processing .
Is=20 this possible with = SETOF functions=20 ?
 
Best=20 Regards
Milen=20
-----Original Message-----
From:=20 pgsql-performance-owner@postgresql.org=20 [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of = Shoaib=20 Mir
Sent: Tuesday, September 19, 2006 11:05 PM
To: = Milen=20 Kulev
Cc: = pgsql-performance@postgresql.org
Subject: Re:=20 [PERFORM] Pipelined functions in Postgres

I think=20 pipelined functions are code you can pretend is a database table. =

For=20 example you can do it like this in Oracle:

select * from = PLSQL_FUNCTION;

You can=20 achieve something similar in PostgreSQL using RETURN SETOF functions = like=20 this:

CREATE OR REPLACE = FUNCTION=20 test_pipe (int)
    RETURNS SETOF RECORD = AS$$DECLARE  =  =20 v_rec    RECORD;
BEGIN
    FOR temp_rec IN (SELECT col = FROM=20 table where col > 10)
    LOOP  =  =20     RETURN NEXT v_rec;
    END LOOP;  =  =20 RETURN;
END;
$$ LANGUAGE plpgsql;

This = function=20 can be called like this:

SELECT * FROM=20 test_pipe(10) AS tbl (col int);
 
Hope this=20 helps...

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

On 9/20/06, Milen=20 Kulev <makulev@gmx.net>=20 wrote:
Hello=20 Lister,
I am curios whether I can emulate the Oracle pipelined = functions=20 functionality in PG too (using RETURN NEXT ). For = more
information and=20 examples about Oracle pipelined functions see:
http://asktom.o= racle.com/pls/ask/f?p=3D4950:8:8127757633768425921::NO::F4950_P8_DISPLAYI= D,F4950_P8_CRITERIA:4447489221109

I=20 have used  pipeline functions in DWH = enviromnent  with=20 success and would like
To use similar concept in PG too. =

Any=20 help, examples , links and  shared experiences would be = greately=20 appreciated.

Best=20 Regards.
Milen


---------------------------(end of=20 broadcast)---------------------------
TIP 1: if posting/reading = through=20 Usenet, please send an appropriate =
      =20 subscribe-nomail command to majordomo@postgresql.org so = that=20 your
       message can get through = to the=20 mailing list cleanly



------=_NextPart_000_007C_01C6DC42.71C22920--