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
