Jesper Krogh <[EMAIL PROTECTED]> 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 Heap Scan 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.420 rows=3 loops=1)
Index Cond: (unique1 = ANY ($1))
Total runtime: 1.155 ms
(5 rows)
regression=# explain analyze execute foo('{}');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan 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.027 rows=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
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
