Re: PL/pgSQL EXECUTE '..' USING with unknown

=?ISO-8859-1?Q?C=E9dric_Villemain?= <cedric(dot)villemain(dot)debian(at)gmail(dot)com> writes:
> Here we are. A simple usecase.

The reason you have an issue here is that the column is char(n) while
the parameter is text. So the non-USING execute is equivalent to

regression=# explain SELECT flag FROM foo where uid = 'cfcd208495d565ef66e7dff9f98764da';
QUERY PLAN
--------------------------------------------------------------------
Index Scan using foo_pkey on foo (cost=0.00..8.27 rows=1 width=1)
Index Cond: (uid = 'cfcd208495d565ef66e7dff9f98764da'::bpchar)
(2 rows)

while the EXECUTE USING is equivalent to

regression=# explain SELECT flag FROM foo where uid = 'cfcd208495d565ef66e7dff9f98764da'::text;
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on foo (cost=0.00..24.02 rows=5 width=1)
Filter: ((uid)::text = 'cfcd208495d565ef66e7dff9f98764da'::text)
(2 rows)

and the reason you don't get an indexscan on the latter is that it's a
TEXT comparison not a BPCHAR comparison; which is different because of
the rules about ignoring trailing blanks.

char(n) sucks. Avoid it if possible. If you insist on using it,
be very very careful about which comparison semantics you're asking for.

regards, tom lane