X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id A130E9FB2C8 for ; Tue, 19 Sep 2006 18:29:23 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 27746-01 for ; Tue, 19 Sep 2006 21:29:15 +0000 (UTC) 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 0014A9FB2C5 for ; Tue, 19 Sep 2006 18:29:13 -0300 (ADT) Received: (qmail invoked by alias); 19 Sep 2006 21:29:07 -0000 Received: from dslb-084-057-016-185.pools.arcor-ip.net (EHLO ltmku) [84.57.16.185] by mail.gmx.net (mp010) with SMTP; 19 Sep 2006 23:29:07 +0200 X-Authenticated: #23352258 From: "Milen Kulev" To: "'Talha Khan'" Cc: Subject: Re: Pipelined functions in Postgres Date: Tue, 19 Sep 2006 23:29:07 +0200 Message-ID: <008b01c6dc32$a35c5290$0a00a8c0@trivadis.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_008C_01C6DC43.66E52290" 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.269 tagged_above=0 required=5 tests=FORGED_RCVD_HELO, HTML_50_60, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/261 X-Sequence-Number: 20947 This is a multi-part message in MIME format. ------=_NextPart_000_008C_01C6DC43.66E52290 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Talha,=20 do you know how much memory is consumed by the SETOF function ? What happens with memory consumption of the function if=20 SELECT ename FROM emp WHERE sal > $1 returns 10 mio rows ?=20 I suppose that memory for the RECORD structure is immediately reused by = the next record. =20 Regards, Milen=20 =20 -----Original Message----- From: Talha Khan [mailto:talha.amjad@gmail.com]=20 Sent: Tuesday, September 19, 2006 11:08 PM To: Milen Kulev Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Pipelined functions in Postgres Hi Milen, =20 Pipelined function is a code that acts like a database table. =20 Inorder to use this functionality in postgres you would need to write = the function like this =20 CREATE OR REPLACE FUNCTION get_test_data (numeric) RETURNS SETOF RECORD AS $$ DECLARE temp_rec RECORD; BEGIN FOR temp_rec IN (SELECT ename FROM emp WHERE sal > $1) LOOP RETURN NEXT temp_rec; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; =20 now inorder to call this function you would write the code as follows =20 SELECT * FROM get_test_data(1000) AS t1 (emp_name VARCHAR); =20 =20 Regards Talha Amjad =20 On 9/19/06, Milen Kulev wrote:=20 Hello Lister, I am curios whether I can emulate the Oracle pipelined functions = functionality in PG too (using RETURN NEXT ). For more=20 information and examples about Oracle pipelined functions see: http://asktom.oracle.com/pls/ask/f?p=3D4950:8:8127757633768425921::NO::F4= 950_P8_DISPLAYID,F4950_P8_CRITERIA:4447489221109 =20 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_008C_01C6DC43.66E52290 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Nachricht
Talha,=20
do you=20 know how much memory is consumed by the SETOF function=20 ?
What=20 happens with memory consumption of the function if =
SELECT=20 ename FROM emp WHERE sal > $1
 returns 10 mio=20 rows ? 
I suppose=20 that memory for the RECORD structure is = immediately reused by the=20 next record.
 
Regards,=20 Milen 
  
-----Original Message-----
From: Talha Khan=20 [mailto:talha.amjad@gmail.com]
Sent: Tuesday, September 19, = 2006=20 11:08 PM
To: Milen Kulev
Cc:=20 pgsql-performance@postgresql.org
Subject: Re: [PERFORM] = Pipelined=20 functions in Postgres

Hi Milen,
 
Pipelined function is a code that acts like a database = table.
 
Inorder to use this functionality in postgres you would need to = write the=20 function like this
 
CREATE OR REPLACE FUNCTION get_test_data (numeric)
  =  =20 RETURNS SETOF RECORD AS
$$
DECLARE
    = temp_rec =20   RECORD;
BEGIN
    FOR temp_rec IN (SELECT ename = FROM=20 emp WHERE sal > $1)
    LOOP
      =  =20 RETURN NEXT temp_rec;
    END LOOP;
   =20 RETURN;
END;
$$ LANGUAGE plpgsql;
 
now inorder to call this function you would write the code as=20 follows
 
SELECT * FROM get_test_data(1000) AS t1 (emp_name=20 VARCHAR);
 
 
Regards
Talha Amjad


 
On 9/19/06, Milen=20 Kulev <makulev@gmx.net>=20 wrote:=20
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=20

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

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

Best=20 Regards.
Milen


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

------=_NextPart_000_008C_01C6DC43.66E52290--