In
an earlier installment, we learned about how to partition a
referenced table, which was good as far as it went. A commenter very
astutely pointed out that this didn't work with RETURNING, so I set
to work fixing that problem.
Here's what I came up with.
The essential problem with the marshalling trigger function is that it
suppresses all that might have been returned, including useful things,
so my solution is to let those writes actually happen, but get cleaned
up before they're visible to the next statement. We do this by
rewriting the original trigger function to allow the INSERTs on the
parent table, and then write a new cleanup trigger which fires after
the statement to clean up those rows. This way, RETURNING works, and
the partition marshalling does, too.
Here's the new function for the old trigger. The only difference from
the old function is that instead of returning NULL, it returns NEW.
CREATE OR REPLACE FUNCTION marshal_p()RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.id % 5 = 0
THEN INSERT INTO p0 (id, t) VALUES (NEW.id, NEW.t);
ELSIF NEW.id % 5 = 1
THEN INSERT INTO p1 (id, t) VALUES (NEW.id, NEW.t);
ELSIF NEW.id % 5 = 2
THEN INSERT INTO p2 (id, t) VALUES (NEW.id, NEW.t);
ELSIF NEW.id % 5 = 3
THEN INSERT INTO p3 (id, t) VALUES (NEW.id, NEW.t);
ELSIF NEW.id % 5 = 4
THEN INSERT INTO p4 (id, t) VALUES (NEW.id, NEW.t);
END IF;
RETURN *NEW*;
END;
$$;
Next, we'll add another function.
CREATE OR REPLACE FUNCTION cleanup_p()RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
DELETE FROM ONLY p;
RETURN OLD;
END;
$$;
and a trigger to call it:
CREATE TRIGGER insert_p_cleanup_trg
AFTER INSERT ON p
FOR EACH STATEMENT
EXECUTE PROCEDURE cleanup_p();
and we're done.
...until the next time.
I see RETURN NEW first time. What is it?