Updating Mapinfo MapCatalog table in the SDW V1.5

-- Function: at_sync_mapinfo_mapcatalog()

-- DROP FUNCTION at_sync_mapinfo_mapcatalog();

CREATE OR REPLACE FUNCTION at_sync_mapinfo_mapcatalog()
  RETURNS trigger AS
$BODY1$
    --$BODY$
    DECLARE
        r record;
        insertStr varchar;
        deleteStr varchar;
        spatialtype varchar;
        symbol varchar;
        projection varchar;
        count integer;
        message varchar;
        default_symbol_point varchar;
        default_symbol_line varchar;
        default_symbol_polygon varchar;
        -- default_symbol_mixed varchar;
    BEGIN
        -- extract default symbology from mapinfo_mapcatalog_configuration
        -- create some default defaults first in case table data doesn't exist
        default_symbol_point := 'SYMBOL (34,12632064,10) ';
        default_symbol_line := 'PEN (1, 2, 0) ';
        default_symbol_polygon := 'PEN (1, 2, 8388608) BRUSH (16, 16776960, 16777215) ';
        -- default_symbol_mixed := 'SYMBOL (34,12632064,10) PEN (1, 2, 8388608) BRUSH (16, 16776960, 16777215) ';
        FOR r in SELECT * FROM mapinfo.mapinfo_mapcatalog_configuration LOOP
            IF (r.setting_name = 'SYMBOL_POINT') THEN
                default_symbol_point := r.setting_value;
            END IF;
            IF (r.setting_name = 'SYMBOL_LINE') THEN
                default_symbol_line := r.setting_value;
            END IF;
            IF (r.setting_name = 'SYMBOL_POLYGON') THEN
                default_symbol_polygon := r.setting_value;
            END IF;			
            --IF (r."SETTING_NAME" = 'SYMBOL_MIXED') THEN
            --	default_symbol_mixed := r."SETTING_VALUE";
            --END IF;						
        END LOOP;
        -- first, select entries in geometry_columns which don't exist in mapinfo_mapcatalog
        -- and add them to mapinfo_mapcatalog
        count := 0;
        FOR r in SELECT	* FROM geometry_columns T2 WHERE NOT EXISTS (SELECT * FROM mapinfo.mapinfo_mapcatalog T1 WHERE T1.ownername = T2.f_table_schema AND T1.tablename = T2.f_table_name) LOOP
            spatialtype := '19.3';
            symbol := default_symbol_point || default_symbol_polygon;
            IF (r.type = 'LINESTRING' OR r.type = 'MULTILINESTRING') THEN
                spatialtype := '19.1';
                symbol := default_symbol_line;
            END IF;
            IF (r.type = 'POLYGON' OR r.type = 'MULTIPOLYGON') THEN
                spatialtype := '19.2';
                symbol := default_symbol_polygon;
            END IF;
            IF (r.type = 'POINT' OR r.type = 'MULTIPOINT') THEN
                spatialtype := '19';
                symbol := default_symbol_point;
            END IF;
            projection := E'Earth Projection 8, 79, "m", -2, 49, 0.9996012717, 400000, -100000 Bounds (-7845061.1011, -15524202.1641) (8645061.1011, 4470074.53373)';
            insertStr := E'INSERT INTO mapinfo.mapinfo_mapcatalog ('
                || 'spatialtype, tablename, ownername, spatialcolumn, db_x_ll, db_y_ll, ' 
                || 'db_x_ur, db_y_ur, view_x_ll, view_y_ll, view_x_ur, view_y_ur, coordinatesystem, symbol, xcolumnname, '
                || 'ycolumnname, renditiontype, renditiontable) '
                || 'VALUES (' || spatialtype || E',\'' || r.f_table_name || E'\', \'' || r.f_table_schema || E'\', \'' || r.f_geometry_column || E'\', 0, 0, '
                || E'700000, 1300000, 0, 0, 700000, 1300000, \'' || projection || E'\', \'' || symbol || E'\', \'NO_COLUMN\', '
                || E'\'NO_COLUMN\', 0, \'\');';
            EXECUTE insertStr;		
            count := count + 1;
        END LOOP;
        message := count || ' inserted, ';
        -- second, delete entries in mapinfo_mapcatalog which don't exist in geometry_columns
        count := 0;
        FOR r in SELECT	* FROM mapinfo.mapinfo_mapcatalog T1 WHERE NOT EXISTS (SELECT * FROM geometry_columns T2 WHERE T1.ownername = T2.f_table_schema AND T1.tablename = T2.f_table_name) LOOP
            deleteStr := E'DELETE FROM mapinfo.mapinfo_mapcatalog WHERE ownername = \'' || r.ownername || E'\' AND tablename = \'' || r.tablename || E'\';';
            EXECUTE deleteStr;
            count := count + 1;
        END LOOP;
        message := 'mapinfo_mapcatalog: ' || message || count || ' removed';
        --return message;
        RETURN NULL;
    END;
    --$BODY$
    $BODY1$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION at_sync_mapinfo_mapcatalog() OWNER TO postgres;

-- Trigger: at_update_mapinfo_mapcatalog_trigger on geometry_columns

-- DROP TRIGGER at_update_mapinfo_mapcatalog_trigger ON geometry_columns;

CREATE TRIGGER at_update_mapinfo_mapcatalog_trigger
AFTER INSERT OR UPDATE OR DELETE
ON geometry_columns
FOR EACH ROW
EXECUTE PROCEDURE at_sync_mapinfo_mapcatalog();

-- Trigger: at_update_mapinfo_mapcatalog_trigger_truncate on geometry_columns

-- DROP TRIGGER at_update_mapinfo_mapcatalog_trigger_truncate ON geometry_columns;

CREATE TRIGGER at_update_mapinfo_mapcatalog_trigger_truncate
  AFTER TRUNCATE
  ON geometry_columns
  FOR EACH STATEMENT
  EXECUTE PROCEDURE at_sync_mapinfo_mapcatalog();