How do I update the GanosBase plug-ins?

Prerequisites

Before you begin, ensure that you have:

• Access to the PostgreSQL command-line interface (CLI)

• A connection to the target database on your ApsaraDB RDS instance

Step 1: Connect and list installed plug-ins

Use the PostgreSQL CLI to connect to your RDS instance, then run the following command to view all installed plug-ins:

\dx

Example output:

List of installed extensions
                    Name                | Version |   Schema   |                                                     Description
------------------------------------+---------+------------+---------------------------------------------------------------------------------------------------------------------
 address_standardizer               | 2.5.4   | public     | Ganos PostGIS+ address standardizer
 address_standardizer_data_us       | 2.5.4   | public     | Ganos PostGIS+ address standardizer data us
 ganos_address_standardizer         | 4.1     | public     | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
 ganos_address_standardizer_data_us | 4.1     | public     | Address Standardizer US dataset example
 ganos_geometry                     | 4.1     | public     | Ganos geometry extension for PostgreSQL
 ganos_geometry_sfcgal              | 4.1     | public     | Ganos geometry SFCGAL functions extension for PostgreSQL
 ganos_geometry_topology            | 4.1     | topology   | Ganos geometry topology spatial types and functions extension for PostgreSQL
 ganos_networking                   | 4.1     | public     | Ganos networking extension for PostgreSQL
 ganos_spatialref                   | 4.1     | public     | Ganos spatial reference extension for PostgreSQL
 ganos_tiger_geocoder               | 4.1     | tiger      | Ganos tiger geocoder and reverse geocoder
 plpgsql                            | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis                            | 2.5.4   | public     | Ganos PostGIS+
 postgis_sfcgal                     | 2.5.4   | public     | Ganos PostGIS+
 postgis_tiger_geocoder             | 2.5.4   | public     | Ganos PostGIS+ tiger geocoder
 postgis_topology                   | 2.5.4   | public     | Ganos PostGIS+ topology

Check the Description column to identify GanosBase plug-ins. If a plug-in's description includes the word "Ganos", it is a GanosBase plug-in.

Step 2: Update the Ganos plug-ins

The update method depends on the version of your installed Ganos plug-ins.

SELECT ganos_update();

CREATE OR REPLACE FUNCTION ganos_update()
    RETURNS text AS
$$
DECLARE
    rec RECORD;
    sql text;
BEGIN
    FOR rec IN
        SELECT extname
        FROM pg_extension
        WHERE extname like 'ganos_%'
        LOOP
            sql = 'ALTER EXTENSION '
                || rec.extname
                || ' UPDATE ';
            RAISE NOTICE '%', sql;
            EXECUTE sql;

        END LOOP;

    return 'All Ganos extensions have updated to latest version';

END
$$ LANGUAGE 'plpgsql' volatile STRICT;

SELECT ganos_update();