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

2010/8/17 Tom Lane <tgl@sss.pgh.pa.us>:
> Cédric Villemain <cedric.villemain.debian@gmail.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.

Oh! Thank you very much for those clarifications.
... and I am sorry for the noisy report ...

--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support