readme.md

This is a set of functions for doing data based partitioning in postgres.

usage

for the insert trigger

CREATE TRIGGER [partition_trigger]
  BEFORE INSERT
  ON [table]
  FOR EACH ROW
  EXECUTE PROCEDURE partition_function('[row name]','[schema name]');

PartitioningFunciton.sql

CREATE OR REPLACE FUNCTION partition_function( )
  RETURNS trigger AS
$BODY$
DECLARE
_tablename text;
_dataval text;
_schema text;
_colName text;

BEGIN

-- Name of the column that is being partitioned on
_colName := TG_ARGV[0];
-- Set _dataval to the value we are using for partitioning
EXECUTE 'SELECT $1.' || quote_ident(_colName)
USING NEW 
INTO _dataval;
-- The schema the partition tables are in
_schema := TG_ARGV[1];
-- The name of the current partition table
_tablename := _schema || '.' || quote_ident(TG_TABLE_NAME||_dataval);

-- First check if the partition table already exists
PERFORM 1
FROM   pg_catalog.pg_class c
JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relkind = 'r'
AND    c.relname = TG_TABLE_NAME||_dataval
AND    n.nspname = _schema;

-- If the partition table does not exist then create it
IF NOT FOUND THEN
  -- The table should inherit from the original table. This allows selects and inserts to be done against the original table 
  EXECUTE 'CREATE TABLE ' || _tablename || '(CHECK (' || _colName || ' = '''|| _dataval ||''')) INHERITS (' || TG_TABLE_SCHEMA || '.' || quote_ident(TG_TABLE_NAME) || ')';

  EXECUTE 'ALTER TABLE ' || _tablename || ' OWNER TO postgres';

  -- This index will give us a speed increase when doing selects against the partition column
  EXECUTE 'CREATE INDEX ' || quote_ident(TG_TABLE_NAME||_dataval||'_indx1') || ' ON ' || _tablename || ' (' || _colName || ', id)';
END IF;

-- Then insert the data into the partition table
EXECUTE 'INSERT INTO ' || _tablename || ' VALUES ($1.*)' USING NEW;

-- Let the user know where their data was really inserted
RAISE NOTICE 'row inserted into child table %', _tablename;

-- Return null so that it doesn't insert anything into the original table
RETURN null;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION partitiontest_partition_function()
  OWNER TO postgres;