postgis_extensions_upgrade(target_version text DEFAULT NULL::text)

DECLARE

    rec record;

    sql text;

    var_schema text;

BEGIN

​

    FOR rec IN

        SELECT name, default_version, installed_version

        FROM pg_catalog.pg_available_extensions

        WHERE name IN (

            'postgis',

            'postgis_raster',

            'postgis_sfcgal',

            'postgis_topology',

            'postgis_tiger_geocoder'

        )

        ORDER BY length(name) -- this is to make sure 'postgis' is first !

    LOOP --{

​

        IF target_version IS NULL THEN

            target_version := rec.default_version;

        END IF;

​

        IF rec.installed_version IS NULL THEN --{

            -- If the support installed by available extension

            -- is found unpackaged, we package it

            IF --{

                 -- PostGIS is always available (this function is part of it)

                 rec.name = 'postgis'

​

                 -- PostGIS raster is available if type 'raster' exists

                 OR ( rec.name = 'postgis_raster' AND EXISTS (

                            SELECT 1 FROM pg_catalog.pg_type

                            WHERE typname = 'raster' ) )

​

                 -- PostGIS SFCGAL is available if

                 -- 'postgis_sfcgal_version' function exists

                 OR ( rec.name = 'postgis_sfcgal' AND EXISTS (

                            SELECT 1 FROM pg_catalog.pg_proc

                            WHERE proname = 'postgis_sfcgal_version' ) )

​

                 -- PostGIS Topology is available if

                 -- 'topology.topology' table exists

                 -- NOTE: watch out for https://trac.osgeo.org/postgis/ticket/2503

                 OR ( rec.name = 'postgis_topology' AND EXISTS (

                            SELECT 1 FROM pg_catalog.pg_class c

                            JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid )

                            WHERE n.nspname = 'topology' AND c.relname = 'topology') )

​

                 OR ( rec.name = 'postgis_tiger_geocoder' AND EXISTS (

                            SELECT 1 FROM pg_catalog.pg_class c

                            JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid )

                            WHERE n.nspname = 'tiger' AND c.relname = 'geocode_settings') )

            THEN --}{ -- the code is unpackaged

                -- Force install in same schema as postgis

                SELECT INTO var_schema n.nspname

                  FROM pg_namespace n, pg_proc p

                  WHERE p.proname = 'postgis_full_version'

                    AND n.oid = p.pronamespace

                  LIMIT 1;

                IF rec.name NOT IN('postgis_topology', 'postgis_tiger_geocoder')

                THEN

                    sql := format(

                              'CREATE EXTENSION %1$I SCHEMA %2$I VERSION unpackaged;'

                              'ALTER EXTENSION %1$I UPDATE TO %3$I',

                              rec.name, var_schema, target_version);

                ELSE

                    sql := format(

                             'CREATE EXTENSION %1$I VERSION unpackaged;'

                             'ALTER EXTENSION %1$I UPDATE TO %2$I',

                             rec.name, target_version);

                END IF;

                RAISE NOTICE 'Packaging and updating %', rec.name;

                RAISE DEBUG '%', sql;

                EXECUTE sql;

            ELSE

                RAISE DEBUG 'Skipping % (not in use)', rec.name;

            END IF; --}

        ELSE -- The code is already packaged, upgrade it --}{

            sql = format(

                'ALTER EXTENSION %1$I UPDATE TO "ANY";'

                'ALTER EXTENSION %1$I UPDATE TO %2$I',

                rec.name, target_version

                );

            RAISE NOTICE 'Updating extension % %', rec.name, rec.installed_version;

            RAISE DEBUG '%', sql;

            EXECUTE sql;

        END IF; --}

​

    END LOOP; --}

​

    RETURN format(

        'Upgrade to version %s completed, run SELECT postgis_full_version(); for details',

        target_version

    );

​

​

END