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

[PostGIS]

#3896: postgis_extensions_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
Resolution:                         |   Keywords:
------------------------------------+---------------------------

Old description:

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

New description:

 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_extensions_upgrade() RETURNS text 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;

 RETURN postgis_full_version();

 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.

--

Comment (by robe):

 I updated the function to return postgis_full_version() details of their
 install.

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