[postgis-tickets] [PostGIS] #3896: postgis_extension_upgrade function to upgrade all your packaged postgis extensions

[PostGIS]

#3896: postgis_extension_upgrade function to upgrade all your packaged postgis
extensions
-----------------------------------+---------------------------
 Reporter:  robe                   |      Owner:  strk
     Type:  enhancement            |     Status:  new
 Priority:  medium                 |  Milestone:  PostGIS 2.5.0
Component:  build/upgrade/install  |    Version:  trunk
 Keywords:                         |
-----------------------------------+---------------------------
 One of the annoyances of having so many postgis extensions is having to
 remember to upgrade all of them when you install a new postgis.

 This function would check to see what extensions of postgis you have
 installed and upgrade them to the latest.  I put in the noraster bit with
 the mindset that if we encode the noraster version in the version as I had
 proposed

 then this function can be used to check which version of postgis a user
 has and upgrade them to the right one (with or without raster).  So if
 they have the noraster edition, it would upgrade them to the next noraster
 version regardless what the default is set to in postgis.control.  That
 would allow us to make the noraster be the default and users who want
 raster, would have to install using


 {{{
 CREATE EXTENSION postgis VERSION "2.5.0";
 }}}



 {{{
 CREATE OR REPLACE FUNCTION postgis_extension_upgrade() RETURNS void AS
 $$
 BEGIN
  DECLARE rec record; BEGIN for rec in SELECT 'ALTER EXTENSION ' || name ||
 ' UPDATE TO "' || CASE WHEN installed_version ILIKE '%noraster' THEN
 replace(default_version,'noraster','') ||'noraster' ELSE
 replace(default_version,'noraster','') END    || '";' AS sql
 FROM pg_available_extensions
 WHERE installed_version > '' AND name IN('address_standardizer',
 'postgis', 'postgis_sfcgal', 'postgis_tiger_geocoder', 'postgis_topology')
 and ( replace(default_version,'noraster','') <> replace(installed_version,
 'noraster','') ) LOOP

     EXECUTE rec.sql;
     RAISE NOTICE '%', rec.sql;
 END LOOP; END;

 END

 $$ language plpgsql;
 }}}


 I tested using a database that has sfcgal and postgis installed at 2.3.1

 NOTICE showed this:


 {{{
 NOTICE:  ALTER EXTENSION postgis UPDATE TO "2.4.0";
 CONTEXT:  PL/pgSQL function postgis_extension_upgrade() line 9 at RAISE
 NOTICE:  ALTER EXTENSION postgis_sfcgal UPDATE TO "2.4.0";
 CONTEXT:  PL/pgSQL function postgis_extension_upgrade() line 9 at RAISE

 }}}


 We could also maybe allow the function to take in an argument.

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3896>
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.