X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7687952862 for ; Tue, 16 Aug 2005 02:53:17 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 60454-10 for ; Tue, 16 Aug 2005 05:53:14 +0000 (GMT) Received: from mailserv.pune.wibhu.com (unknown [210.212.168.94]) by svr1.postgresql.org (Postfix) with ESMTP id B7BC7528AD for ; Tue, 16 Aug 2005 02:53:06 -0300 (ADT) Received: from Akshay ([192.168.1.136]) by mailserv.pune.wibhu.com (8.12.8/8.12.5) with ESMTP id j7G5nrs0028033; Tue, 16 Aug 2005 11:20:10 +0530 From: "Akshay Mathur" To: "'Thomas F. O'Connell'" Cc: Subject: Re: sql function: using set as argument Date: Tue, 16 Aug 2005 11:23:06 +0530 Message-ID: <005901c5a226$cd42a100$a601a8c0@pune.wibhu.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_005A_01C5A254.E6FADD00" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 In-Reply-To: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.19 required=5 tests=[HTML_90_100=0.189, HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200508/161 X-Sequence-Number: 22388 This is a multi-part message in MIME format. ------=_NextPart_000_005A_01C5A254.E6FADD00 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Thanks! Thomas Actually I am going to use many functions for different counts and a wrapper function to return all count in one shot. My function1 is going to be a part of all count functions. If I use it as sub query in all the functions, performance degrades drastically, as my query of finction1 is also heavy. My wrapper function is going to look like: Get_all_counts() Select count1(result_set_of_finction1, int, int), count2(result_set_of_finction1, int, int), count3(result_set_of_finction1, int, int)... Regards, akshay --------------------------------------- Akshay Mathur SMTS, Product Verification AirTight Networks, Inc. ( www.airtightnetworks.net) O: +91 20 2588 1555 ext 205 F: +91 20 2588 1445 -----Original Message----- From: Thomas F. O'Connell [mailto:tfo@sitening.com] Sent: Tuesday, August 16, 2005 3:39 AM To: Akshay Mathur Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] sql function: using set as argument Is there a reason not to build it in as a sub-query? E.g., if you have a function get_count( int ): SELECT count(b_column) FROM some_table WHERE some_field_1 in ( SELECT a_column FROM a_table WHERE some_condition ) AND some_field_2 = $2; -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your iT http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) On Aug 10, 2005, at 9:30 AM, Akshay Mathur wrote: Hi, I have a function that returns a set. Looks like: Select a_column from a_table where some_condition; I want to use output of this function as an argument of another function Second looks like: Get_count(result_set_of_function_1, int) Select count(b_column) from some_table where some_field_1 in ($1) and some_field_2 = $2; Please suggest how can I do that? Regards, akshay --------------------------------------- Akshay Mathur SMTS, Product Verification AirTight Networks, Inc. ( www.airtightnetworks.net) O: +91 20 2588 1555 ext 205 F: +91 20 2588 1445 ------=_NextPart_000_005A_01C5A254.E6FADD00 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Thanks! = Thomas

 

Actually I am going to use many = functions for different counts and a wrapper function to return all count in one = shot. My function1 is going to be a part of all count functions. If I use it as = sub query in all the functions, performance degrades drastically, as my query of finction1 is also heavy.

 

My wrapper function is going to = look like:

        = ;    Get_all_counts()

        = ;            =     Select count1(result_set_of_finction1, int, int), = count2(result_set_of_finction1, int, int), count3(result_set_of_finction1, int, = int)…….

 

 

Regards,

 

akshay

 

-----------------= ----------------------

Akshay Mathur

SMTS, Product = Verification

AirTight = Networks, Inc. (= www.airtightnetworks.net)

O: +91 20 2588 1555 ext = 205

F: +91 20 2588 1445

 

-----Original = Message-----
From: Thomas F. O'Connell [mailto:tfo@sitening.com]
Sent: Tuesday, August 16, = 2005 3:39 AM
To: Akshay Mathur
Cc: = pgsql-sql@postgresql.org
Subject: Re: [SQL] sql = function: using set as argument

 

Is there a reason not to build it in as a = sub-query?

 

E.g., if you have a function get_count( int = ):

 

SELECT count(b_column)<= /span>

FROM some_table

WHERE some_field_1 in (

    SELECT a_column

    FROM = a_table

    = WHERE some_condition

)

AND some_field_2 =3D = $2;

 

--=

Thomas F. O'Connell

Co-Founder, Information Architect

Sitening, LLC

 

Strategic Open Source: Open Your i™

 

110 30th Avenue North, Suite 6

Nashville, TN 37203-6320

615-469-5150<= o:p>

615-469-5151 (fax)

 

On Aug 10, 2005, at 9:30 AM, Akshay Mathur = wrote:



Hi,

 

I have a function that returns a set. =

Looks = like:

        = ;    Select a_column from a_table where = some_condition;

 

I want to use output of this function as an argument = of another function

        = ;    Second looks like:

        = ;            =     Get_count(result_set_of_function_1, = int)

        = ;            =             &= nbsp;   Select count(b_column) from some_table where = some_field_1 in ($1) and some_field_2 =3D $2;

 

Please suggest how can I do = that?

 

Regards,

 

akshay<= o:p>

 

 

-----------------------------------= ----

Akshay Mathur

SMTS, Product = Verification

AirTight = Networks, Inc. (= www.airtightnetworks.net)

O: +91 20 2588 1555 ext = 205

F: +91 20 2588 = 1445

 

 

------=_NextPart_000_005A_01C5A254.E6FADD00--