NEED URGENT HELP....

select * from sp_get_phase ('sandip', 'oms', '1,4') this return a Blank
record

it would match ur query against '1,4' for the corressponding field in the
table.
do u really have one such value for that field in your table, i mean '1,4'
??
it won't search for 1 and 4 separately if that is what you want your query
to work.

~Harpreet

Sandip G wrote:

WHERE (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)

select * from sp_get_phase ('sandip', 'oms', '1,4') this return a
Blank record.

$3 is a parameter marker for a single value. You cannot supply a
comma-separated list of values and expect it to operate on them. As
Harpreet points out, it is interpreting your comma-separated list as a
single value. If you know you will always have (e.g.) two values you
want to pass, you could code your function with "in ($3, $4)", but if
you want a generalized, variable-length list of values in your IN
clause, you'd need to use dynamic SQL.

--
Guy Rouillier

WHERE (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)

Could'nt he use an array in this 3rd parameter?

--
ZehRique

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/

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

Martijn van Oosterhout escreveu:

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,

I believe not because the aray have one another sintax, different of on
simple data, but if you create one stored procedure you will be
transform the data...
If you sayd array to the data like '1,2,3' is another case, and I think
you dont have problemns with this.
ok.

--
Ivo Nascimento
Iann tech - Desenvolvendo solu��es com performance e seguran�a
http://www.ianntech.com.br

Great !!! Its working.... Thanks to all for the great help. I am new
to Postgre and like it's performance... I would like to learn it in
depth, Please provide me any good resource to learn Postgre with lots
of sample code/queries. Cheers !!!Best Regards,Sandip.

----- Original Message -----
From: "Gurjeet Singh"
To: "Martijn van Oosterhout" , "Henrique P Machado" , "Guy Rouillier"
, pgsql-general@postgresql.org, "Sandip G"
Subject: Re: [GENERAL] NEED URGENT HELP....
Date: Tue, 26 Dec 2006 20:39:01 +0530

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

--
___________________________________________________
Search for products and services at:
http://search.mail.com