terraware Database

Parameters

Name	Type	Mode

Definition

DECLARE rec record; sql text; var_schema text;
BEGIN

FOR rec IN
		SELECT name, default_version, installed_version
		FROM pg_catalog.pg_available_extensions
		WHERE name IN (
			'postgis',
			'postgis_raster',
			'postgis_sfcgal',
			'postgis_topology',
			'postgis_tiger_geocoder'
		)
		ORDER BY length(name) -- this is to make sure 'postgis' is first !
	LOOP
		IF rec.installed_version IS NULL THEN
			-- If the support installed by available extension
			-- is found unpackaged, we package it
			IF
				 -- PostGIS is always available (this function is part of it)
				 rec.name = 'postgis'

-- PostGIS raster is available if type 'raster' exists
				 OR ( rec.name = 'postgis_raster' AND EXISTS (
							SELECT 1 FROM pg_catalog.pg_type
							WHERE typname = 'raster' ) )

-- PostGIS SFCGAL is availble if
				 -- 'postgis_sfcgal_version' function exists
				 OR ( rec.name = 'postgis_sfcgal' AND EXISTS (
							SELECT 1 FROM pg_catalog.pg_proc
							WHERE proname = 'postgis_sfcgal_version' ) )

-- PostGIS Topology is available if
				 -- 'topology.topology' table exists
				 -- NOTE: watch out for https://trac.osgeo.org/postgis/ticket/2503
				 OR ( rec.name = 'postgis_topology' AND EXISTS (
							SELECT 1 FROM pg_catalog.pg_class c
							JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid )
							WHERE n.nspname = 'topology' AND c.relname = 'topology') )

OR ( rec.name = 'postgis_tiger_geocoder' AND EXISTS (
							SELECT 1 FROM pg_catalog.pg_class c
							JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid )
							WHERE n.nspname = 'tiger' AND c.relname = 'geocode_settings') )
			THEN
				--force install in same schema as postgis only if postgis extension is installed
				IF rec.name NOT IN('postgis', 'postgis_topology', 'postgis_tiger_geocoder') AND EXISTS (SELECT 1 FROM pg_catalog.pg_extension WHERE extname = 'postgis') THEN
					sql = 'CREATE EXTENSION ' || rec.name || ' FROM unpackaged SCHEMA '
					|| quote_ident( (SELECT ns.nspname FROM pg_catalog.pg_extension AS e INNER JOIN pg_catalog.pg_namespace AS ns ON e.extnamespace = ns.oid WHERE extname = 'postgis')) || ';';
				ELSE
					sql = 'CREATE EXTENSION ' || rec.name || ' FROM unpackaged;';
				END IF;
				RAISE NOTICE 'Packaging extension %', rec.name;
				RAISE DEBUG '%', sql;
				EXECUTE sql;
			ELSE
				RAISE NOTICE 'Extension % is not available or not packagable for some reason', rec.name;
			END IF;
		ELSIF rec.default_version != rec.installed_version
		THEN
			sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE TO ' ||
						quote_ident(rec.default_version)   || ';';
			RAISE NOTICE 'Updating extension % from % to %',
				rec.name, rec.installed_version, rec.default_version;
			RAISE DEBUG '%', sql;
			EXECUTE sql;
		ELSIF (rec.default_version = rec.installed_version AND rec.installed_version ILIKE '%dev') OR
			(public._postgis_pgsql_version() != public._postgis_scripts_pgsql_version())
		THEN
			-- we need to upgrade to next and back
			RAISE NOTICE 'Updating extension % %',
				rec.name, rec.installed_version;
			sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE TO ' ||
						quote_ident(rec.default_version || 'next')   || ';';
			RAISE DEBUG '%', sql;
			EXECUTE sql;
			sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE TO ' ||
						quote_ident(rec.default_version)   || ';';
			RAISE DEBUG '%', sql;
			EXECUTE sql;
		END IF;

END LOOP;

RETURN 'Upgrade completed, run SELECT postgis_full_version(); for details';

END

postgis_extensions_upgrade

Description

Parameters

Definition