Skip to content

Instantly share code, notes, and snippets.

@vincentdesmares
Created May 11, 2017 22:58
Show Gist options
  • Save vincentdesmares/26b26686a51aa911a59688990b2cf7a6 to your computer and use it in GitHub Desktop.
Save vincentdesmares/26b26686a51aa911a59688990b2cf7a6 to your computer and use it in GitHub Desktop.
-- Attach a magic function to the insert of this table:
CREATE OR REPLACE FUNCTION create_partition_and_insert()
RETURNS TRIGGER AS
$BODY$
DECLARE
partition VARCHAR(25);
BEGIN
partition := TG_RELNAME || '_' || NEW.period || ‘p’;
IF NOT EXISTS(SELECT relname
FROM pg_class
WHERE relname = partition)
THEN
RAISE NOTICE 'A partition has been created %', partition;
EXECUTE 'CREATE TABLE ' || partition || ' (check (period = ''' || NEW.period || ''')) INHERITS (' || TG_RELNAME ||
');';
END IF;
EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').*;';
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment