Thread: sql function: using set as argument

sql function: using set as argument

From

"Akshay Mathur"

Date:

14 August 2005, 22:42:33

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hi,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I have a function that returns a set. </span></font><p class="MsoNormal"
style="text-indent:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">Looks
like:</span></font><pclass="MsoNormal" style="text-indent:.5in"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"><spanstyle="mso-tab-count:1">            </span>Select a_column from a_table
wheresome_condition;</span></font><p class="MsoNormal" style="text-indent:.5in"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> </span></font><pclass="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">I want to use output of this function as an argument of another function</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"><span style="mso-tab-count:1">            </span>Second looks like:</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"><span style="mso-tab-count:2">                        </span>Get_<span
class="GramE">count(</span>result_set_of_function_1,int)</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"><span style="mso-tab-count:2">                        </span><span
style="mso-tab-count:1">           </span>Select <span class="GramE">count(</span>b_column) from some_table where
some_field_1in ($1) and some_field_2 = $2;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Please suggest how can I do that?</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Regards,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><span class="GramE"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">akshay</span></font></span><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"></span></font><pclass="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"><span style="mso-spacerun:yes"> </span></span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Georgia" size="2"><span style="font-size:10.0pt;
font-family:Georgia;mso-no-proof:yes">---------------------------------------</span></font><span
style="mso-no-proof:yes"></span><pclass="MsoAutoSig" style="margin:0in;margin-bottom:.0001pt"><font color="navy"
face="Georgia"size="2"><span style="font-size:10.0pt;font-family:Georgia; 
color:navy;mso-no-proof:yes">Akshay Mathur</span></font><p class="MsoAutoSig"
style="margin:0in;margin-bottom:.0001pt"><fontcolor="navy" face="Georgia" size="1"><span
style="font-size:7.5pt;font-family:Georgia;
color:navy;mso-no-proof:yes">SMTS, Product Verification</span></font><p class="MsoNormal"><font color="navy"
face="Georgia"size="3"><span style="font-size: 
12.0pt;font-family:Georgia;color:navy;mso-no-proof:yes">AirTight Networks, Inc. </span></font><font color="navy"
face="Georgia"><spanlang="DE" style="font-family: 
Georgia;color:navy;mso-ansi-language:DE;mso-no-proof:yes">(</span></font><font color="navy" face="Arial" size="1"><span
style="font-size:9.0pt;font-family:Arial;
color:navy;mso-no-proof:yes"><a href="http://www.airtightnetworks.net/" target="_new"><font color="gray" size="2"><span
lang="DE"style="font-size:10.0pt; 
color:gray;mso-ansi-language:DE">www.airtightnetworks.net</span></font></a></span></font><font color="navy"
face="Arial"><spanlang="DE" style="font-family:Arial;color:navy; 
mso-ansi-language:DE;mso-no-proof:yes">)</span></font><font color="navy"><span lang="DE"
style="color:navy;mso-ansi-language:DE;mso-no-proof:yes"></span></font><pclass="MsoAutoSig"
style="margin:0in;margin-bottom:.0001pt"><fontcolor="navy" face="MS Sans Serif" size="2"><span
style="font-size:10.0pt;font-family:"MSSans Serif"; 
color:navy;mso-no-proof:yes">O: +91 20 2588 1555 ext 205</span></font><font color="navy" face="Georgia" size="2"><span
style="font-size:10.0pt;font-family:Georgia;
color:navy;mso-no-proof:yes"></span></font><p class="MsoAutoSig" style="margin:0in;margin-bottom:.0001pt"><font
color="navy"face="MS Sans Serif" size="2"><span style="font-size:10.0pt;font-family:"MS Sans Serif"; 
color:navy;mso-no-proof:yes">F: +91 20 2588 1445</span></font><font color="navy" face="Georgia" size="2"><span
style="font-size:10.0pt;font-family:Georgia;
color:navy;mso-no-proof:yes"></span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span
style="font-size:
12.0pt"> </span></font></div>

Re: sql function: using set as argument

From

"Thomas F. O'Connell"

Date:

15 August 2005, 22:09:13

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 i™

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

Re: sql function: using set as argument

From

"Akshay Mathur"

Date:

19 August 2005, 04:41:32

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 i™

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

Re: sql function: using set as argument

From

"Akshay Mathur"

Date:

06 September 2005, 04:24:28

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