postgis_extension_upgrade function to upgrade all your packaged postgis extensions

[robe]

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.