Open
Description
I'm pretty new to pgTAP but I think there is no easy way to check if a trigger event is avaliable at a given table.
rules_are is similar but that's not working for triggers.
The line in question of a schema that the function will test is marked in the below example definition:
CREATE TRIGGER test_trigger
AFTER INSERT OR UPDATE ON test_table
FOR EACH ROW
EXECUTE FUNCTION test_trigger_function();
So the idea is to add a has_trigger_events function.
I think the following code is exactly implementing that function:
-- has_trigger_events_test.sql
BEGIN;
SELECT plan(13);
-- SELECT * FROM no_plan();
-- Define a test table
CREATE TABLE test_table (
id SERIAL PRIMARY KEY,
name TEXT
);
-- Create a trigger on the test table
CREATE OR REPLACE FUNCTION test_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
-- Trigger function logic here
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER test_trigger
AFTER INSERT OR UPDATE ON test_table
FOR EACH ROW
EXECUTE FUNCTION test_trigger_function();
-- has_trigger_events test function
-- Parameters:
-- p_table_name - name of table
-- p_trigger_name - name of trigger function
-- p_expected_events_array - array of events
-- p_test_description
-- Returns:
-- test_result text
CREATE OR REPLACE FUNCTION has_trigger_events(
p_table_name text,
p_trigger_name text,
p_expected_events_array text[],
p_test_description text
)
RETURNS TEXT AS $$
DECLARE
found_events TEXT[];
extra_events TEXT[];
missing_events TEXT[];
result TEXT;
BEGIN
-- Check if the trigger exists
IF NOT EXISTS (
SELECT 1
FROM information_schema.triggers
WHERE event_object_table = p_table_name
AND trigger_name = p_trigger_name
) THEN
RETURN ok(false, 'Trigger ' || p_trigger_name || ' does not exist on table ' || p_table_name);
END IF;
-- Fetch trigger events based on provided parameters
found_events := _get_trigger_events(p_table_name, p_trigger_name);
-- RAISE NOTICE 'test event: %', ARRAY_TO_STRING(found_events, ', ');
-- Compare expected events with found events to identify missing and extra events
missing_events := ARRAY(
SELECT unnest(p_expected_events_array)
EXCEPT
SELECT unnest(found_events)
);
extra_events := ARRAY(
SELECT unnest(found_events)
EXCEPT
SELECT unnest(p_expected_events_array)
);
-- Generate error message using _are function
result := _are(
'events',
extra_events,
missing_events,
p_test_description
);
RETURN result;
END;
$$ LANGUAGE plpgsql;
-- Reusable function to fetch trigger events based on provided parameters
-- Parameters:
-- p_table_name - name of table
-- p_triggger_name - name of trigger function
-- p_expected_timing - optional timing e.g. 'AFTER'
CREATE OR REPLACE FUNCTION _get_trigger_events(
p_table_name text,
p_trigger_name text,
p_expected_timing text DEFAULT NULL
)
RETURNS TEXT[] AS $$
DECLARE
trigger_timing_filter text;
BEGIN
IF p_expected_timing IS NOT NULL THEN
trigger_timing_filter := ' AND trigger_timing = ' || quote_literal(p_expected_timing);
ELSE
trigger_timing_filter := '';
END IF;
RETURN ARRAY(
SELECT upper(event_manipulation)
FROM information_schema.triggers
WHERE event_object_table = p_table_name
AND trigger_name = p_trigger_name
|| trigger_timing_filter
);
END;
$$ LANGUAGE plpgsql;
-- Test Case 1: No missing or extra events (Both INSERT and UPDATE are expected).
SELECT * FROM check_test(
has_trigger_events('test_table', 'test_trigger', ARRAY['INSERT', 'UPDATE'], 'should have no missing or extra events'),
true,
'has_trigger_event( tab, trigger, events, description )'
);
-- Test Case 2: Missing event (Only INSERT is expected).
-- counts as 3 tests has_trigger + description correct + diag correct
SELECT * FROM check_test(
has_trigger_events('test_table', 'test_trigger', ARRAY['INSERT'], 'should fail with extra event UPDATE'),
false,
'has_trigger_events( tab, trigger, events, description )',
'should fail with extra event UPDATE',
E' Extra events:\n "UPDATE"'
);
-- Test Case 3: Extra event (Only UPDATE is expected).
SELECT * FROM check_test(
has_trigger_events('test_table', 'test_trigger', ARRAY['UPDATE'], 'should fail with extra event INSERT'),
false,
'has_trigger_events( tab, trigger, events, description )',
'should fail with extra event INSERT',
E' Extra events:\n "INSERT"'
);
-- Test Case 4: Both missing and extra events (Only DELETE is expected).
SELECT * FROM check_test(
has_trigger_events('test_table', 'test_trigger', ARRAY['DELETE'], 'should fail with both missing and extra events'),
false,
'has_trigger_events( tab, trigger, events, description )',
'should fail with both missing and extra events',
E' Extra events:\n "INSERT"\n "UPDATE"\n Missing events:\n "DELETE"'
);
-- Test Case 5: Trigger does not exist.
SELECT * FROM check_test(
has_trigger_events('test_table', 'non_existent_trigger', ARRAY['INSERT'], 'should fail - trigger does not exist'),
false,
'has_trigger_events( tab, non_existent_trigger, events, description )'
);
-- Test Case 6: Table does not exist.
SELECT * FROM check_test(
has_trigger_events('non_existent_table', 'test_trigger', ARRAY['INSERT'], 'should fail - table does not exist'),
false,
'has_trigger_events( non_existent_table, trigger, events, description )'
);
-- Test Case 7: Empty events.
SELECT * FROM check_test(
has_trigger_events('test_table', 'test_trigger', ARRAY[]::TEXT[], 'should fail with both missing and extra events'),
false,
'has_trigger_events( table, trigger, ARRAY[], description )'
);
SELECT * FROM finish();
ROLLBACK;
-- End of test caseI haven't checked how to add it to pgTAP but if there is a guide on how to contribute, I could create a PR if this feature is interesting for pgTAP.
If this function is not interesting, that's also no problem.
The tests above are covering all cases.
Topics not covered in the function
- Trigger timing BEFORE / AFTER not added to
has_trigger_events, would be possible but makes thehas_trigger_eventsusage more complicated. Maybe this would be better to add as additional test function. - Trigger definition e.g.
FOR EACH ROWnot covered inhas_trigger_events- would be better in a separatehas_trigger_definitionfunction hasnt_trigger_eventsnot added yet - is it needed? How to add without duplicating thehas_trigger_eventslogic e.g. refactor/rename tocheck_trigger_eventsand add an arg.p_check_presenceto check has or hasn't based on it.
The mentioned missing parts would be nice to have but I think they're all optional.
Metadata
Metadata
Assignees
Labels
No labels