[postgis-tickets] r15970 - postgis_extensions_upgrade(). Can't remember which postgis extensions you installed and are two lazy to figure out and upgrade each with ALTER EXTENSION.

[Regina Obe]

Author: robe
Date: 2017-10-11 10:11:50 -0700 (Wed, 11 Oct 2017)
New Revision: 15970

Modified:
   trunk/NEWS
   trunk/doc/reference_management.xml
   trunk/postgis/postgis.sql.in
Log:
postgis_extensions_upgrade().  Can't remember which postgis extensions you installed and are two lazy to figure out and upgrade each with ALTER EXTENSION.
just do 

SELECT postgis_extensions_upgrade();

Closes #3896

Modified: trunk/NEWS
===================================================================
--- trunk/NEWS	2017-10-11 17:05:18 UTC (rev 15969)
+++ trunk/NEWS	2017-10-11 17:11:50 UTC (rev 15970)
@@ -3,6 +3,7 @@
 * New Features *
   - #3876, ST_Angle function (Rémi Cura)
   - #3564, ST_LineInterpolatePoints (Dan Baston)
+  - #3896, PostGIS_Extensions_Upgrade()
 
 * Breaking Changes *
   - #3885, version number removed from address_standardize lib file

Modified: trunk/doc/reference_management.xml
===================================================================
--- trunk/doc/reference_management.xml	2017-10-11 17:05:18 UTC (rev 15969)
+++ trunk/doc/reference_management.xml	2017-10-11 17:11:50 UTC (rev 15970)
@@ -344,7 +344,72 @@
 	  </refsection>
 	</refentry>
 
+<refentry id="PostGIS_Extensions_Upgrade">
+	  <refnamediv>
+		<refname>PostGIS_Extensions_Upgrade</refname>
 
+		<refpurpose>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.</refpurpose>
+	  </refnamediv>
+
+	  <refsynopsisdiv>
+		<funcsynopsis>
+		  <funcprototype>
+			<funcdef>text <function>PostGIS_Extensions_Upgrade</function></funcdef>
+
+			<paramdef></paramdef>
+		  </funcprototype>
+		</funcsynopsis>
+	  </refsynopsisdiv>
+
+	  <refsection>
+		<title>Description</title>
+
+		<para>Upgrades installed postgis packaged extensions
+		to latest installed version. Only extensions you have installed in the database will be upgraded and if they are already at last installed version,
+		they will not be upgraded. Reports full postgis version and build configuration
+		infos after.  This is short-hand for doing multiple ALTER EXTENSION .. UPDATE for each postgis extension.
+		Currently only tries to upgrade extensions postgis, postgis_sfcgal, postgis_topology, and postgis_tiger_geocoder.</para>
+
+		<para>Availability: 2.5.0</para>
+	  </refsection>
+
+	  <refsection>
+		<title>Examples</title>
+
+		<programlisting>SELECT PostGIS_Extensions_Upgrade();</programlisting>
+<screen>
+NOTICE:  ALTER EXTENSION postgis_tiger_geocoder UPDATE TO "2.5.0dev";
+CONTEXT:  PL/pgSQL function postgis_extensions_upgrade() line 10 at RAISE
+NOTICE:  ALTER EXTENSION postgis_topology UPDATE TO "2.5.0dev";
+CONTEXT:  PL/pgSQL function postgis_extensions_upgrade() line 10 at RAISE
+
+							   postgis_extensions_upgrade
+----------------------------------------------------------------------------------
+POSTGIS="2.5.0dev r15966" [EXTENSION] PGSQL="100"
+GEOS="3.7.0dev-CAPI-1.11.0 8fe2ce6" SFCGAL="1.3.1"
+PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.2, released 2017/09/15"
+LIBXML="2.7.8" LIBJSON="0.12" LIBPROTOBUF="1.2.1" TOPOLOGY RASTER
+(1 row)</screen>
+	  </refsection>
+
+	  <refsection>
+		<title>See Also</title>
+
+		<para>
+		<xref linkend="upgrading" />,
+		<xref linkend="PostGIS_GEOS_Version" />,
+		<xref linkend="PostGIS_Lib_Version" />,
+		<xref linkend="PostGIS_LibXML_Version" />,
+		<xref linkend="PostGIS_PROJ_Version" />,
+		<xref linkend="PostGIS_Version" />
+		</para>
+	  </refsection>
+	</refentry>
+
+
+
 	<refentry id="PostGIS_Full_Version">
 	  <refnamediv>
 		<refname>PostGIS_Full_Version</refname>

Modified: trunk/postgis/postgis.sql.in
===================================================================
--- trunk/postgis/postgis.sql.in	2017-10-11 17:05:18 UTC (rev 15969)
+++ trunk/postgis/postgis.sql.in	2017-10-11 17:11:50 UTC (rev 15970)
@@ -2790,6 +2790,27 @@
 	FROM substring(version(), 'PostgreSQL ([0-9\.]+)') AS s;
 $$ LANGUAGE 'sql' STABLE;
 
+-- Availability: 2.5.0
+CREATE OR REPLACE FUNCTION postgis_extensions_upgrade() RETURNS text
+AS $$
+DECLARE rec record;
+BEGIN
+ FOR rec in SELECT 'ALTER EXTENSION ' || name || ' UPDATE TO ' || quote_ident(default_version)   || ';' AS sql
+	FROM pg_available_extensions
+	WHERE installed_version > '' AND name IN('postgis', 'postgis_sfcgal', 'postgis_tiger_geocoder', 'postgis_topology')
+	AND ( default_version <> installed_version ) LOOP
+
+    EXECUTE rec.sql;
+    RAISE NOTICE '%', rec.sql;
+END LOOP;
+
+RETURN @extschema at .postgis_full_version();
+
+END
+$$ language plpgsql;
+
+
+-- Changed: 2.4.0
 CREATE OR REPLACE FUNCTION postgis_full_version() RETURNS text
 AS $$
 DECLARE