Создание триггера для партицирования по месяцам

gistfile1.java

CREATE OR replace FUNCTION "SC_Tag".server_partition_function ( )
RETURNS TRIGGER AS $body$ DECLARE _new_time timestamp;
_id INTEGER;
_tablename text;
_startdate text;
_month text;
_enddate text;
_result record;
BEGIN
    --Takes the CURRENT inbound "time" VALUE AND determines WHEN midnight IS FOR the given date
    _new_time : = NEW. "F_Date";
_id : = NEW. "ID_TagData";
_startdate : = _new_time::date;
_month : = to_char (
    _new_time,
    'YYYY-MM' );
_tablename : = 'tagdata_' || _month;
-- CHECK IF the PARTITION needed FOR the CURRENT record 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 = _tablename
    AND n.nspname = 'SC_Tag';
-- IF the PARTITION needed does NOT yet exist, THEN we CREATE it:
-- Note that || IS string concatenation (joining two strings TO make one)
IF NOT FOUND THEN _enddate: = to_char (
    _startdate::date + interval '1 month',
    'YYYY-MM' );
EXECUTE 'CREATE TABLE "SC_Tag".' || quote_ident (
    _tablename )
|| ' (
 CONSTRAINT ' || quote_ident (
    _tablename || '_pkey' )
|| ' PRIMARY KEY("ID_TagData"),
 CHECK ( to_char("F_Date", ''YYYY-MM'') >= ' || quote_literal (
    _month )
|| '
 AND to_char("F_Date", ''YYYY-MM'') < ' || quote_literal (
    _enddate )
|| '
 )
 ) INHERITS ("SC_Tag"."T_TagData")';
-- TABLE permissions are NOT inherited FROM the parent.
-- IF permissions CHANGE ON the master be sure TO CHANGE them ON the child ALSO.
--EXECUTE 'ALTER TABLE "SC_Tag".' || quote_ident(_tablename) || ' OWNER TO postgres';
--EXECUTE 'GRANT ALL ON TABLE "SC_Tag".' || quote_ident(_tablename) || ' TO my_role';
-- INDEXES are defined per child, so we assign a DEFAULT INDEX that uses the PARTITION COLUMNS
EXECUTE 'CREATE UNIQUE INDEX ' || quote_ident (
    _tablename || '_indx1' )
|| ' ON "SC_Tag".' || quote_ident (
    _tablename )
|| ' ("F_Date", "ID_TagData")';
END IF;
DELETE
FROM
    "SC_Tag"."T_TagData"
WHERE
    "ID_TagData" = _id;
-- INSERT the CURRENT record INTO the correct PARTITION, which we are sure will now exist.
EXECUTE 'INSERT INTO "SC_Tag".' || quote_ident (
    _tablename )
|| ' VALUES ($1.*)' USING NEW;
RETURN NULL;
END;
$body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 100;
----
CREATE TRIGGER server_master_trigger
BEFORE INSERT 
ON "SC_Tag"."T_TagData" FOR EACH ROW 
EXECUTE PROCEDURE "SC_Tag".server_partition_function();