From akshay.mathur@airtightnetworks.net Wed Aug 10 14:30:30 2005 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 E80F152AF8 for ; Wed, 10 Aug 2005 11:30:30 -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 60423-02 for ; Wed, 10 Aug 2005 14:30:20 +0000 (GMT) Received: from mailserv.pune.wibhu.com (unknown [210.212.168.94]) by svr1.postgresql.org (Postfix) with ESMTP id DEBBE52A04 for ; Wed, 10 Aug 2005 11:30:11 -0300 (ADT) Received: from Akshay ([192.168.1.110]) by mailserv.pune.wibhu.com (8.12.8/8.12.5) with ESMTP id j7AERirw021029 for ; Wed, 10 Aug 2005 19:57:50 +0530 From: "Akshay Mathur" To: Subject: sql function: using set as argument Date: Wed, 10 Aug 2005 20:00:24 +0530 Message-ID: <001a01c59db8$0fdef360$6e01a8c0@pune.wibhu.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_001B_01C59DE6.29972F60" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.19 tagged_above=0 required=5 tests=HTML_90_100, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200508/82 X-Sequence-Number: 22310 This is a multi-part message in MIME format. ------=_NextPart_000_001B_01C59DE6.29972F60 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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_001B_01C59DE6.29972F60 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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 =3D $2;

 

Please suggest how can I do = that?

 

Regards,

 

akshay<= font size=3D2 face=3DArial>

 

 

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

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_001B_01C59DE6.29972F60-- From tfo@sitening.com Mon Aug 15 22:09:08 2005 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 B750552970 for ; Mon, 15 Aug 2005 19:09:08 -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 43418-02 for ; Mon, 15 Aug 2005 22:09:05 +0000 (GMT) Received: from window.monsterlabs.com (window.monsterlabs.com [216.183.105.176]) by svr1.postgresql.org (Postfix) with SMTP id 9A12E528D2 for ; Mon, 15 Aug 2005 19:09:04 -0300 (ADT) Received: (qmail 30402 invoked from network); 15 Aug 2005 22:09:06 -0000 Received: from pcp0012204803pcs.blairblvd.tn.nash.comcast.net (HELO ?192.168.15.104?) (69.245.49.69) by 0 with SMTP; 15 Aug 2005 22:09:06 -0000 In-Reply-To: <001a01c59db8$0fdef360$6e01a8c0@pune.wibhu.com> References: <001a01c59db8$0fdef360$6e01a8c0@pune.wibhu.com> Mime-Version: 1.0 (Apple Message framework v733) X-Priority: 3 (Normal) Content-Type: multipart/alternative; boundary=Apple-Mail-6--192346934 Message-Id: Cc: From: "Thomas F. O'Connell" Subject: Re: sql function: using set as argument Date: Mon, 15 Aug 2005 17:09:01 -0500 To: Akshay Mathur X-Mailer: Apple Mail (2.733) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.133 required=5 tests=[AWL=-0.100, HTML_FONT_BIG=0.232, HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200508/96 X-Sequence-Number: 22324 --Apple-Mail-6--192346934 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=WINDOWS-1252; delsp=yes; format=flowed 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 =3D $2; -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i=99 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 =20 > function > > Second looks like: > > Get_count(result_set_of_function_1, int) > > Select count(b_column) from =20 > some_table where some_field_1 in ($1) and some_field_2 =3D $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 > > --Apple-Mail-6--192346934 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=WINDOWS-1252
Is there a reason not to = build it in as a sub-query?

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

SELECT=A0count(b_column)
FROM some_table
WHERE some_field_1 in (
=A0=A0 =A0SELECT = a_column
=A0=A0 =A0FROM = a_table
=A0 =A0 = WHERE=A0some_condition
AND=A0some_field_2 =3D = $2;

11= 0 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,

=A0

I have a function that = returns a set.

Looks = like:

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = Select a_column from a_table where = some_condition;

=A0<= /P>

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

=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0 Second looks like:

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0 Get_count(result_set_of_function_1, = int)

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

=A0

Please suggest how can I = do that?

=A0

Regards,

=A0

akshay

=

=A0

=A0

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

Akshay = Mathur
SMTS, Product = Verification

AirTight = Networks, Inc. (<= FONT size=3D"1" color=3D"navy" face=3D"Arial">www.airtightnetworks.net)

=A0

=

= --Apple-Mail-6--192346934-- From akshay.mathur@airtightnetworks.net Tue Aug 16 05:53:17 2005 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

 

 

-----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-- From akshay.mathur@airtightnetworks.net Tue Aug 30 13:11:05 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 80B55D7793 for ; Tue, 30 Aug 2005 10:11:05 -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 54173-10 for ; Tue, 30 Aug 2005 13:10:59 +0000 (GMT) Received: from mailserv.pune.wibhu.com (unknown [210.212.168.94]) by svr1.postgresql.org (Postfix) with ESMTP id 9DFACD7BE4 for ; Tue, 30 Aug 2005 10:10:51 -0300 (ADT) Received: from Akshay ([192.168.1.151]) by mailserv.pune.wibhu.com (8.12.8/8.12.5) with ESMTP id j7UD6Ps0015324 for ; Tue, 30 Aug 2005 18:36:34 +0530 From: "Akshay Mathur" To: Subject: Re: sql function: using set as argument Date: Tue, 30 Aug 2005 18:41:14 +0530 Message-ID: <003b01c5ad64$502edaf0$9701a8c0@pune.wibhu.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_003C_01C5AD92.69E716F0" 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=[AWL=0.000, HTML_90_100=0.189, HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200509/51 X-Sequence-Number: 22598 This is a multi-part message in MIME format. ------=_NextPart_000_003C_01C5AD92.69E716F0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Found a solution Second function can be written as Get_count(anyarray, int) Select count(b_column) from some_table where some_field_1 any ($1) and some_field_2 = $2; Calling this function: Select Get_count(array(function_1), int_value) 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: Akshay Mathur [mailto:akshay.mathur@airtightnetworks.net] Sent: Wednesday, August 10, 2005 8:00 PM To: 'pgsql-sql@postgresql.org' Subject: sql function: using set as argument 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_003C_01C5AD92.69E716F0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Found a = solution

 

Second function can be written as =

        = ;    Get_count(anyarray, int)

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

 

Calling this = function:

        = ;    Select Get_count(array(function_1), int_value)

 

Regards,

 

akshay

 

 

 

-----Original = Message-----
From: Akshay Mathur [mailto:akshay.mathur@airtightnetworks.net]
Sent: = Wednesday, August 10, = 2005 8:00 PM
To: = 'pgsql-sql@postgresql.org'
Subject: sql function: = using set as argument

 

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

 

 

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

Akshay Mathur

SMTS, Product = Verification

AirTight Networks, Inc. (<= a href=3D"http://www.airtightnetworks.net/" target=3D"_new">www.airtightne= tworks.net)

O: +91 20 2588 1555 ext = 205

F: +91 20 2588 1445

 

------=_NextPart_000_003C_01C5AD92.69E716F0--