The following bug has been logged online:

Bug reference: 2847
Logged by: Sandip
Email address: sandip (AT) singapore (DOT) com
PostgreSQL version: 8.1
Operating system: Windows XP
Description: Bug with IN statement
Details:

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

------=_Part_82102_14265828.1166774595504
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 12/21/06, Sandip <sandip (AT) singapore (DOT) com> wrote:

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


--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com

------=_Part_82102_14265828.1166774595504
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 12/21/06, <b class="gmail_sendername">Sandip</b> &lt;<a href="mailto:sandip (AT) singapore (DOT) com">sandip (AT) singapore (DOT) com</a>&gt; wrote:<div><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
CREATE OR REPLACE FUNCTION sp_get_phase(character varying, character<br>varying, character varying)<br>&nbsp;&nbsp;RETURNS ret_dv_sp_get_phase AS<br>$BODY$<br>SELECT&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,<br>
UPDATE_DATE,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Ad dInfo1, AddInfo2<br>FROM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;T_PHASE<br>W HERE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp; (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)<br>$BODY$<br>&nbsp;&nbsp;LANGUAGE 'sql' VOLATILE;<br><br><br>select * from sp_get_phase ('sandip', 'oms', '1,4') this return a Blank
<br>record.</blockquote><div><br>For this function call, the SELECT inside the function gets translated into <br><br> </div><br><div><br>SELECT&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp;&nbsp;&nbsp;&nbsp; ...<br>FROM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;T_PHASE<br>WHERE& nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp; (USER_ID = 'sandip') AND (COMPANY_ID = 'oms') AND BOOK_NO IN ('1,4')
<br><br>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!!!
<br>&nbsp;</div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">I tried to execute the SQL statement from the function<br><br>SELECT&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
<br>UPDATE_DATE,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;AddInfo1, AddInfo2<br>FROM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;T_PHASE<br>W HERE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp; (USER_ID = 'testUser') AND (COMPANY_ID = 'testCompany') AND<br>BOOK_NO IN (1,4)<br><br>----- This Works fine... returns 2 records.&nbsp;&nbsp; What may be the problem?
<br><br></blockquote></div><br>Best regards,<br><br clear="all"><br>-- <br>gurjeet[.singh]@EnterpriseDB.com<br>singh.gurjeet@{ gmail | hotmail | yahoo }.com

------=_Part_82102_14265828.1166774595504--