table_version.ver_get_lds_survey_non_bdy_marks_diff()

gistfile1.sql

bde_db=# \df+ table_version.ver_get_lds_survey_non_bdy_marks_diff
List of functions
Schema|Name|Result data type|Argument data types|Type|Volatility|Owner|Language|Source code|Description
table_version|ver_get_lds_survey_non_bdy_marks_diff|TABLE(_diff_action character, id integer, name character varying, "order" integer, nominal_accuracy numeric, date_last_adjusted timestamp without time zone, shape geometry)|p_revision1 integer, p_revision2 integer|normal|volatile|bde_dba|plpgsql|
    DECLARE
        v_revision1      INTEGER;
        v_revision2      INTEGER;
        v_temp           INTEGER;
        v_base_version   INTEGER;
        v_revision_table TEXT;
    BEGIN
        IF NOT table_version.ver_is_table_versioned('lds', 'survey_non_bdy_marks') THEN
            RAISE EXCEPTION 'Table lds.survey_non_bdy_marks is not versioned';
        END IF;
        
        v_revision1 := p_revision1;
        v_revision2 := p_revision2;
        IF v_revision1 = v_revision2 THEN
            RETURN;
        END IF;
        
        IF v_revision1 > v_revision2 THEN
            RAISE EXCEPTION 'Revision 1 (%) is greater than revision 2 (%)', v_revision1, v_revision2;
        END IF;
        
        SELECT table_version.ver_get_table_base_revision('lds', 'survey_non_bdy_marks')
        INTO   v_base_version;
        IF v_base_version > v_revision2 THEN
            RETURN;
        END IF;
        IF v_base_version > v_revision1 THEN
            v_revision1 := v_base_version;
        END IF;
        
        RETURN QUERY EXECUTE
        table_version.ver_ExpandTemplate(
            $sql$
            WITH last_value_changed AS (
                SELECT DISTINCT ON (T.id)
                    T.*
                FROM
                    table_version.lds_survey_non_bdy_marks_revision AS T
                WHERE (
                    (T._revision_created <= %1% AND T._revision_expired > %1% AND T._revision_expired <= %2%) OR
                    (T._revision_created > %1%  AND T._revision_created <= %2%)
                )
                ORDER BY
                    T.id, 
                    T._revision_created DESC
            ),
            old_state_changed AS(
                SELECT DISTINCT
                    T.id
                FROM
                    table_version.lds_survey_non_bdy_marks_revision AS T
                WHERE
                     T._revision_created <= %1% AND T._revision_expired > %1% AND
                     T.id IN (SELECT last_value_changed.id FROM last_value_changed)
            )
            SELECT
                CASE WHEN LVC._revision_expired <= %2% THEN
                    'D'::CHAR(1)
                WHEN OSC.id IS NULL THEN
                    'I'::CHAR(1)
                ELSE
                    'U'::CHAR(1)
                END AS diff_action,
                LVC.id, 
                LVC.name, 
                LVC."order", 
                LVC.nominal_accuracy, 
                LVC.date_last_adjusted, 
                LVC.shape
            FROM
                last_value_changed AS LVC
                LEFT JOIN old_state_changed AS OSC ON LVC.id = OSC.id;
            $sql$,
            ARRAY[
                v_revision1::TEXT,
                v_revision2::TEXT
            ]
        );
        RETURN;
    END;
|
(1 row)