Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Создание триггера для партицирования по месяцам
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();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.