[postgis-tickets] [PostGIS] #5584: PostGIS Upgrade Issue

[PostGIS]

#5584: PostGIS Upgrade Issue
------------------------------------+-------------------------------
  Reporter:  rama                   |      Owner:  strk
      Type:  defect                 |     Status:  new
  Priority:  critical               |  Milestone:  PostGIS Packaging
 Component:  build/upgrade/install  |    Version:  3.3.x
Resolution:                         |   Keywords:
------------------------------------+-------------------------------
Comment (by rama):

 Function:postgis_extensions_upgrade
 {{{
 -- FUNCTION: public.postgis_extensions_upgrade()

 -- DROP FUNCTION IF EXISTS public.postgis_extensions_upgrade();

 CREATE OR REPLACE FUNCTION public.postgis_extensions_upgrade(
         )
     RETURNS text
     LANGUAGE 'plpgsql'
     COST 100
     VOLATILE PARALLEL UNSAFE
 AS $BODY$
 DECLARE rec record; sql text;
 BEGIN
         -- if at a version different from default version or we are at a
 dev version,
         -- then do an upgrade to default version

         FOR rec in SELECT  name, default_version, installed_version
                 FROM pg_available_extensions
                 WHERE installed_version > '' AND name IN('postgis',
 'postgis_sfcgal', 'postgis_tiger_geocoder', 'postgis_topology')
                 AND ( default_version <> installed_version  OR
                         ( default_version = installed_version AND
 default_version ILIKE '%dev%' AND  installed_version ILIKE '%dev%'  )  )
 LOOP

                 -- we need to upgrade to next so our installed is
 different from current
                 -- and then we can upgrade to default_version
                 IF rec.installed_version = rec.default_version THEN
                         sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE
 TO ' || quote_ident(rec.default_version || 'next')   || ';';
                         EXECUTE sql;
                         RAISE NOTICE '%', sql;
                 END IF;

                 sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE TO ' ||
 quote_ident(rec.default_version)   || ';';
                 EXECUTE sql;
                 RAISE NOTICE '%', sql;
         END LOOP;

         RETURN public.postgis_full_version();

 END
 $BODY$;

 ALTER FUNCTION public.postgis_extensions_upgrade()
     OWNER TO postgres;

 COMMENT ON FUNCTION public.postgis_extensions_upgrade()
     IS 'Upgrades installed postgis packaged extensions (e.g.
 postgis_sfcgal, postgis_topology, postgis_sfcgal) to latest installed
 version. Reports full postgis version and build configuration infos
 after.';

 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5584#comment:6>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.