Обсуждение: 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.
--
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@singapore.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.
--
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
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/ > 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....
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com
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
--