Re: Limitations of PostgreSQL

Here's one (of many) solutions:

CREATE OR REPLACE FUNCTION range( ANYELEMENT, ANYARRAY ) RETURNS INTEGER
LANGUAGE SQL AS
 'SELECT CASE array_upper( $2, 1 ) WHEN 1 THEN 0 ELSE range( $1, $2[
1:(array_upper( $2, 1 ) - 1) ] ) END
       + CASE WHEN $1 > $2[ array_upper( $2, 1 ) ] THEN 1 ELSE 0 END; ';

Called as

SELECT range( 25, ARRAY[ 10, 20, 30, 40, 50 ] );

-- Dean


On 2005-10-13 09:48, Scott Marlowe wrote:
> I have to admit, my thoughts on it were to build a query with case statements in it and execute it.  That sounds
aboutlike you're proposing, right? 
>
> On Thu, 2005-10-13 at 11:30, Dean Gibson (DB Administrator) wrote:
>
>> What's the point of a binary search if the list is small enough to fit on a line or two?  And if a query can be
substitutedfor N1-NN, you have to read all the values anyway, and then the function is trivially expressed as a normal
querywith no decrease in speed. 
>>
>> -- Dean
>>
>>> On Wed, 2005-10-12 at 20:08, Michael Fuhr wrote:
>>>
>>>
>>>> Here's an excerpt from the MySQL documentation:
>>>> INTERVAL(N,N1,N2,N3,...)
>>>>     Returns 0 if N < N1, 1 if N < N2 and so on or -1 if N is
>>>>     NULL.  All arguments are treated as integers.  It is required
>>>>     that N1 < N2 < N3 < ... < Nn for this function to work
>>>>     correctly.  This is because a binary search is used (very fast).
>>>>
>>>>