Re: [SQL] sql function: using set as argument

Akshay Mathur Thu, 18 Aug 2005 21:53:26 -0700

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:[EMAIL PROTECTED]
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?