Re: Schema variables - new implementation for Postgres 15 - Mailing list pgsql-hackers

=# "
CREATE COLLATION

=# create variable myvariable text collate mycollation;
CREATE VARIABLE

=# select classid::regclass, objid, objsubid, refclassid::regclass, refobjid, refobjsubid from pg_depend where classid::regclass::text = 'pg_variable' or refclassid::regclass::text = 'pg_variable';
   classid   | objid | objsubid |  refclassid  | refobjid | refobjsubid
-------------+-------+----------+--------------+----------+-------------
 pg_variable | 16407 |        0 | pg_namespace |     2200 |           0
(1 row)

fixed

 

=# let myvariable = 'AA';
LET

=# select 'AA' collate "en-x-icu" < myvariable;
 ?column?
----------
 f
(1 row)

=# select 'AA' collate "en-x-icu" < myvariable collate mycollation;
ERROR:  42P21: collation mismatch between explicit collations "en-x-icu" and "mycollation"
LINE 1: select 'AA' collate "en-x-icu" < myvariable collate mycollat...

What do you expect?  I don't understand collating well, but it looks correct. Minimally the tables have the same behavior.

create collation mycollation (locale = 'fr-FR', provider = 'icu');

create table foo(mycol text collate mycollation);

select 'AA' collate "en-x-icu" < mycol from foo;

┌──────────┐
│ ?column? │
╞══════════╡
│ f        │
└──────────┘
(1 row)


postgres=# select 'AA' collate "en-x-icu" < mycol collate mycollation from foo;
ERROR:  collation mismatch between explicit collations "en-x-icu" and "mycollation"
LINE 1: select 'AA' collate "en-x-icu" < mycol collate mycollation f...
                                               ^