Re: The empty list?

Jesper Krogh <jesper@krogh.cc> writes:
> ... the right-hand-side of on
> in operation will allways be a list (if I havent forgotten something).

IN (SELECT ...) for one thing.

> ... but missing SQL for the empty list basically means that
> we cant handle the empty set encoded in the empty array without
> explicitly introducing code for this size of array. From a
> programatically viewpoint, this seems quite odd.

FWIW, as of 8.2 the best option will probably be to use
"col = ANY (array)", which does support zero-length arrays
if you use either an out-of-line parameter or an array literal.

regression=# prepare foo(int[]) as select * from tenk1 where unique1 = any ($1);
PREPARE
regression=# explain analyze execute foo('{1,9,42}');                                                      QUERY PLAN
                                                    
 

-------------------------------------------------------------------------------------------------------------------------Bitmap
HeapScan on tenk1  (cost=36.38..424.30 rows=489 width=244) (actual time=0.487..0.524 rows=3 loops=1)  Recheck Cond:
(unique1= ANY ($1))  ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..36.38 rows=489 width=0) (actual
time=0.420..0.420rows=3 loops=1)        Index Cond: (unique1 = ANY ($1))Total runtime: 1.155 ms
 
(5 rows)

regression=# explain analyze execute foo('{}');                                                      QUERY PLAN
                                              
 

-------------------------------------------------------------------------------------------------------------------------Bitmap
HeapScan on tenk1  (cost=36.38..424.30 rows=489 width=244) (actual time=0.044..0.044 rows=0 loops=1)  Recheck Cond:
(unique1= ANY ($1))  ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..36.38 rows=489 width=0) (actual
time=0.027..0.027rows=0 loops=1)        Index Cond: (unique1 = ANY ($1))Total runtime: 0.478 ms
 
(5 rows)

This is not SQL-standard syntax IIRC, but then "foo IN ()" would
certainly not be either.
        regards, tom lane