सेक्शनिंग: शॉट और भूल गए

 CREATE OR REPLACE FUNCTION to_timestamp(timestamp without time zone) RETURNS timestamp with time zone AS $_$ select $1::timestamp with time zone; $_$ LANGUAGE sql STABLE STRICT; CREATE OR REPLACE FUNCTION to_timestamp(timestamp with time zone) RETURNS timestamp with time zone AS $_$ select $1::timestamp with time zone; $_$ LANGUAGE sql STABLE STRICT; CREATE OR REPLACE FUNCTION to_numeric(text) RETURNS numeric AS $_$ select regexp_replace($1, '\D+', '', 'g')::numeric; $_$ LANGUAGE sql STABLE STRICT; CREATE OR REPLACE FUNCTION copy_constraints(srcoid oid, dstoid oid) RETURNS integer AS $BODY$ declare i int4 := 0; constrs record; srctable text; dsttable text; begin srctable = srcoid::regclass; dsttable = dstoid::regclass; for constrs in select conname as name, pg_get_constraintdef(oid) as definition from pg_constraint where conrelid = srcoid loop begin execute 'alter table ' || dsttable || ' add constraint ' || replace(replace(constrs.name, srctable, dsttable),'.','_') || ' ' || constrs.definition; i = i + 1; exception when duplicate_table then end; end loop; return i; exception when undefined_table then return null; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE; CREATE OR REPLACE FUNCTION copy_constraints(src text, dst text) RETURNS integer AS $BODY$ begin return copy_constraints(src::regclass::oid, dst::regclass::oid); end; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE; CREATE OR REPLACE FUNCTION copy_indexes(srcoid oid, dstoid oid) RETURNS integer AS $BODY$ declare i int4 := 0; indexes record; srctable text; dsttable text; script text; begin srctable = srcoid::regclass; dsttable = dstoid::regclass; for indexes in select c.relname as name, pg_get_indexdef(idx.indexrelid) as definition from pg_index idx, pg_class c where idx.indrelid = srcoid and c.oid = idx.indexrelid loop script = replace (indexes.definition, ' INDEX ' || indexes.name, ' INDEX ' || replace(replace(indexes.name, srctable, dsttable),'.','_')); script = replace (script, ' ON ' || srctable, ' ON ' || dsttable); begin execute script; i = i + 1; exception when duplicate_table then end; end loop; return i; exception when undefined_table then return null; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE; CREATE OR REPLACE FUNCTION copy_indexes(src text, dst text) RETURNS integer AS $BODY$ begin return copy_indexes(src::regclass::oid, dst::regclass::oid); end; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE; CREATE OR REPLACE FUNCTION copy_triggers(srcoid oid, dstoid oid) RETURNS integer AS $BODY$ declare i int4 := 0; triggers record; srctable text; dsttable text; script text = ''; begin srctable = srcoid::regclass; dsttable = dstoid::regclass; for triggers in select tgname as name, pg_get_triggerdef(oid) as definition from pg_trigger where tgrelid = srcoid loop script = replace (triggers.definition, ' TRIGGER ' || triggers.name, ' TRIGGER ' || replace(replace(triggers.name, srctable, dsttable),'.','_')); script = replace (script, ' ON ' || srctable, ' ON ' || dsttable); begin execute script; i = i + 1; exception when duplicate_table then end; end loop; return i; exception when undefined_table then return null; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE; CREATE OR REPLACE FUNCTION copy_triggers(src text, dst text) RETURNS integer AS $BODY$ begin return copy_triggers(src::regclass::oid, dst::regclass::oid); end; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE; 

 CREATE OR REPLACE FUNCTION "create_partition" (in tbl varchar, in method varchar, in params varchar, in sample text) RETURNS text AS $BODY$ declare scheme varchar := split_part(tbl, '.', 1); parent varchar := split_part(tbl, '.', 2); field varchar := split_part(tbl, '.', 3); child varchar; script text; trig text; part text[]; begin execute 'select partition_'||method||'('''||params||''', '''||field||''', '''||sample||''')' into part; -- RAISE EXCEPTION 'part %', part; child = scheme || '.' || parent || '_' || (part[1]::text); execute 'create table IF NOT EXISTS ' || child || ' ( constraint partition_' || (part[1]) || ' check ' || (part[2]) || ' ) inherits (' || parent || ')'; perform copy_constraints(parent, child); perform copy_indexes(parent, child); -- execute 'GRANT SELECT ON ' || child || ' TO some_other_user'; -- execute 'GRANT ALL ON ' || child || ' TO user'; script = (select string_agg(c, chr(10)||' ELS') from ( select to_numeric(replace(t.table_name, parent||'_','')) as n, 'IF '||replace(left(right(cc.check_clause, -1), -1), c.column_name, 'NEW.'||c.column_name)||' THEN INSERT INTO '||t.table_schema||'.'||t.table_name||' VALUES (NEW.*);' as c from information_schema.TABLE_CONSTRAINTS t join information_schema.CONSTRAINT_COLUMN_USAGE c ON t.constraint_name = c.constraint_name join information_schema.check_constraints cc ON t.constraint_name = cc.constraint_name where constraint_type IN ('CHECK') and t.table_name like parent||'\_%' group by t.table_schema, t.table_name, c.column_name, cc.check_clause order by n desc) t); trig = 'trig_partition_'||parent||'_'||field; execute 'CREATE OR REPLACE FUNCTION '||trig||'() RETURNS TRIGGER AS $BODY2$ declare child text; begin '||script||' ELSE EXECUTE ''SELECT create_partition('''''||tbl||'''''::text, '''''||method||'''''::text, '''''||params||'''''::text , ''''''||NEW.'||field||'||''''''::text)'' INTO child; EXECUTE ''INSERT INTO ''|| child || '' SELECT $1.*'' USING NEW; END IF; RETURN NULL; end; $BODY2$ LANGUAGE ''plpgsql'' VOLATILE;'; execute 'DROP TRIGGER IF EXISTS t_'||trig||' ON "'||parent||'" CASCADE'; execute 'CREATE TRIGGER t_'||trig||' BEFORE INSERT ON "'||parent||'" FOR EACH ROW EXECUTE PROCEDURE '||trig||'();'; return child; end; $BODY$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION "partition_date" (in params text, in field varchar, in sample timestamp with time zone) RETURNS text[] AS $BODY$ declare period varchar:= split_part(params, ',', 1); fmt varchar := split_part(params, ',', 2); clock timestamp with time zone := to_timestamp(sample); delta varchar := '1 '||period; suffix varchar; check_beg varchar; check_end varchar; condition varchar; begin -- RAISE EXCEPTION 'period %, fmt %, clock %', period, fmt, clock; check_beg = date_trunc(period, clock); check_end = date_trunc(period, clock + delta::interval); suffix = to_char (clock, fmt); condition = '( ' || field || ' >= ' || quote_literal (check_beg) || ' and ' || field || ' < ' || quote_literal (check_end) || ' )'; return ARRAY[suffix, condition]; end; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE; CREATE OR REPLACE FUNCTION "partition_digits" (in params text, in field varchar, in sample numeric) RETURNS text[] AS $BODY$ declare len int := split_part(params, ',', 1)::int; pref int := split_part(params, ',', 2)::int; norm text := to_char(sample::numeric, 'FM000000000000000000000'); suffix text := regexp_replace(left(norm, -1*(len-pref)), '0*(?=\d{'||pref::text||'})', ''); check_beg varchar; check_end varchar; condition varchar; begin check_beg = (trunc(norm::numeric, -1*(len-pref)))::numeric::text; check_end = (check_beg::numeric+10^(len-pref))::numeric::text; condition = '( ' || field || ' >= ' || check_beg || ' and ' || field || ' < ' || check_end || ' )'; return ARRAY[suffix, condition]; end; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE; 

 DROP TABLE IF EXISTS "public"."test1" CASCADE; CREATE TABLE "public"."test1" ( "id" serial, "cdate" timestamp with time zone, "text" text, CONSTRAINT "test11_pkey" PRIMARY KEY (id) ) WITH OIDS; CREATE INDEX test_idx_cdate ON test1 USING btree (cdate); -- select create_partition('public.test1.cdate', 'date', 'day,YYYY_MM_DD', now()::text); -- select create_partition('public.test1.cdate', 'date', 'week,YYYY_IW', now()::text); select create_partition('public.test1.cdate', 'date', 'month,YYYY_MM', now()::text); -- insert into test1 (cdate, text) SELECT cdate, md5(random()::text) as text FROM generate_series('2012-01-01'::date, now(), '1 day'::interval) cdate; 

 DROP TABLE IF EXISTS "public"."test2" CASCADE; CREATE TABLE "public"."test2" ( "id" bigserial, "text" text, CONSTRAINT "test2_pkey" PRIMARY KEY (id) ) WITH OIDS; select create_partition('public.test2.id', 'digits', '10,2', 1::int); insert into test2 values(10000000, 'test2'); -- insert into test2 (id, text) SELECT t.id, md5(random()::text) as text FROM generate_series(0, 100000000000, 1000000000) as t(id) limit 200;