NEED URGENT HELP....

I am using PostgreSql 8.1 with pgAdmin III. OS is XP. this is my
function:

CREATE OR REPLACE FUNCTION sp_get_phase(character varying, character
varying, character varying)
RETURNS ret_dv_sp_get_phase AS
$BODY$
SELECT BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
UPDATE_DATE,
AddInfo1, AddInfo2
FROM T_PHASE
WHERE (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)
$BODY$
LANGUAGE 'sql' VOLATILE;

When I run
select * from sp_get_phase ('sandip', 'oms', '4') returns 1
record.....this works fine....

select * from sp_get_phase ('sandip', 'oms', '1') returns 1
record.....this also works fine... BUT

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

I tried to execute the SQL statement from the function

SELECT BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
UPDATE_DATE,
AddInfo1, AddInfo2
FROM T_PHASE
WHERE (USER_ID = 'testUser') AND (COMPANY_ID = 'testCompany') AND
BOOK_NO IN (1,4)

----- This Works fine... returns 2 records. What may be the problem?

Thanks in advance.
Regards,
Sandip.

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

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

On 12/21/06, Sandip G <sandip(at)singapore(dot)com> wrote:
>
> I am using PostgreSql 8.1 with pgAdmin III. OS is XP.
>
> this is my function:
>
> CREATE OR REPLACE FUNCTION sp_get_phase(character varying, character
> varying, character varying)
> RETURNS ret_dv_sp_get_phase AS
> $BODY$
> SELECT BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
> UPDATE_DATE,
> AddInfo1, AddInfo2
> FROM T_PHASE
> WHERE (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)
> $BODY$
> LANGUAGE 'sql' VOLATILE;
>
>
> When I run
> select * from sp_get_phase ('sandip', 'oms', '4') returns 1
> record.....this works fine....
>
> select * from sp_get_phase ('sandip', 'oms', '1') returns 1
> record.....this also works fine... BUT
>
> select * from sp_get_phase ('sandip', 'oms', '1,4') this return a Blank
> record.
>
> I tried to execute the SQL statement from the function
>
> SELECT BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
> UPDATE_DATE,
> AddInfo1, AddInfo2
> FROM T_PHASE
> WHERE (USER_ID = 'testUser') AND (COMPANY_ID = 'testCompany')
> AND BOOK_NO IN (1,4)
>
> ----- This Works fine... returns 2 records. What may be the problem?
>
> Thanks in advance.
> Regards,
> Sandip.
>
>
>
> -- <http://a8-asy.a8ww.net/a8-ads/adftrclick?redirectid=en-mail_a_01>

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(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

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(at)svana(dot)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(at)svana(dot)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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ 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