PLlua procedure and trigger to automatically create partition tables in Postgresql

partition_trigger.sql.lua

--- vim: set nosta noet ts=4 sw=4 ft=lua:
---
--- Hello.  I'm a partitioner that puts rows into dated tables, segmented by date ranges.
--- Install me like so on your master table, as a db admin user (for the security definer):
---
---   CREATE TRIGGER auto_partition BEFORE INSERT ON [table] FOR EACH ROW EXECUTE PROCEDURE auto_partition()
---

CREATE OR REPLACE FUNCTION clear_partition_cache()
RETURNS boolean
LANGUAGE pllua
SECURITY DEFINER
AS $$
-- If you manually delete partitioned tables, this function
-- clears the lookup cache of the tables that it knows about.
if shared.auto_partition then shared.auto_partition = nil end
return true
$$;

CREATE OR REPLACE FUNCTION auto_partition()
RETURNS TRIGGER
LANGUAGE pllua
SECURITY DEFINER
AS $$
	--[[ ===================================================================================
	V A R I A B L E S
	=================================================================================== --]]

	-- The owner of this table.  Inheritence doesn't include owner info,
	-- so it is set explicitly upon child table creation.
	--
	local owner = 'static'

	-- The date naming convention of the child table, in strftime format.
	--
	-- table --> table_2012_06
	--
	local child_format = "_%Y_%m"

	-- A PostgreSQL interval for check constraints.
	-- This should match the table naming convention.
	--
	-- Examples:
	--
	--  # create a new table every year
	--  child_format = "_%Y"
	--  interval = '1 year'
	--
	--  # create a new table every month
	--  child_format = "_%Y_%m"
	--  interval = '1 month'
	--
	--  # create a new table every week
	--  child_format = "_%Y_%m_%U"
	--  interval = '1 week'
	--
	local interval = '1 month'


	---------------------------------------------------------
	-- [[ shouldn't need to edit anything else, really. ]] --
	---------------------------------------------------------

	-- The name of the table the insertion was taking place on.
	local orig_table = trigger.relation.name
	
	-- The name of the table we're really going to insert into.
	local insert_table = orig_table .. os.date( child_format )


	--[[ ===================================================================================
	H E L P E R  F U N C T I O N S
	=================================================================================== --]]

	--  Run a DB query after formatting.
	--
	local execute = function( ... ) return server.execute( string.format(...) ) end


	-- Return the first column value.  Requires the query to alias to 'val'.
	--
	local get_val = function( ... )
		local row = execute( ... )
		if row then return row[1].val end
		return nil
	end

	-- Debug logging, after formatting.
	--
	local log = function( ... ) print( string.format(...) ) end

	-- Safety check.
	--
	if trigger.operation ~= "insert" then
		log( "Uh oh!  Trigger applied to the wrong operation type? (Should just be on INSERT)" )
		return trigger.row
	end


	--[[ ===================================================================================
	C A C H I N G
	=================================================================================== --]]

	-- Cache prep
	--
	if not shared.auto_partition then setshared( 'auto_partition', {} ) end
	if not shared.auto_partition[ orig_table ] then shared.auto_partition[ orig_table ] = {} end
	local cache = shared.auto_partition[ orig_table ]

	-- Remember the DDL of the parent table, so we can prepare the
	-- insertion with the proper table types and escaping.
	--
	if not cache.ddl then
		log( "Parent table (%s) DDL not cached.  Retreiving it.", orig_table )
		local ddl = {}
		for row in server.rows(string.format([[
			SELECT a.attname as name, t.typname as dtype
			FROM pg_class AS c
			LEFT JOIN pg_attribute AS a ON a.attrelid = c.oid AND a.attnum > 0
			LEFT JOIN pg_type as t ON a.atttypid = t.oid
			WHERE c.relname = '%s']], orig_table )) do

			local name  = tostring( row['name'] )
			local dtype = tostring( row['dtype'] )

			-- no fromstring/tostring converters for these types?
			if dtype == 'bpchar'  then dtype = 'text' end
			if dtype == 'varchar' then dtype = 'text' end

			log( "DDL: %s --> %s", name, dtype )
			ddl[ name ] = dtype
		end
		cache.ddl = ddl
	end

	-- Remember child table creations in the PL shared area, to avoid
	-- having to perform a SELECT on pg_class for each and every insert.
	--
	if not cache[ insert_table ] then
		log( "Partition cache doesn't contain key for '%s -> %s', doing explicit check",
			orig_table, insert_table )
		local exists = get_val([[
			SELECT relname AS val FROM pg_class WHERE relname = '%s' ]], insert_table )
		if exists then
			log( "Child table '%s' exists!", insert_table )
			cache[ insert_table ] = exists
		end
	end



	--[[ ===================================================================================
	T H E  G U T S !
	=================================================================================== --]]

	-- Convert the trigger userdata to a regular lua table for easier
	-- massaging.  This will only build pairs for inserted values.
	--
	local tups = {}
	for k,v in pairs(trigger.relation.attributes) do
		local value = trigger.row[ k ]
		if value then
			tups[ k ] = value
		end
	end


	-- If the destination table doesn't exist, create it.
	--
	if not cache[ insert_table ] then
		log( "Creating partitioned table '%s'", insert_table )

		-- Find the valid date ranges for constraint exclusion.
		-- This trigger only goes to a day's resolution.
		--
		local valid_start = get_val( "SELECT to_char( NOW(), 'YYYY-MM-DD' ) AS val" )
		local valid_end   = get_val( "SELECT to_char( NOW() + '%s'::interval, 'YYYY-MM-DD' ) AS val", interval )

		execute([[
			CREATE TABLE "%s" (
				LIKE "%s" INCLUDING INDEXES,
				CHECK (
					ts >= '%s'::timestamptz AND
					ts <  '%s'::timestamptz
				)
			)
			INHERITS( "%s" );
		]], insert_table, orig_table, valid_start, valid_end, orig_table )

		execute( "ALTER TABLE \"%s\" OWNER TO %s", insert_table, owner );

		-- cache
		cache[ insert_table ] = true
	end


	-- Use index positions for table.concat()
	--
	local cols, types, vals, pholds = {}, {}, {}, {}
	local i = 1
	for col,val in pairs( tups ) do
		cols[i]   = col
		types[i]  = cache.ddl[ col ]
		vals[i]   = val
		pholds[i] = '$'..i
		i = i + 1
	end


	-- Redirect the insert into the right spot.
	--
	local insert = string.format([[ INSERT INTO "%s" ( %s ) VALUES ( %s ) ]],
			insert_table, table.concat( cols, ', ' ), table.concat( pholds, ', ' ))
	-- print( insert );
	local plan = server.prepare( insert, types )
	plan:execute( vals )


	-- Wipe the original row, so nothing is inserted into the parent table.
	--
	trigger.row = nil

$$;

Source : http://projects.martini.nu/staticcling/browser/misc/partition_trigger.sql.lua?rev=32
It doesn't work with Arrays.