track_collation.sql

\set ON_ERROR_STOP
DROP TABLE IF EXISTS test1;
DROP DOMAIN IF EXISTS d_es, d_en_fr, d_en_fr_ga;
DROP TYPE IF EXISTS t_en_fr_ga, t_en_fr;
DROP DOMAIN IF EXISTS d_custom;
DROP TYPE IF EXISTS t_custom;
DROP COLLATION IF EXISTS custom;
CREATE TYPE t_en_fr AS (fr text COLLATE "fr-x-icu", en text COLLATE "en-x-icu");
CREATE DOMAIN d_en_fr AS t_en_fr;
CREATE DOMAIN d_es AS text COLLATE "es-x-icu";
CREATE TYPE t_en_fr_ga AS (en_fr t_en_fr, ga text COLLATE "ga-x-icu");
CREATE DOMAIN d_en_fr_ga AS t_en_fr_ga;
CREATE TYPE t_custom AS (meh text, meh2 text);
CREATE DOMAIN d_custom AS t_custom;

CREATE COLLATION custom (
    LOCALE = 'fr-x-icu', PROVIDER = 'icu'
);

CREATE TABLE test1(
    id integer,
    t_en_fr t_en_fr,
    d_en_fr d_en_fr,
    d_es d_es,
    t_en_fr_ga t_en_fr_ga,
    d_en_fr_ga d_en_fr_ga,
    d_en_fr_ga_arr d_en_fr_ga[]
);

CREATE INDEX idx01_t_en_fr__d_es ON test1 (t_en_fr, d_es);
CREATE INDEX idx02_d_en_fr ON test1 (d_en_fr);
CREATE INDEX idx03_t_en_fr_ga ON test1 (t_en_fr_ga);
CREATE INDEX idx04_d_en_fr_ga ON test1 (d_en_fr_ga);
CREATE INDEX idx05_d_en_fr_ga_arr ON test1 (d_en_fr_ga_arr);
CREATE INDEX idx06_d_en_fr_ga ON test1(id) WHERE (d_en_fr_ga).en_fr.fr = 'meh';
CREATE INDEX idx07_d_en_fr_ga ON test1(id) WHERE (d_en_fr_ga).ga = 'meh';
CREATE INDEX idx08_d_en_fr_ga ON test1(id) WHERE (t_en_fr_ga) = ('meh', 'meh', 'meh');
CREATE INDEX idx09_d_en_fr_ga ON test1(id) WHERE (d_en_fr_ga) = ('meh', 'meh', 'meh');
CREATE INDEX idx10_d_en_fr_ga_es ON test1(id) WHERE (d_en_fr_ga) = ('meh', 'meh', 'meh' COLLATE "es-x-icu");
CREATE INDEX idx11_d_es ON test1(id) WHERE (d_es) = ('meh');
CREATE INDEX idx12_custom ON test1(id) WHERE ('meh', 'meh')::d_custom = ('meh', 'meh' collate custom)::d_custom;
CREATE INDEX idx13_custom ON test1(id) WHERE ('meh' collate custom, 'meh')::d_meh = ('meh', 'meh')::d_custom;

SELECT objid::regclass, refobjid::regcollation, refobjversion
FROM pg_depend d
LEFT JOIN pg_class c ON c.oid = d.objid
WHERE refclassid = 'pg_collation'::regclass
AND coalesce(relkind, 'i') = 'i'
ORDER BY 1, 2;