PGYI violation logic example

get_dead_live_violations.sql

CREATE OR REPLACE FUNCTION get_dead_live_violations(IN upload_id text)
    RETURNS TABLE(
        upload_id text,
        source_row_index integer,
        violating_table text,
        violating_key text,
        violation_rule text,
        violation_severity text,
        violation_comment text) AS $$

DECLARE
    measure RECORD;
    measure_number integer;
    source_table := 'trees_measurement';
    rule_type := 'bio.dead_live_violation';
    severity := 'error';
BEGIN
    -- RULE: trees should not transition from Dead Condition to Live Condition
    -- DEPENDENCY: last_and_staged_trees_measurement_series

    -- for each company tree measure
    FOR measure IN 
        SELECT * 
        FROM last_and_staged_trees_measurement_series(upload_id) -- could this be a view?
    LOOP

        -- for each consecutive tree measure (2+)
        FOR measure_number IN 2..array_upper(measure.measurement_numbers, 1) LOOP

            -- if current condition is live (L) and previous was dead (D).
            IF ( measure.conditions[measure_number]; = 'L' AND measure.conditions[measure_number-1] = 'D' ) THEN

                -- this is a violation
                RETURN QUERY
                    SELECT
                        measure.upload_ids[measure_number] AS upload_id,
                        measure.source_row_indexes[measure_number] AS source_row_index,
                        source_table AS violating_table,
                        r.company || '-' || r.company_plot_number || '-' || tree AS violating_key,
                        rule_type AS violation_rule,
                        severity AS violation_severity,
                        'Tree changed from dead to live in year: ' || years[measure_number]::text ||
                            '.  Conditions over measurement periods: (' || array_to_string(measure.conditions, ',') || ')'
                            AS violation_comment;
            END IF; 

        END LOOP;

    END LOOP;
END;

$$ LANGUAGE plpgsql VOLATILE COST 100 ROWS 1000;

last_and_staged_trees_measurement_series.sql

CREATE OR REPLACE FUNCTION last_and_staged_trees_measurement_series(IN upload_id text)
    RETURNS TABLE (
        company text,
        company_plot_number text,
        tree_number integer,
        upload_ids text[],
        source_row_indexes integer[],
        measurement_years integer[],
        measurement_numbers integer[]) AS $$

BEGIN
    
    -- main view used by any measurement series based validation rule

    RETURN QUERY
        SELECT 
            trees_measurement.company,
            trees_measurement.company_plot_number,
            trees_measurement.tree_number,
            array_agg(trees_measurement.upload_id ORDER BY trees_measurement.measurement_number) AS upload_ids,
            array_agg(trees_measurement.source_row_index ORDER BY trees_measurement.measurement_number) AS source_row_indexes,
            array_agg(plot_measurement.measurement_year ORDER BY trees_measurement.measurement_number) AS measurement_years,
            array_agg(plot_measurement.measurement_number ORDER BY trees_measurement.measurement_number) AS measurement_numbers,
            array_agg(
                CASE
                    WHEN trees_measurement.condition_code1 = '1' THEN 'D'::text
                    WHEN trees_measurement.condition_code1 = '2' THEN 'D'::text
                    WHEN trees_measurement.condition_code2 = '1' THEN 'D'::text
                    WHEN trees_measurement.condition_code2 = '2' THEN 'D'::text
                    WHEN trees_measurement.condition_code3 = '1' THEN 'D'::text
                    WHEN trees_measurement.condition_code3 = '2' THEN 'D'::text
                ELSE 'L'::text
                END ORDER BY trees_measurement.measurement_number
            ) AS conditions,
            array_agg(trees_measurement.dbh ORDER BY trees_measurement.measurement_number) AS dbhs,
            array_agg(trees_measurement.htlc ORDER BY trees_measurement.measurement_number) AS htlcs,
            array_agg(trees_measurement.height ORDER BY trees_measurement.measurement_number) AS heights

        FROM trees_measurement
            INNER JOIN staged_plot_measurements -- new view => (!is_committed, !is_historical)
            LEFT OUTER JOIN last_plot_measurements -- new view => (is_committed, !is_historical && max measurement_number)
        WHERE trees_measurement.measurement_number = last_plot_measurement.measurement_number
            OR trees_measurement.upload_id = upload_id
        GROUP BY trees_measurement.company, trees_measurement.company_plot_number, trees_measurement.tree_number;
END;

$$ LANGUAGE plpgsql VOLATILE COST 100 ROWS 1000;