Make Postgresql partition table create in to a function

要建立Postgresql的partition雖然語法不覆雜，但是若要針對不一樣的table,pkey,index來建會就變的無法很自動，
因此可以用下面此function來簡化所有開立的流程(ps.因為目前系統並沒有parent table的check，因此此function需再自行加上
parent table的check



CREATE OR REPLACE FUNCTION dbaegis_gen_partition_table_f(p_table_name varchar,p_part_col varchar, p_part_date varchar)
RETURNS varchar
LANGUAGE plpgsql
AS
$body$
DECLARE
/*
Description: To Generate the partition table
Usage: select dbaegis_gen_partition_table_f(p_table_name,'sql_login_utc','20110320');
to generate partition table for sql_conns on 2011/3/02
use sql to get the result:true/false
Creation: 2011-04-07 By Marc
*/

r1 record;

ls_current_statement VARCHAR(2000) default '';
v_begin_utc int8;
v_end_utc int8;
v_part_date_str varchar(12);
v_table_sql varchar(1024);
v_pkey_sql varchar(1024);
v_index_sql varchar(1024);
v_grant_sql varchar(1024);

BEGIN

ls_current_statement = 'Validate Date';
select extract(epoch from p_part_date::timestamp) into v_begin_utc;
select extract(epoch from p_part_date::timestamp)-86400 into v_end_utc;
select to_char( p_part_date::timestamp,'YYYY_MM_DD') into v_part_date_str;

ls_current_statement = 'Generate Create table sql';

SELECT 'CREATE TABLE ' ||relname|| '_'||v_part_date_str||' ('||
' CHECK( '||p_part_col||' >= '||v_begin_utc||' AND '||p_part_col||' < '||v_end_utc||' )'||
') INHERITS (' ||relname|| ');' AS table_sql
into v_table_sql
FROM pg_class
WHERE relname = p_table_name;

raise notice '%',v_table_sql;
execute v_table_sql;


ls_current_statement = 'Generate Create primary key sql';
--find Primary Key
SELECT 'ALTER TABLE ONLY ' ||ct.relname|| '_'||v_part_date_str||' ADD CONSTRAINT partition_'||v_part_date_str||'_from_' ||ci.relname|| ' PRIMARY KEY (' ||dbaegis_aggregate_concate (a.attname) || ');' AS pkey_sql
into v_pkey_sql
FROM pg_catalog.pg_namespace n,
pg_catalog.pg_class ct,
pg_catalog.pg_class ci,
pg_catalog.pg_attribute a,
pg_catalog.pg_index i
WHERE ct.oid = i.indrelid
AND ci.oid = i.indexrelid
AND a.attrelid = ci.oid
AND i.indisprimary
AND ct.relname = p_table_name
AND ct.relnamespace = n.oid
AND n.nspname = 'public'
GROUP BY ct.relname, ci.relname;

raise notice '%',v_pkey_sql;
execute v_pkey_sql;

ls_current_statement = 'Generate Create index sql';
for r1 in
SELECT 'CREATE ' ||case i.indisunique WHEN TRUE THEN 'unique ' ELSE '' end|| 'INDEX partition_'||v_part_date_str||'_from_' ||ci.relname|| ' ON ' ||ct.relname|| '_'||v_part_date_str||' USING btree (' ||CASE WHEN i.indexprs IS NULL THEN a.attname ELSE pg_get_indexdef (ci.oid,a.attnum,FALSE) END|| ');' AS index_sql
FROM pg_catalog.pg_namespace n,
pg_catalog.pg_class ct,
pg_catalog.pg_class ci,
pg_catalog.pg_attribute a,
pg_catalog.pg_am am,
pg_catalog.pg_index i
WHERE ct.oid = i.indrelid
AND ci.oid = i.indexrelid
AND a.attrelid = ci.oid
AND ci.relam = am.oid
AND n.oid = ct.relnamespace
AND n.nspname = 'public'
AND ct.relname = p_table_name
AND i.indisprimary = FALSE
ORDER BY i.indisunique desc, i.indisclustered, ci.relname loop
v_index_sql = r1.index_sql;

raise notice '%',v_index_sql;
execute v_index_sql;
end loop;

ls_current_statement = 'Generate Create table grant sql';
SELECT 'GRANT ' ||dbaegis_aggregate_concate (privilege_type) || ' ON TABLE ' ||table_name|| '_'||v_part_date_str||' TO webui;' AS grant_sql
into v_grant_sql
FROM information_schema.table_privileges
WHERE table_schema = 'public'
AND grantee = 'webui'
AND table_name = p_table_name
GROUP BY table_schema, table_name;

raise notice '%',v_grant_sql;
execute v_grant_sql;

return p_table_name|| '_'||v_part_date_str;

EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION '<>', ls_current_statement || ' ErrCode: ' || SQLSTATE || '-' || SQLERRM;
return '';
END;
$body$;
/