Re: DROP COLLATION vs pg_collation question

[Karsten Hilbert <Karsten.Hilbert@xxxxxxx>]

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