Could you explain why for the following PostgreSQL function:
CREATE FUNCTION components.remove_empty_gaps()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
current_page_id UUID;
deleted_row_start INT;
deleted_row_end INT;
row_span INT;
BEGIN
current_page_id := OLD.page_id;
SELECT row_start, row_end INTO deleted_row_start, deleted_row_end
FROM components.component_position
WHERE component_id = OLD.id;
row_span := deleted_row_end - deleted_row_start + 1;
IF OLD.type = 'header' OR OLD.type = 'section' THEN
UPDATE components.component_position cp
SET row_start = row_start - row_span,
row_end = row_end - row_span
FROM components.component c
WHERE cp.row_start > deleted_row_end
AND cp.component_id = c.id
AND c.page_id = current_page_id
AND c.type != 'footer';
END IF;
RETURN OLD;
END;
$$;
These two statements behave differently?
CREATE TRIGGER remove_empty_gaps
BEFORE DELETE ON components.component
FOR EACH STATEMENT
EXECUTE FUNCTION components.remove_empty_gaps();
CREATE TRIGGER remove_empty_gaps
BEFORE DELETE ON components.component
FOR EACH ROW
EXECUTE FUNCTION components.remove_empty_gaps();
For example, given this data in the component_position
table:
{
"component_position": [
{
"component_id" : "39dadec0-24b7-40cd-9fb9-41cbc17a2460",
"row_start" : 1,
"col_start" : 1,
"row_end" : 2,
"col_end" : 13,
"row_end_span" : 0,
"col_end_span" : 0
},
{
"component_id" : "54718411-dfb1-40a3-ba01-8703e435ed33",
"row_start" : 3,
"col_start" : 1,
"row_end" : 4,
"col_end" : 13,
"row_end_span" : 0,
"col_end_span" : 0
},
{
"component_id" : "857df909-fb40-4d24-86e6-992110ee12df",
"row_start" : 5,
"col_start" : 1,
"row_end" : 6,
"col_end" : 13,
"row_end_span" : 0,
"col_end_span" : 0
},
{
"component_id" : "7a00db94-53f2-41df-8ca7-e0b9764e5996",
"row_start" : 7,
"col_start" : 1,
"row_end" : 8,
"col_end" : 13,
"row_end_span" : 0,
"col_end_span" : 0
}
]}
When using the FOR EACH ROW and deleting component 54718411-dfb1-40a3-ba01-8703e435ed33
, the unexepcted output would look like this:
{
"component_position": [
{
"component_id" : "39dadec0-24b7-40cd-9fb9-41cbc17a2460",
"row_start" : 1,
"col_start" : 1,
"row_end" : 2,
"col_end" : 13,
"row_end_span" : 0,
"col_end_span" : 0
},
{
"component_id" : "857df909-fb40-4d24-86e6-992110ee12df",
"row_start" : 3,
"col_start" : 1,
"row_end" : 4,
"col_end" : 13,
"row_end_span" : 0,
"col_end_span" : 0
},
{
"component_id" : "7a00db94-53f2-41df-8ca7-e0b9764e5996",
"row_start" : 3,
"col_start" : 1,
"row_end" : 4,
"col_end" : 13,
"row_end_span" : 0,
"col_end_span" : 0
}
]}
While the FOR EACH STATEMENT with deleting 54718411-dfb1-40a3-ba01-8703e435ed33
, the output is correct with the proper shifting:
{
"component_position": [
{
"component_id" : "39dadec0-24b7-40cd-9fb9-41cbc17a2460",
"row_start" : 1,
"col_start" : 1,
"row_end" : 2,
"col_end" : 13,
"row_end_span" : 0,
"col_end_span" : 0
},
{
"component_id" : "857df909-fb40-4d24-86e6-992110ee12df",
"row_start" : 3,
"col_start" : 1,
"row_end" : 4,
"col_end" : 13,
"row_end_span" : 0,
"col_end_span" : 0
},
{
"component_id" : "7a00db94-53f2-41df-8ca7-e0b9764e5996",
"row_start" : 5,
"col_start" : 1,
"row_end" : 6,
"col_end" : 13,
"row_end_span" : 0,
"col_end_span" : 0
}
]}
I thought in this case FOR EACH ROW should give the exact same result as FOR EACH STATEMENT considering I only delete a single row.