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