gis.road_graph

Parameters

Name	Type	Mode
_schema_name	text	IN
_table_name	text	IN
_add_optional_fields	boolean	IN
field_name	text	TABLE
field_type	text	TABLE
field_status	text	TABLE

Definition

DECLARE
    table_regclass regclass;
    _geometry_type text;
    field record;
    existing_type text;
BEGIN
    -- Check the table exists
    table_regclass = to_regclass(format('%I.%I', _schema_name, _table_name));
    IF table_regclass IS NULL THEN
        field_status = 'table not found';
        RETURN NEXT;
        RETURN;
    END IF;

-- Get the geometry type from the managed objects table if the table is registered,
    -- or else from the geometry column geom
    SELECT INTO _geometry_type
        lower(o.geometry_type)
    FROM road_graph.managed_objects AS o
    WHERE o.schema_name = _schema_name
    AND o.table_name = _table_name
    LIMIT 1
    ;
    IF _geometry_type IS NULL THEN
        SELECT INTO _geometry_type
            lower(g.type)
        FROM geometry_columns AS g
        WHERE g.f_table_schema = _schema_name
        AND g.f_table_name = _table_name
        AND g.f_geometry_column = 'geom'
        LIMIT 1
        ;
    END IF;
    IF _geometry_type IS NULL THEN
        field_status = 'geom column not found';
        RETURN NEXT;
        RETURN;
    END IF;
    IF _geometry_type NOT IN ('point', 'linestring', 'multilinestring') THEN
        field_status = format('geometry type not supported: %s', _geometry_type);
        RETURN NEXT;
        RETURN;
    END IF;

-- Add the reference fields depending on the geometry type
    FOR field IN
        SELECT f.name, f.type
        FROM (
            VALUES
            -- name, type, geometry type, required, position
            ('road_code', 'text', 'all', True, 1),
            ('marker_code', 'integer', 'point', True, 2),
            ('abscissa', 'real', 'point', True, 3),
            ('cumulative', 'real', 'point', False, 4),
            ('start_marker_code', 'integer', 'line', True, 5),
            ('start_abscissa', 'real', 'line', True, 6),
            ('start_cumulative', 'real', 'line', False, 7),
            ('end_marker_code', 'integer', 'line', True, 8),
            ('end_abscissa', 'real', 'line', True, 9),
            ('end_cumulative', 'real', 'line', False, 10),
            ('offset', 'real', 'all', False, 11),
            ('side', 'text', 'all', False, 12)
        ) AS f(name, type, geometry, required, position)
        WHERE f.geometry IN ('all', CASE WHEN _geometry_type = 'point' THEN 'point' ELSE 'line' END)
        AND (f.required OR _add_optional_fields)
        ORDER BY f.position
    LOOP
        field_name = field.name;
        field_type = field.type;

-- Check if the field already exists
        SELECT INTO existing_type
            format_type(a.atttypid, a.atttypmod)
        FROM pg_attribute AS a
        WHERE a.attrelid = table_regclass
        AND a.attname = field.name
        AND a.attnum > 0
        AND NOT a.attisdropped
        ;

IF existing_type IS NULL THEN
            EXECUTE format(
                'ALTER TABLE %I.%I ADD COLUMN %I %s',
                _schema_name, _table_name, field.name, field.type
            );
            field_status = 'added';
        ELSIF existing_type = field.type THEN
            field_status = 'already exists';
        ELSE
            -- Do not modify an existing field with another type
            field_status = format('already exists with the type %s', existing_type);
        END IF;
        RETURN NEXT;
    END LOOP;

RETURN;
END;

add_managed_object_reference_fields(_schema_name text, _table_name text, _add_optional_fields boolean DEFAULT true)

Description

Parameters

Definition