postgis_full_version()

DECLARE

    libver text;

    librev text;

    projver text;

    projver_compiled text;

    geosver text;

    geosver_compiled text;

    sfcgalver text;

    gdalver text := NULL;

    libxmlver text;

    liblwgeomver text;

    dbproc text;

    relproc text;

    fullver text;

    rast_lib_ver text := NULL;

    rast_scr_ver text := NULL;

    topo_scr_ver text := NULL;

    json_lib_ver text;

    protobuf_lib_ver text;

    wagyu_lib_ver text;

    sfcgal_lib_ver text;

    sfcgal_scr_ver text;

    pgsql_scr_ver text;

    pgsql_ver text;

    core_is_extension bool;

BEGIN

    SELECT public.postgis_lib_version() INTO libver;

    SELECT public.postgis_proj_version() INTO projver;

    SELECT public.postgis_geos_version() INTO geosver;

    SELECT public.postgis_geos_compiled_version() INTO geosver_compiled;

    SELECT public.postgis_proj_compiled_version() INTO projver_compiled;

    SELECT public.postgis_libjson_version() INTO json_lib_ver;

    SELECT public.postgis_libprotobuf_version() INTO protobuf_lib_ver;

    SELECT public.postgis_wagyu_version() INTO wagyu_lib_ver;

    SELECT public._postgis_scripts_pgsql_version() INTO pgsql_scr_ver;

    SELECT public._postgis_pgsql_version() INTO pgsql_ver;

    BEGIN

        SELECT public.postgis_gdal_version() INTO gdalver;

    EXCEPTION

        WHEN undefined_function THEN

            RAISE DEBUG 'Function postgis_gdal_version() not found.  Is raster support enabled and rtpostgis.sql installed?';

    END;

    BEGIN

        SELECT public.postgis_sfcgal_full_version() INTO sfcgalver;

        BEGIN

            SELECT public.postgis_sfcgal_scripts_installed() INTO sfcgal_scr_ver;

        EXCEPTION

            WHEN undefined_function THEN

                sfcgal_scr_ver := 'missing';

        END;

    EXCEPTION

        WHEN undefined_function THEN

            RAISE DEBUG 'Function postgis_sfcgal_scripts_installed() not found. Is sfcgal support enabled and sfcgal.sql installed?';

    END;

    SELECT public.postgis_liblwgeom_version() INTO liblwgeomver;

    SELECT public.postgis_libxml_version() INTO libxmlver;

    SELECT public.postgis_scripts_installed() INTO dbproc;

    SELECT public.postgis_scripts_released() INTO relproc;

    SELECT public.postgis_lib_revision() INTO librev;

    BEGIN

        SELECT topology.postgis_topology_scripts_installed() INTO topo_scr_ver;

    EXCEPTION

        WHEN undefined_function OR invalid_schema_name THEN

            RAISE DEBUG 'Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed?';

        WHEN insufficient_privilege THEN

            RAISE NOTICE 'Topology support cannot be inspected. Is current user granted USAGE on schema "topology" ?';

        WHEN OTHERS THEN

            RAISE NOTICE 'Function postgis_topology_scripts_installed() could not be called: % (%)', SQLERRM, SQLSTATE;

    END;

​

    BEGIN

        SELECT postgis_raster_scripts_installed() INTO rast_scr_ver;

    EXCEPTION

        WHEN undefined_function THEN

            RAISE DEBUG 'Function postgis_raster_scripts_installed() not found. Is raster support enabled and rtpostgis.sql installed?';

        WHEN OTHERS THEN

            RAISE NOTICE 'Function postgis_raster_scripts_installed() could not be called: % (%)', SQLERRM, SQLSTATE;

    END;

​

    BEGIN

        SELECT public.postgis_raster_lib_version() INTO rast_lib_ver;

    EXCEPTION

        WHEN undefined_function THEN

            RAISE DEBUG 'Function postgis_raster_lib_version() not found. Is raster support enabled and rtpostgis.sql installed?';

        WHEN OTHERS THEN

            RAISE NOTICE 'Function postgis_raster_lib_version() could not be called: % (%)', SQLERRM, SQLSTATE;

    END;

​

    fullver = 'POSTGIS="' || libver;

​

    IF  librev IS NOT NULL THEN

        fullver = fullver || ' ' || librev;

    END IF;

​

    fullver = fullver || '"';

​

    IF EXISTS (

        SELECT * FROM pg_catalog.pg_extension

        WHERE extname = 'postgis')

    THEN

            fullver = fullver || ' [EXTENSION]';

            core_is_extension := true;

    ELSE

            core_is_extension := false;

    END IF;

​

    IF liblwgeomver != relproc THEN

        fullver = fullver || ' (liblwgeom version mismatch: "' || liblwgeomver || '")';

    END IF;

​

    fullver = fullver || ' PGSQL="' || pgsql_scr_ver || '"';

    IF pgsql_scr_ver != pgsql_ver THEN

        fullver = fullver || ' (procs need upgrade for use with PostgreSQL "' || pgsql_ver || '")';

    END IF;

​

    IF  geosver IS NOT NULL THEN

        fullver = fullver || ' GEOS="' || geosver || '"';

        IF (string_to_array(geosver, '.'))[1:2] != (string_to_array(geosver_compiled, '.'))[1:2]

        THEN

            fullver = format('%s (compiled against GEOS %s)', fullver, geosver_compiled);

        END IF;

    END IF;

​

    IF  sfcgalver IS NOT NULL THEN

        fullver = fullver || ' SFCGAL="' || sfcgalver || '"';

    END IF;

​

    IF  projver IS NOT NULL THEN

        fullver = fullver || ' PROJ="' || projver || '"';

        IF (string_to_array(projver, '.'))[1:3] != (string_to_array(projver_compiled, '.'))[1:3]

        THEN

            fullver = format('%s (compiled against PROJ %s)', fullver, projver_compiled);

        END IF;

    END IF;

​

    IF  gdalver IS NOT NULL THEN

        fullver = fullver || ' GDAL="' || gdalver || '"';

    END IF;

​

    IF  libxmlver IS NOT NULL THEN

        fullver = fullver || ' LIBXML="' || libxmlver || '"';

    END IF;

​

    IF json_lib_ver IS NOT NULL THEN

        fullver = fullver || ' LIBJSON="' || json_lib_ver || '"';

    END IF;

​

    IF protobuf_lib_ver IS NOT NULL THEN

        fullver = fullver || ' LIBPROTOBUF="' || protobuf_lib_ver || '"';

    END IF;

​

    IF wagyu_lib_ver IS NOT NULL THEN

        fullver = fullver || ' WAGYU="' || wagyu_lib_ver || '"';

    END IF;

​

    IF dbproc != relproc THEN

        fullver = fullver || ' (core procs from "' || dbproc || '" need upgrade)';

    END IF;

​

    IF topo_scr_ver IS NOT NULL THEN

        fullver = fullver || ' TOPOLOGY';

        IF topo_scr_ver != relproc THEN

            fullver = fullver || ' (topology procs from "' || topo_scr_ver || '" need upgrade)';

        END IF;

        IF core_is_extension AND NOT EXISTS (

            SELECT * FROM pg_catalog.pg_extension

            WHERE extname = 'postgis_topology')

        THEN

                fullver = fullver || ' [UNPACKAGED!]';

        END IF;

    END IF;

​

    IF rast_lib_ver IS NOT NULL THEN

        fullver = fullver || ' RASTER';

        IF rast_lib_ver != relproc THEN

            fullver = fullver || ' (raster lib from "' || rast_lib_ver || '" need upgrade)';

        END IF;

        IF core_is_extension AND NOT EXISTS (

            SELECT * FROM pg_catalog.pg_extension

            WHERE extname = 'postgis_raster')

        THEN

                fullver = fullver || ' [UNPACKAGED!]';

        END IF;

    END IF;

​

    IF rast_scr_ver IS NOT NULL AND rast_scr_ver != relproc THEN

        fullver = fullver || ' (raster procs from "' || rast_scr_ver || '" need upgrade)';

    END IF;

​

    IF sfcgal_scr_ver IS NOT NULL AND sfcgal_scr_ver != relproc THEN

        fullver = fullver || ' (sfcgal procs from "' || sfcgal_scr_ver || '" need upgrade)';

    END IF;

​

    -- Check for the presence of deprecated functions

    IF EXISTS ( SELECT oid FROM pg_catalog.pg_proc WHERE proname LIKE '%_deprecated_by_postgis_%' )

    THEN

        fullver = fullver || ' (deprecated functions exist, upgrade is not complete)';

    END IF;

​

    RETURN fullver;

END