Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id F18CB1337BB4 for ; Wed, 13 Apr 2011 12:48:56 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 24849-10 for ; Wed, 13 Apr 2011 15:48:34 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from out02.mta.xmission.com (out02.mta.xmission.com [166.70.13.232]) by mail.postgresql.org (Postfix) with ESMTP id CF5AF1337B2F for ; Wed, 13 Apr 2011 12:48:32 -0300 (ADT) Received: from in01.mta.xmission.com ([166.70.13.51]) by out02.mta.xmission.com with esmtps (TLSv1:AES256-SHA:256) (Exim 4.69) (envelope-from ) id 1QA2Iw-0008OA-CB; Wed, 13 Apr 2011 09:48:30 -0600 Received: from [208.187.158.94] (helo=[172.16.1.234]) by in01.mta.xmission.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.69) (envelope-from ) id 1QA2Iv-0004xi-Vd; Wed, 13 Apr 2011 09:48:30 -0600 Message-ID: <4DA5C5CB.3030907@gmail.com> Date: Wed, 13 Apr 2011 09:48:27 -0600 From: Rob Sargent User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.14) Gecko/20110221 SUSE/3.1.8 Lightning/1.0b2 Thunderbird/3.1.8 MIME-Version: 1.0 To: pgsql-sql@postgresql.org References: <5079.1302707363@sss.pgh.pa.us> In-Reply-To: <5079.1302707363@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-XM-SPF: eid=; ; ; mid=; ; ; hst=in01.mta.xmission.com; ; ; ip=208.187.158.94; ; ; frm=robjsargent@gmail.com; ; ; spf=neutral X-XM-AID: U2FsdGVkX1/TGxBPaooMNDrUB+hmRpViG0SeRn/zUnM= X-SA-Exim-Connect-IP: 208.187.158.94 X-SA-Exim-Mail-From: robjsargent@gmail.com X-Spam-DCC: XMission; sa06 1397; Body=1 Fuz1=1 Fuz2=1 X-Spam-Combo: ;pgsql-sql@postgresql.org X-Spam-Relay-Country: Subject: Re: pass in array to function for use by where clause? how optimize? X-SA-Exim-Version: 4.2.1 (built Fri, 06 Aug 2010 16:31:04 -0600) X-SA-Exim-Scanned: Yes (on in01.mta.xmission.com) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.889 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201104/12 X-Sequence-Number: 35259 On 04/13/2011 09:09 AM, Tom Lane wrote: > Anish Kejariwal writes: >> (select store_id, avg(sales) sales >> from store >> where group_id in(select $1[i] from generate_subscripts($1, 1) g(i)) > > Seems like a pretty brute-force way to deal with the array. Try > > where group_id = any($1) > > Not sure if it'll be a great deal faster, but it's at least easier to > write. > > regards, tom lane > Would adding a subservient function which actually uses the value of the iteration (group_id) as a single parameter be of any use? rjs