gis.road_graph

Parameters

Name	Type	Mode
_editing_session_id	integer	IN
sql_text	text	TABLE

Definition

DECLARE
    editing_session_record record;
BEGIN
    RETURN QUERY
    WITH
    items AS (
        SELECT unnest(array['nodes', 'roads', 'edges', 'markers']) AS item
    ),
    ids AS (
        SELECT item, logged_ids->item AS item_ids
        FROM items, "road_graph".editing_sessions
        WHERE status = 'edited'
        AND id = _editing_session_id
    ),
    item_cols AS (
        SELECT item, string_agg(c.column_name, ', ' ORDER BY ordinal_position) AS cols
        FROM items, information_schema.columns c
        WHERE table_schema = 'road_graph'
        AND table_name = item
        AND c.column_name != 'id'
        GROUP BY item
    ),
    queries AS (
        SELECT item, "key"::integer AS id, "value" AS op
        FROM ids, jsonb_each_text(item_ids)
    )
    SELECT concat(
        CASE
            WHEN op = 'I' THEN 'INSERT INTO'
            WHEN op = 'U' THEN 'UPDATE'
            WHEN op = 'D' THEN 'DELETE FROM'
        END, ' ',
        '"road_graph".', q.item, ' AS t ',
        CASE
            WHEN op = 'I' THEN 'SELECT * FROM road_graph_editing.' || q.item || ' AS s '
            WHEN op = 'U' THEN concat(
                ' SET (', c.cols, ') = (',
                's.', replace(c.cols, ', ', ', s.'),
                ') FROM road_graph_editing.',  q.item, ' AS s '
            )
            WHEN op = 'D' THEN ''
        END, ' ',
        CASE
            WHEN op = 'I' THEN ' WHERE s.id = ' || id::text
            WHEN op = 'U' THEN ' WHERE s.id = t.id AND t.id = ' || id::text
            WHEN op = 'D' THEN ' WHERE t.id = ' || id::text
        END,
        CASE
            WHEN op = 'I' THEN ' ON CONFLICT DO NOTHING '
            ELSE ''
        END,
        ';'
    ) AS sql
    FROM queries AS q, item_cols AS c
    WHERE q.item = c.item
    ;

END;

create_queries_from_editing_session(_editing_session_id integer)

Description

Parameters

Definition