gis Database

Parameters

Name	Type	Mode
_json_data	json	IN
_template_section	text	IN

Definition

DECLARE
    item record;
    html text;
BEGIN

-- Get HTML template from html_template table
    SELECT content
    FROM pgmetadata.html_template AS h
    WHERE True
    AND section = _template_section
    INTO html
    ;
    IF html IS NULL THEN
        RETURN NULL;
    END IF;

-- Get dataset item
    -- We transpose dataset record into rows such as
    -- col    | val
    -- id     | 1
    -- uid    | dfd3b73c-3cd3-40b7-b92d-aa0f625c86fe
    -- ...
    -- title  | My title
    -- For each row, we search and replace the [% "col" %] by val
    FOR item IN
        SELECT (line.d).key AS col, Coalesce((line.d).value, '') AS val
        FROM (
            SELECT json_each_text(_json_data) d
        ) AS line
    LOOP
        -- replace QGIS style field [% "my_field" %] by field value
        html = regexp_replace(
            html,
            concat('\[%( )*?(")*', item.col ,'(")*( )*%\]'),
            item.val,
            'g'
        )
        ;

END LOOP;

RETURN html;

END;

generate_html_from_json

Description

Parameters

Definition