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

Re: the IN clause saga

From: Fernando Nasser <fnasser(at)redhat(dot)com>
To: Barry Lind <blind(at)xythos(dot)com>
Cc: Felipe Schnack <felipes(at)ritterdosreis(dot)br>,Oliver Jowett <oliver(at)opencloud(dot)com>, pgsql-jdbc(at)postgresql(dot)org,Dave Cramer <Dave(at)micro-automation(dot)net>
Subject: Re: the IN clause saga
Date: 2003-07-22 17:35:37
Message-ID: 3F1D75E9.1000507@redhat.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
In case you missed my last comment:

Mind that this is only necessary for backward compatibility.  With 7.4 
and the V3 protocol you just send an array and the backend sorts it out.

Of course, one can opt in not providing this feature for pre 7.4 
backends but I see no reason for that.  The parsing required is confined 
to the surrounding sql fragments (we already split the command) and very 
simple.

Fernando

Barry Lind wrote:
> No you are not.
> 
> --Barry
> 
> Felipe Schnack wrote:
> 
>>   Am I the only the only one who doesn't like the idea of the driver 
>> parsing SQL statements (to check if there is a IN clause)
>>
>> On Tue, 22 Jul 2003 10:41:22 -0400
>> Fernando Nasser <fnasser(at)redhat(dot)com> wrote:
>>
>>
>>> Oliver Jowett wrote:
>>>
>>>> On Tue, Jul 22, 2003 at 09:05:45AM -0400, Fernando Nasser wrote:
>>>>
>>>>
>>>>> Thanks for summarizing it Oliver.
>>>>>
>>>>> I've asked Tom Lane about the backend behavior and he informed me 
>>>>> that:
>>>>>
>>>>> 1) 7.4 backends do support parameters in the IN predicate, as ($1, 
>>>>> $2, $3) (i.e., our (?, ?, ?) syntax).
>>>>>
>>>>> 2) 7.4 backends have a PostgreSQL specific extension that allows 
>>>>> you to fill the IN predicate with a list: ($1)  (i.e., our (?) ).  
>>>>> One has to pass a PostgreSQL array, like integer[] to fill the 
>>>>> list.  Note that the parenthesis is already in place, it is not 
>>>>> generated by the ? expansion.
>>>>
>>>>
>>>>
>>>> I assume this is only when you're doing a PREPARE/EXECUTE?
>>>>
>>>
>>> yes.
>>>
>>>
>>>>> The feature 2 in 7.4 backends is of limited use as the planner does 
>>>>> not know about the list, so the generated plan will not be as good 
>>>>> as if you pass the list with fixed values since the beginning.  But 
>>>>> an improvement for this can be attempted for 7.5.
>>>>
>>>>
>>>>
>>>> Hm, then it sounds like the right solution is to have setArray() 
>>>> expand as
>>>> the guts of an IN clause when the backend is <7.4 or server prepares 
>>>> are
>>>> off, and the parameter is in a query of the form "... IN (?)", and as a
>>>> normal array otherwise.
>>>>
>>>
>>> That is _exactly_ what I am proposing (option 2 of your summary)
>>>
>>>
>>>
>>> -- 
>>> Fernando Nasser
>>> Red Hat Canada Ltd.                     E-Mail:  fnasser(at)redhat(dot)com
>>> 2323 Yonge Street, Suite #300
>>> Toronto, Ontario   M4P 2C9
>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 2: you can get off all lists at once with the unregister command
>>>    (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>>
>>
>>
>>
> 
> 
> 


-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser(at)redhat(dot)com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9


In response to

pgsql-jdbc by date

Next:From: João Paulo VasconcellosDate: 2003-07-22 17:43:27
Subject: JDBC install
Previous:From: Fernando NasserDate: 2003-07-22 17:33:35
Subject: Re: the IN clause saga

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