> > DROP COLLATION IF EXISTS pg_catalog."...."
>
> Yes, that will delete a row from "pg_collation".
Many thanks.
> Note that with DROP COLLATION you can only remove collations
> that belong to the encoding of your current database.
A-ha ! Can that bit be found anywhere in the docs ?
IOW, the following code is exactly useless ?
(because of the "collencoding <> _db_encoding" business ;-)
create function gm.remove_unneeded_collations()
returns void
language plpgsql
security definer
as '
DECLARE
_rec record;
_db_name text;
_db_encoding integer;
BEGIN
SELECT pg_catalog.current_database() INTO _db_name;
SELECT encoding INTO _db_encoding FROM pg_database WHERE
datname = _db_name;
RAISE NOTICE ''database [%]: removing collations for encodings
other than the database encoding [%]'', _db_name,
pg_catalog.pg_encoding_to_char(_db_encoding);
FOR _rec IN (
SELECT oid, collnamespace, collname, collencoding
FROM pg_collation
WHERE
oid > 1000
AND
collencoding IS NOT NULL
AND
collencoding <> -1
AND
collencoding <> _db_encoding
) LOOP
RAISE NOTICE ''dropping collation #% "%.%" (encoding:
%)'', _rec.oid, _rec.collnamespace::regnamespace, _rec.collname,
pg_catalog.pg_encoding_to_char(_rec.collencoding);
BEGIN
EXECUTE ''DROP COLLATION IF EXISTS '' ||
_rec.collnamespace::regnamespace || ''."'' || _rec.collname || ''"'';
EXCEPTION
WHEN undefined_object THEN RAISE NOTICE
''collation does not seem to exist (perhaps for the DB encoding ?)'';
END;
END LOOP;
END;';
The reason for this being the wish to reduce the risk surface
for locale version information changes at the OS level by
removing collations not relevant to a given database.
Thanks,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
