Postgresql – use a rule to insert aggregate data into a new table

insertpostgresqltrigger

I can use a RULE for bulk inserts into partitioned tables. For that I used something like

DO INSTEAD
INSERT INTO child_table (col1, col2)
VALUES (NEW.col1, NEW.col2)

From the RULES docs:

command
The command or commands that make up the rule action. Valid commands are SELECT, INSERT, UPDATE, DELETE, or NOTIFY.

How do I write something like?

DO ALSO
INSERT INTO agg_table (col1, agg_col)
VALUES (NEW.col1, AVG(NEW.col2))
GROUP BY NEW.col1

Which gives me a syntax error at GROUP

Best Answer

Trigger Transition Tables

First you should be using TRIGGER and not rules when you can. Second, what you want is possible with version 10's, REFERENCING

The REFERENCING option enables collection of transition relations, which are row sets that include all of the rows inserted, deleted, or modified by the current SQL statement. This feature lets the trigger see a global view of what the statement did, not just one row at a time. This option is only allowed for an AFTER trigger that is not a constraint trigger; also, if the trigger is an UPDATE trigger, it must not specify a column_name list. OLD TABLE may only be specified once, and only for a trigger that can fire on UPDATE or DELETE; it creates a transition relation containing the before-images of all rows updated or deleted by the statement. Similarly, NEW TABLE may only be specified once, and only for a trigger that can fire on UPDATE or INSERT; it creates a transition relation containing the after-images of all rows updated or inserted by the statement.

CREATE TRIGGER AFTER INSERT
  ON tblfoo
  REFERENCING NEW TABLE as new
  FOR EACH STATEMENT
  EXECUTE PROCEDURE whatever();

Now whatever has access to new. You can select the avg() and put it wherever you want.

See also,

Related Question