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 61E549FB27F for ; Tue, 19 Sep 2006 18:05:30 -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 26549-05 for ; Tue, 19 Sep 2006 18:05:21 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.236]) by postgresql.org (Postfix) with ESMTP id 3EC2A9FB27E for ; Tue, 19 Sep 2006 18:05:22 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id i7so1648416wra for ; Tue, 19 Sep 2006 14:05:17 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=hrqO/rXiNMA4iRdM/oliwJRT0jy7Y56sl+cBI2rYTJ5zbwi/tIJgm/aBEWDmIiu6JwMM06AybWgw9a5fGuI2rZRvf5Sh41S/qnrsicPVDfgFGKppSzZ814Q+69nNdGmvhR3O+I71VGIAB6I0qsZCQNKOVs+Eh1qqdZe2m+NdjJ8= Received: by 10.90.68.15 with SMTP id q15mr5901961aga; Tue, 19 Sep 2006 14:05:17 -0700 (PDT) Received: by 10.90.81.4 with HTTP; Tue, 19 Sep 2006 14:05:17 -0700 (PDT) Message-ID: Date: Wed, 20 Sep 2006 02:05:17 +0500 From: "Shoaib Mir" To: "Milen Kulev" Subject: Re: Pipelined functions in Postgres Cc: pgsql-performance@postgresql.org In-Reply-To: <005701c6dc2c$49011fc0$0a00a8c0@trivadis.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_60349_4837437.1158699917342" References: <005701c6dc2c$49011fc0$0a00a8c0@trivadis.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.434 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_50_60, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/258 X-Sequence-Number: 20944 ------=_Part_60349_4837437.1158699917342 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 > ------=_Part_60349_4837437.1158699917342 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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@gmx.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@postgresql.org so that your
       message can get through to the mailing list cleanly




------=_Part_60349_4837437.1158699917342--