Re: DROP COLLATION vs pg_collation question

> > 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