PostgreSQL 10 High Performance

Dynamic trigger functions

The orders_insert_trigger() function shown previously is static; the statements it executes are the same every time. As you might expect from reading the code, the actual execution time will vary based on which partition you are inserting into, and maintaining that trigger code is both monotonous and error prone. It's possible to remove the maintenance chore by just directly computing the partition required:

CREATE OR REPLACE FUNCTION orders_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
   ins_sql TEXT;
BEGIN
   ins_sql := 
       'INSERT INTO orders_'|| to_char(NEW.orderdate, 'YYYY_MM') ||
       '(orderid,orderdate,customerid,net_amount,tax,totalamount)  VALUES ' || '('|| NEW.orderid || ',' || quote_literal(NEW.orderdate) || ','  ...