Sample scripts

Use these sample scripts to create a test schema, tables, a trigger, an event trigger, a function, and a procedure, and populate the tables with test data. These objects are used throughout the following debugging walkthroughs:

Run these scripts once, in order, in the database you’re going to debug. It’s safe to rerun them: the first script drops and recreates the schedules schema every time.

Create the test schema and tables

The script creates the schedules schema and three tables: schedule_template_detail, schedule, and schedule_detail, then populates the first two with test data.

Note

The script sets search_path for the current role, not just the current session. The debug engine opens its own connection to run the routine being debugged, and that connection doesn’t inherit a session-scoped SET search_path. Without the role-level setting, an unqualified table reference such as schedule fails with relation "schedule" does not exist.

DROP SCHEMA IF EXISTS schedules CASCADE;
CREATE SCHEMA schedules;

ALTER ROLE CURRENT_USER SET search_path TO schedules, public;
SET search_path TO schedules;

-- Templates: the ordered pattern of shifts / absences repeated over the period.
CREATE TABLE schedule_template_detail (
    template_id   integer      NOT NULL,
    day_order     smallint     NOT NULL,
    absence_code  varchar(25)  DEFAULT NULL,
    work_shift_cd varchar(25)  DEFAULT NULL,
    CONSTRAINT pk_schedule_template_detail PRIMARY KEY (template_id, day_order)
);

INSERT INTO schedule_template_detail (template_id, day_order, absence_code, work_shift_cd) VALUES
    (1, 1, NULL,            '1/10'),
    (1, 2, NULL,            '1/11.5'),
    (1, 3, NULL,            '1/10'),
    (1, 4, NULL,            '1/11.5'),
    (1, 5, NULL,            '1/10'),
    (1, 6, 'offdays',       NULL),
    (1, 7, 'offdays',       NULL),
    (2, 1, NULL,            '2/11.5'),
    (2, 2, NULL,            '1/11.5'),
    (2, 3, NULL,            '2/11.5'),
    (2, 4, NULL,            '1/11.5'),
    (2, 5, NULL,            '1/11.5'),
    (2, 6, 'offdays',       NULL),
    (2, 7, 'offdays',       NULL),
    (3, 1, NULL,            '1/8'),
    (3, 2, NULL,            '1/8'),
    (3, 3, 'business_trip', NULL);

-- Schedule header: links a concrete schedule to a template.
CREATE TABLE schedule (
    schedule_id  integer GENERATED BY DEFAULT AS IDENTITY,
    template_id  integer DEFAULT NULL,
    CONSTRAINT pk_schedule PRIMARY KEY (schedule_id)
);

INSERT INTO schedule (schedule_id, template_id) VALUES
    (1, 1),
    (2, 1),
    (3, 3),
    (4, 2),
    (5, 1);

-- Resulting per-day schedule that the debugged routines populate.
CREATE TABLE schedule_detail (
    schedule_id     integer      NOT NULL,
    date_out        date         NOT NULL,
    time_sheet_date date         NOT NULL,
    absence_code    varchar(25)  DEFAULT NULL,
    CONSTRAINT pk_schedule_detail PRIMARY KEY (schedule_id, date_out)
);

Create the trigger function and trigger

The script creates the trg_schedule_detail_before_ins trigger function, which defaults a missing absence code and rejects a row without a timesheet date, and the schedule_detail_before_ins row-level trigger that calls it.

SET search_path TO schedules;

CREATE OR REPLACE FUNCTION trg_schedule_detail_before_ins()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_time_sheet_date date;
BEGIN
    v_time_sheet_date := NEW.time_sheet_date;

    IF NEW.absence_code IS NULL THEN
        NEW.absence_code := 'sicklist';
    END IF;

    IF v_time_sheet_date IS NULL THEN
        RAISE EXCEPTION 'time_sheet_date must not be NULL for schedule_id=%', NEW.schedule_id
            USING ERRCODE = '23502';
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER schedule_detail_before_ins
    BEFORE INSERT ON schedule_detail
    FOR EACH ROW
    EXECUTE FUNCTION trg_schedule_detail_before_ins();

Create the event trigger function and event trigger

The script creates the evt_protect_schedule_objects event trigger function, which prevents the core demo tables from being dropped, and the protect_schedule_objects event trigger that fires it on every DROP statement.

SET search_path TO schedules;

CREATE OR REPLACE FUNCTION evt_protect_schedule_objects()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
    r             record;
    v_event       text;
    v_object_name text;
BEGIN
    v_event := TG_EVENT;

    FOR r IN SELECT object_name, object_type, schema_name
             FROM pg_event_trigger_dropped_objects()
    LOOP
        v_object_name := r.object_name;

        IF r.schema_name = 'schedules'
           AND r.object_type = 'table'
           AND v_object_name IN ('schedule', 'schedule_detail', 'schedule_template_detail') THEN
            RAISE EXCEPTION 'Dropping protected table schedules.% is not allowed (event %)',
                v_object_name, v_event;
        END IF;
    END LOOP;
END;
$$;

CREATE EVENT TRIGGER protect_schedule_objects
    ON sql_drop
    EXECUTE FUNCTION evt_protect_schedule_objects();

Create the functions

The following two scripts create two functions: get_first_day_of_month and fill_schedule.

get_first_day_of_month

The get_first_day_of_month function returns the first day of the calendar month a given date belongs to.

SET search_path TO schedules;

CREATE OR REPLACE FUNCTION get_first_day_of_month(p_date date)
RETURNS date
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    RETURN date_trunc('month', p_date)::date;
END;
$$;

fill_schedule

The fill_schedule function takes three input parameters (the first and the last day of a period, and the identifier of a schedule) and generates one row in schedule_detail per calendar day of that period, calling get_first_day_of_month for every generated day. It returns the number of rows it inserted.

SET search_path TO schedules;

CREATE OR REPLACE FUNCTION fill_schedule(
    p_date_from    date,
    p_date_to      date,
    p_schedule_id  integer
)
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
    v_template_id     integer;
    v_template_count  smallint;
    v_row_number      smallint := 0;
    v_date_out        date;
    v_time_sheet_date date;
    v_inserted_count  integer := 0;
    v_absence_code    varchar(25);
    v_work_shift_cd   varchar(25);
BEGIN
    SELECT s.template_id
      INTO v_template_id
      FROM schedule AS s
     WHERE s.schedule_id = p_schedule_id;

    SELECT COUNT(*)
      INTO v_template_count
      FROM schedule_template_detail AS td
     WHERE td.template_id = v_template_id;

    IF v_template_count IS NULL OR v_template_count = 0 THEN
        RETURN 0;
    END IF;

    DELETE FROM schedule_detail AS sd
     WHERE sd.schedule_id = p_schedule_id
       AND sd.date_out BETWEEN p_date_from AND p_date_to;

    v_date_out := p_date_from;

    WHILE v_date_out <= p_date_to LOOP

        IF v_row_number = v_template_count THEN
            v_row_number := 1;
        ELSE
            v_row_number := v_row_number + 1;
        END IF;

        v_time_sheet_date := get_first_day_of_month(v_date_out);

        SELECT td.absence_code, td.work_shift_cd
          INTO v_absence_code, v_work_shift_cd
          FROM schedule_template_detail AS td
         WHERE td.template_id = v_template_id
           AND td.day_order  = v_row_number;

        INSERT INTO schedule_detail (schedule_id, date_out, time_sheet_date, absence_code)
        VALUES (p_schedule_id, v_date_out, v_time_sheet_date, v_absence_code);

        v_inserted_count := v_inserted_count + 1;

        v_date_out := v_date_out + INTERVAL '1 day';
    END LOOP;

    RETURN v_inserted_count;
END;
$$;

Create the procedures

rebuild_schedule

The script creates the rebuild_schedule procedure. It takes the same three parameters as fill_schedule, delegates the row generation to it, and then scans the generated rows to build a few statistics.

SET search_path TO schedules;

CREATE OR REPLACE PROCEDURE rebuild_schedule(
    p_date_from    date,
    p_date_to      date,
    p_schedule_id  integer
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_time_sheet_date date;
    v_template_id     integer;
    v_day_span        integer;
    v_generated       integer := 0;

    v_rec             record;
    v_work_days       integer := 0;
    v_off_days        integer := 0;
    v_trip_days       integer := 0;
    v_other_days      integer := 0;
    v_min_date        date;
    v_max_date        date;

    v_status          varchar(20);
BEGIN
    IF p_date_from > p_date_to THEN
        RAISE EXCEPTION 'Invalid period: date_from (%) is later than date_to (%)',
            p_date_from, p_date_to
            USING ERRCODE = '22007';
    END IF;

    SELECT s.template_id
      INTO v_template_id
      FROM schedule AS s
     WHERE s.schedule_id = p_schedule_id;

    IF v_template_id IS NULL THEN
        RAISE EXCEPTION 'Schedule % was not found', p_schedule_id
            USING ERRCODE = 'P0002';
    END IF;

    v_time_sheet_date := get_first_day_of_month(p_date_from);
    v_day_span        := (p_date_to - p_date_from) + 1;

    RAISE NOTICE 'Rebuilding schedule % for % day(s): % .. % (timesheet anchor %)',
        p_schedule_id, v_day_span, p_date_from, p_date_to, v_time_sheet_date;

    v_generated := fill_schedule(p_date_from, p_date_to, p_schedule_id);

    FOR v_rec IN
        SELECT sd.date_out, sd.time_sheet_date, sd.absence_code
          FROM schedule_detail AS sd
         WHERE sd.schedule_id = p_schedule_id
           AND sd.date_out BETWEEN p_date_from AND p_date_to
         ORDER BY sd.date_out
    LOOP
        IF v_min_date IS NULL OR v_rec.date_out < v_min_date THEN
            v_min_date := v_rec.date_out;
        END IF;
        IF v_max_date IS NULL OR v_rec.date_out > v_max_date THEN
            v_max_date := v_rec.date_out;
        END IF;

        IF v_rec.absence_code = 'offdays' THEN
            v_off_days := v_off_days + 1;
        ELSIF v_rec.absence_code = 'business_trip' THEN
            v_trip_days := v_trip_days + 1;
        ELSIF v_rec.absence_code = 'sicklist' THEN
            v_work_days := v_work_days + 1;
        ELSE
            v_other_days := v_other_days + 1;
        END IF;
    END LOOP;

    IF v_generated = 0 THEN
        v_status := 'EMPTY';
    ELSIF v_work_days >= v_off_days THEN
        v_status := 'WORK_HEAVY';
    ELSE
        v_status := 'REST_HEAVY';
    END IF;

    RAISE NOTICE 'Done [%]: % row(s) over % .. % | work=% off=% trip=% other=%',
        v_status, v_generated, v_min_date, v_max_date,
        v_work_days, v_off_days, v_trip_days, v_other_days;

EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'rebuild_schedule failed for schedule %: % (SQLSTATE %)',
            p_schedule_id, SQLERRM, SQLSTATE;
END;
$$;

drop_schedule_table

The script creates the drop_schedule_table procedure. This is a small wrapper used only to debug the event trigger. An event trigger function is never called directly, so this procedure gives you something to start a debug session on and step into, reaching evt_protect_schedule_objects when it issues a dynamic DROP TABLE statement.

SET search_path TO schedules;

CREATE OR REPLACE PROCEDURE drop_schedule_table(p_table_name text)
LANGUAGE plpgsql
AS $$
BEGIN
    EXECUTE 'DROP TABLE ' || p_table_name;
END;
$$;

What the objects are for

Object	Type	Description
schedule_template_detail	Table	Stores templates of a working week. Every template is a set of days, each with a work shift code or an absence code.
schedule	Table	Stores schedules. Every schedule refers to the template it’s built from.
schedule_detail	Table	Stores one row per calendar day of a schedule.
trg_schedule_detail_before_ins	Trigger function	Fires as a row-level trigger function on schedule_detail (demonstrated in Debug a trigger function).
schedule_detail_before_ins	Trigger	Calls trg_schedule_detail_before_ins before every `INSERT` on schedule_detail.
evt_protect_schedule_objects	Event trigger function	Blocks dropping the core demo tables (demonstrated in Debug an event trigger function).
protect_schedule_objects	Event trigger	Calls evt_protect_schedule_objects on every `DROP` statement (`sql_drop`).
get_first_day_of_month	Function	Returns the first day of the calendar month a given date belongs to (demonstrated in Debug a function).
fill_schedule	Function	Generates the per-day rows for a period (demonstrated in Debug a function).
rebuild_schedule	Procedure	Orchestrates fill_schedule and reports statistics (demonstrated in Debug a procedure).
drop_schedule_table	Procedure	Wrapper that issues a dynamic `DROP TABLE`, used to reach evt_protect_schedule_objects from the debugger (demonstrated in Debug an event trigger function).