Обсуждение: 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.

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

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.

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