Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id B00B59FA489 for ; Tue, 26 Dec 2006 11:09:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 49614-05-3 for ; Tue, 26 Dec 2006 11:08:52 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.226]) by postgresql.org (Postfix) with ESMTP id 497A59FA394 for ; Tue, 26 Dec 2006 11:09:03 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id 71so65876wri for ; Tue, 26 Dec 2006 07:09:02 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=sUxl/zEwCRBgY2hXwIQNV5KZ6w2JaKOtmtbAmGWrkILQFJjwWqCxvRKLpAwWPJcdPJHnMNQuKdEzKbUb0xAZ8nkavfiakNuriWSg0TBpPXHHOxRbw2BG1Pmj63Uc2I1BVHfotg9Lbabp6xcriDlKJwAAgrcjYB0RdQw+9iaqDnM= Received: by 10.90.100.2 with SMTP id x2mr2088751agb.1167145742022; Tue, 26 Dec 2006 07:09:02 -0800 (PST) Received: by 10.90.50.13 with HTTP; Tue, 26 Dec 2006 07:09:01 -0800 (PST) Message-ID: <65937bea0612260709s6a4e0d16v375d401564569f22@mail.gmail.com> Date: Tue, 26 Dec 2006 20:39:01 +0530 From: "Gurjeet Singh" To: "Martijn van Oosterhout" , "Henrique P Machado" , "Guy Rouillier" , pgsql-general@postgresql.org, "Sandip G" Subject: Re: NEED URGENT HELP.... In-Reply-To: <20061226115348.GB8412@svana.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_142245_23710774.1167145741987" References: <20061221074133.5B05A872DC@cal1-1.us4.outblaze.com> <4590629B.9070808@burntmail.com> <20061226115348.GB8412@svana.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/1457 X-Sequence-Number: 106395 ------=_Part_142245_23710774.1167145741987 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline It works Martijn... but with a few changes will be required in your function Sandip; you will have to pass an ARRAY constructor and return a SETOF record. Here's a sample: postgres=> create table tab ( a int, b int ); CREATE TABLE postgres=> insert into tab values ( 1, 9 ); INSERT 0 1 postgres=> insert into tab values (2,8); INSERT 0 1 postgres=> insert into tab values (3,7); INSERT 0 1 postgres=> insert into tab values (4,6); INSERT 0 1 postgres=> insert into tab values (5,5); INSERT 0 1 postgres=> create or replace function fun ( character varying [] ) returns setof tab as postgres-> $$ postgres$> select * from tab where a = any ($1) postgres$> $$ language 'sql' volatile; CREATE FUNCTION postgres=> select fun('{1}'); fun ------- (1,9) (1 row) postgres=> select fun('{2,3}'); fun ------- (2,8) (3,7) (2 rows) postgres=> Hope it helps.... On 12/26/06, Martijn van Oosterhout wrote: > > On Mon, Dec 25, 2006 at 08:52:52PM -0300, Henrique P Machado wrote: > > WHERE (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3) > > > > Could'nt he use an array in this 3rd parameter? > > I think so, if it's written: > > AND BOOK_NO = ANY($3) > > Have a nice day, > -- > Martijn van Oosterhout http://svana.org/kleptog/ > > From each according to his ability. To each according to his ability to > litigate. > > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.1 (GNU/Linux) > > iD8DBQFFkQ1LIB7bNG8LQkwRApbCAJsH26IcDusO5Vi5kNC1UQ185usbnACeOxdC > xQo+z5Z7+Xofks/h3MmeF7w= > =Rq6g > -----END PGP SIGNATURE----- > > > -- gurjeet[.singh]@EnterpriseDB.com singh.gurjeet@{ gmail | hotmail | yahoo }.com ------=_Part_142245_23710774.1167145741987 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline It works Martijn...

but with a few changes will be required in your function Sandip; you will have to pass an ARRAY constructor and return a SETOF record. Here's a sample:

postgres=> create table tab ( a int, b int );
CREATE TABLE
postgres=> insert into tab values ( 1, 9 );
INSERT 0 1
postgres=> insert into tab values (2,8);
INSERT 0 1
postgres=> insert into tab values (3,7);
INSERT 0 1
postgres=> insert into tab values (4,6);
INSERT 0 1
postgres=> insert into tab values (5,5);
INSERT 0 1
postgres=> create or replace function fun ( character varying [] ) returns setof
 tab as
postgres-> $$
postgres$> select * from tab where a = any ($1)
postgres$> $$ language 'sql' volatile;
CREATE FUNCTION
postgres=> select fun('{1}');
  fun
-------
 (1,9)
(1 row)

postgres=> select fun('{2,3}');
  fun
-------
 (2,8)
 (3,7)
(2 rows)

postgres=>

Hope it helps....


On 12/26/06, Martijn van Oosterhout <kleptog@svana.org> wrote:
On Mon, Dec 25, 2006 at 08:52:52PM -0300, Henrique P Machado wrote:
> WHERE           (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)
>
> Could'nt he use an array in this 3rd parameter?

I think so, if it's written:

AND BOOK_NO = ANY($3)

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFkQ1LIB7bNG8LQkwRApbCAJsH26IcDusO5Vi5kNC1UQ185usbnACeOxdC
xQo+z5Z7+Xofks/h3MmeF7w=
=Rq6g
-----END PGP SIGNATURE-----





--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com ------=_Part_142245_23710774.1167145741987--