Skip site navigation (1) Skip section navigation (2)

Re: BUG #2847: Bug with IN statement

From: "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com>
To: Sandip <sandip(at)singapore(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2847: Bug with IN statement
Date: 2006-12-22 08:03:15
Message-ID: 65937bea0612220003p5134e13fhe5e733adb41b7b91@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On 12/21/06, Sandip <sandip(at)singapore(dot)com> wrote:
>
> 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;
>
>
> select * from sp_get_phase ('sandip', 'oms', '1,4') this return a Blank
> record.


For this function call, the SELECT inside the function gets translated into



SELECT          ...
FROM            T_PHASE
WHERE           (USER_ID = 'sandip') AND (COMPANY_ID = 'oms') AND BOOK_NO IN
('1,4')

Notice the quotes around the list of values for $3. So you can see that the
IN list contains just one value and not two as you might have expected.
You'll need to do some trickery. Try the EXECUTE, perhaps that might help!!!


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?
>
>
Best regards,


-- 
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | yahoo }.com

In response to

pgsql-bugs by date

Next:From: Steve LangridgeDate: 2006-12-22 08:35:55
Subject: BUG #2856: Jdbc 4 connector running on JDK 1.6 should not raise exception for getClientInfo/setClientInfo
Previous:From: Gurjeet SinghDate: 2006-12-22 07:13:10
Subject: Re: Internal Error XXOO...Mission Critical

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group