Re: execute prepared statement passing parameter expression with COLLATE clause - Mailing list pgsql-hackers

hi.

$Subject setup

CREATE COLLATION case_insensitive (provider = icu, locale =
'@colStrength=secondary', deterministic = false);
CREATE COLLATION ignore_accents (provider = icu, locale =
'@colStrength=primary;colCaseLevel=yes', deterministic = false);
DROP TABLE IF EXISTS pktable cascade;
CREATE TABLE pktable (x text COLLATE case_insensitive);
INSERT INTO pktable VALUES ('A');
DEALLOCATE q6;
PREPARE q6 AS SELECT * FROM pktable WHERE x = $1;


select * from pktable where x = 'Å' collate ignore_accents;
--return one row

execute q6('Å' collate ignore_accents);
--return zero rows

not sure return zero rows is desired.

postgres=# explain execute q6('Å' collate ignore_accents);
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on pktable  (cost=0.00..27.00 rows=7 width=32)
   Filter: (x = 'Å'::text)
(2 rows)

postgres=# explain select * from pktable where x = 'Å' collate ignore_accents;
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on pktable  (cost=0.00..27.00 rows=7 width=32)
   Filter: (x = 'Å'::text COLLATE ignore_accents)