{"id":177957,"date":"2013-04-30T12:54:04","date_gmt":"2013-04-30T08:54:04","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=177957"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=177957","title":{"rendered":"<span class=\"post_title\">\u0421\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0438 \u00ab\u0436\u0438\u0432\u044b\u0435 \u0441\u043d\u0438\u043c\u043a\u0438\u00bb \u0434\u0430\u043d\u043d\u044b\u0445 \u0432 PostgreSQL<\/span>"},"content":{"rendered":"<div class=\"content html_format\">   \t\u0425\u043e\u0442\u044f \u0442\u0435\u043c\u0430 \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u0443\u0436\u0435 \u043f\u043e\u0434\u043d\u0438\u043c\u0430\u043b\u0430\u0441\u044c <a href=\"http:\/\/habrahabr.ru\/post\/74984\/\">\u0440\u0430\u043d\u0435\u0435<\/a>, \u044f \u0445\u043e\u0447\u0443 \u043a \u043d\u0435\u0439 \u0432\u0435\u0440\u043d\u0443\u0442\u044c\u0441\u044f, \u0447\u0442\u043e\u0431\u044b \u0440\u0430\u0441\u0441\u043a\u0430\u0437\u0430\u0442\u044c \u043e \u0441\u0432\u043e\u0435\u043c \u043e\u043f\u044b\u0442\u0435 \u0440\u0435\u0448\u0435\u043d\u0438\u044f \u044d\u0442\u043e\u0439 \u0437\u0430\u0434\u0430\u0447\u0438, \u0432\u043e\u0437\u043d\u0438\u043a\u0448\u0435\u0439 \u0432 \u0441\u0432\u044f\u0437\u0438 \u0441 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e\u0441\u0442\u044c\u044e \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u043e\u0439 \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u043a\u043e\u0439 \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u043e\u0431\u044a\u0435\u043c\u043e\u0432 \u0434\u0430\u043d\u043d\u044b\u0445. \u041f\u043e\u043c\u0438\u043c\u043e \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f, \u044f \u0440\u0430\u0441\u0441\u043c\u043e\u0442\u0440\u044e \u043f\u0440\u0435\u0434\u0435\u043b\u044c\u043d\u043e \u0443\u043f\u0440\u043e\u0449\u0435\u043d\u043d\u0443\u044e \u0440\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u044e \u00ab\u0441\u043d\u0438\u043c\u043a\u043e\u0432\u00bb \u0430\u0433\u0440\u0435\u0433\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0445 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432, \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u043e\u0431\u043d\u043e\u0432\u043b\u044f\u0435\u043c\u044b\u0445 \u043f\u0440\u0438 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u0438 \u0438\u0441\u0445\u043e\u0434\u043d\u044b\u0445 \u0434\u0430\u043d\u043d\u044b\u0445..<br \/>  <a name=\"habracut\"><\/a><br \/>  \u041e\u0434\u043d\u0438\u043c \u0438\u0437 \u0433\u043b\u0430\u0432\u043d\u044b\u0445 \u0442\u0440\u0435\u0431\u043e\u0432\u0430\u043d\u0438\u0439, \u043a \u0440\u0430\u0437\u0440\u0430\u0431\u0430\u0442\u044b\u0432\u0430\u0435\u043c\u043e\u0439 \u0441\u0438\u0441\u0442\u0435\u043c\u0435, \u0431\u044b\u043b\u043e \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435 \u0431\u0435\u0441\u043f\u043b\u0430\u0442\u043d\u043e\u0433\u043e \u041f\u041e, \u0432 \u0441\u0432\u044f\u0437\u0438 \u0441 \u0447\u0435\u043c, \u0432\u044b\u0431\u043e\u0440 \u043f\u0430\u043b \u043d\u0430 PostgreSQL. \u041d\u0430 \u043c\u043e\u043c\u0435\u043d\u0442 \u043d\u0430\u0447\u0430\u043b\u0430 \u0440\u0430\u0431\u043e\u0442\u044b \u043d\u0430\u0434 \u043f\u0440\u043e\u0435\u043a\u0442\u043e\u043c, \u044f \u0434\u043e\u0432\u043e\u043b\u044c\u043d\u043e \u043f\u043e\u0432\u0435\u0440\u0445\u043d\u043e\u0441\u0442\u043d\u043e \u0437\u043d\u0430\u043b PostgreSQL, \u043d\u043e \u0431\u044b\u043b \u043d\u0435\u043f\u043b\u043e\u0445\u043e \u0437\u043d\u0430\u043a\u043e\u043c \u0441 \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u044f\u043c\u0438 Oracle Database. \u041f\u043e\u0441\u043a\u043e\u043b\u044c\u043a\u0443 \u0440\u0435\u0447\u044c \u0448\u043b\u0430 \u043e\u0431 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u043e\u0439 \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u043a\u0435, \u043c\u043d\u0435 \u0445\u043e\u0442\u0435\u043b\u043e\u0441\u044c \u0438\u043c\u0435\u0442\u044c \u0430\u043d\u0430\u043b\u043e\u0433\u0438 \u0442\u0430\u043a\u0438\u0445 \u043e\u043f\u0446\u0438\u0439 Oracle \u043a\u0430\u043a <a href=\"http:\/\/docs.oracle.com\/cd\/A97630_01\/server.920\/a96520\/parpart.htm#97896\">Partitioning<\/a> \u0438 <a href=\"http:\/\/docs.oracle.com\/cd\/A97630_01\/server.920\/a96520\/mv.htm#721\">Materialized Views<\/a>. \u041f\u043e\u0441\u043b\u0435 \u043e\u0437\u043d\u0430\u043a\u043e\u043c\u043b\u0435\u043d\u0438\u044f \u0441 \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u044f\u043c\u0438 <a href=\"http:\/\/www.postgresql.org\/docs\/9.1\/static\/ddl-partitioning.html\">PostgreSQL<\/a>, \u0441\u0442\u0430\u043b\u043e \u043f\u043e\u043d\u044f\u0442\u043d\u043e, \u0447\u0442\u043e \u044d\u0442\u043e\u0442 \u0444\u0443\u043d\u043a\u0446\u0438\u043e\u043d\u0430\u043b, \u0442\u0430\u043a \u0438\u043b\u0438 \u0438\u043d\u0430\u0447\u0435, \u043f\u0440\u0438\u0434\u0435\u0442\u0441\u044f \u043f\u0438\u0441\u0430\u0442\u044c \u0432\u0440\u0443\u0447\u043d\u0443\u044e.<\/p>\n<p>  \u0420\u0430\u0437\u0443\u043c\u0435\u0435\u0442\u0441\u044f, \u0440\u0435\u0447\u044c \u043d\u0435 \u0448\u043b\u0430 \u043e \u043a\u0430\u043a\u043e\u0439 \u043b\u0438\u0431\u043e \u043f\u043e\u043b\u043d\u043e\u0446\u0435\u043d\u043d\u043e\u0439 \u0440\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u0438 Materialized Views, \u043f\u0440\u0435\u0434\u0443\u0441\u043c\u0430\u0442\u0440\u0438\u0432\u0430\u044e\u0449\u0435\u0439 <a href=\"http:\/\/docs.oracle.com\/cd\/B28359_01\/server.111\/b28313\/qrbasic.htm\">\u043f\u0435\u0440\u0435\u043f\u0438\u0441\u044b\u0432\u0430\u043d\u0438\u0435 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432<\/a>. \u0414\u043b\u044f \u043c\u043e\u0438\u0445 \u043d\u0443\u0436\u0434 \u0432\u043f\u043e\u043b\u043d\u0435 \u0445\u0432\u0430\u0442\u0430\u043b\u043e \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u0438 \u0441\u043e\u0437\u0434\u0430\u043d\u0438\u044f \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u043e\u0431\u043d\u043e\u0432\u043b\u044f\u0435\u043c\u044b\u0445 \u0430\u0433\u0440\u0435\u0433\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0445 \u043e\u0434\u043d\u043e-\u0442\u0430\u0431\u043b\u0438\u0447\u043d\u044b\u0445 \u0432\u044b\u0431\u043e\u0440\u043e\u043a (\u043f\u043e\u0434\u0434\u0435\u0440\u0436\u043a\u0430 \u0441\u043e\u0435\u0434\u0438\u043d\u0435\u043d\u0438\u044f \u0442\u0430\u0431\u043b\u0438\u0446, \u0441\u043a\u043e\u0440\u0435\u0435 \u0432\u0441\u0435\u0433\u043e, \u0431\u0443\u0434\u0435\u0442 \u0434\u043e\u0431\u0430\u0432\u043b\u0435\u043d\u0430 \u0432 \u0431\u043b\u0438\u0436\u0430\u0439\u0448\u0435\u043c \u0431\u0443\u0434\u0443\u0449\u0435\u043c). \u0414\u043b\u044f \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f, \u044f \u043f\u043b\u0430\u043d\u0438\u0440\u043e\u0432\u0430\u043b \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c \u043c\u043d\u043e\u0433\u043e\u043a\u0440\u0430\u0442\u043d\u043e \u043e\u043f\u0438\u0441\u0430\u043d\u043d\u044b\u0439 \u043f\u043e\u0434\u0445\u043e\u0434 \u0441 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435\u043c <a href=\"http:\/\/www.postgresql.org\/docs\/9.0\/static\/ddl-inherit.html\">\u043d\u0430\u0441\u043b\u0435\u0434\u0443\u0435\u043c\u044b\u0445<\/a> \u0442\u0430\u0431\u043b\u0438\u0446, \u0441\u043e \u0432\u0441\u0442\u0430\u0432\u043a\u043e\u0439 \u0434\u0430\u043d\u043d\u044b\u0445, \u0443\u043f\u0440\u0430\u0432\u043b\u044f\u0435\u043c\u043e\u0439 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u043e\u043c. \u0423 \u043c\u0435\u043d\u044f \u0431\u044b\u043b\u0430 \u043c\u044b\u0441\u043b\u044c \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c \u0434\u043b\u044f \u0443\u043f\u0440\u0430\u0432\u043b\u0435\u043d\u0438\u044f \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435\u043c <a href=\"http:\/\/www.postgresql.org\/docs\/9.1\/static\/ddl-partitioning.html#DDL-PARTITIONING-ALTERNATIVES\">Rules<\/a>, \u043d\u043e \u044f \u043e\u0442 \u043d\u0435\u0435 \u043e\u0442\u043a\u0430\u0437\u0430\u043b\u0441\u044f, \u043f\u043e\u0441\u043a\u043e\u043b\u044c\u043a\u0443, \u0432 \u043c\u043e\u0435\u043c \u0441\u043b\u0443\u0447\u0430\u0435, \u043f\u0440\u0435\u043e\u0431\u043b\u0430\u0434\u0430\u043b\u0430 \u0432\u0441\u0442\u0430\u0432\u043a\u0430 \u0434\u0430\u043d\u043d\u044b\u0445 \u043e\u0434\u0438\u043d\u043e\u0447\u043d\u044b\u043c\u0438 \u0437\u0430\u043f\u0438\u0441\u044f\u043c\u0438.<\/p>\n<p>  \u041d\u0430\u0447\u0430\u043b \u044f, \u0440\u0430\u0437\u0443\u043c\u0435\u0435\u0442\u0441\u044f, \u0441 \u0442\u0430\u0431\u043b\u0438\u0446 \u0434\u043b\u044f \u0445\u0440\u0430\u043d\u0435\u043d\u0438\u044f \u043c\u0435\u0442\u0430\u0434\u0430\u043d\u043d\u044b\u0445:<\/p>\n<div class=\"spoiler\"><b class=\"spoiler_title\">ps_tables.sql<\/b><\/p>\n<div class=\"spoiler_text\">\n<pre><code class=\"sql\">create sequence ps_table_seq;  create table    ps_table (   id            bigint         default nextval('ps_table_seq') not null,   name          varchar(50)    not null unique,   primary key(id) );  create sequence ps_column_seq;  create table    ps_column (   id            bigint         default nextval('ps_column_seq') not null,   table_id      bigint         not null references ps_table(id),   name          varchar(50)    not null,   parent_name   varchar(50),   type_name     varchar(8)     not null check (type_name in ('date', 'key', 'nullable', 'sum', 'min', 'max', 'cnt')),   unique (table_id, name),   primary key(id) );  create table    ps_range_partition (   table_id      bigint         not null references ps_table(id),   type_name     varchar(10)    not null check (type_name in ('day', 'week', 'month', 'year')),   start_value   date           not null,   end_value     date           not null,   primary key(table_id, start_value) );  create table    ps_snapshot (   snapshot_id   bigint         not null references ps_table(id),   table_id      bigint         not null references ps_table(id),   type_name     varchar(10)    not null check (type_name in ('day', 'week', 'month', 'year')),   primary key(snapshot_id) ); <\/code><\/pre>\n<p>  <\/div>\n<\/div>\n<p>  \u0417\u0434\u0435\u0441\u044c \u0432\u0441\u0435 \u0434\u043e\u0441\u0442\u0430\u0442\u043e\u0447\u043d\u043e \u043e\u0447\u0435\u0432\u0438\u0434\u043d\u043e. \u0415\u0434\u0438\u043d\u0441\u0442\u0432\u0435\u043d\u043d\u043e\u0435, \u043e \u0447\u0435\u043c \u0441\u0442\u043e\u0438\u0442 \u0441\u043a\u0430\u0437\u0430\u0442\u044c, \u044d\u0442\u043e \u0442\u0438\u043f\u044b \u0441\u0442\u043e\u043b\u0431\u0446\u043e\u0432:  <\/p>\n<table border=\"1\">\n<tbody>\n<tr>\n<td><b>\u0422\u0438\u043f<\/b>  <\/td>\n<td><b>\u041e\u043f\u0438\u0441\u0430\u043d\u0438\u0435<\/b>  <\/td>\n<\/tr>\n<tr>\n<td><b>date<\/b>  <\/td>\n<td>\u0421\u0442\u043e\u043b\u0431\u0435\u0446, \u0441\u043e\u0434\u0435\u0440\u0436\u0430\u0449\u0438\u0439 \u043a\u0430\u043b\u0435\u043d\u0434\u0430\u0440\u043d\u0443\u044e \u0434\u0430\u0442\u0443, \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u043c\u044b\u0439 \u043f\u0440\u0438 \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0438 \u0438 \u0430\u0433\u0440\u0435\u0433\u0430\u0446\u0438\u0438 \u0434\u0430\u043d\u043d\u044b\u0445 (\u043f\u043e\u0434\u0434\u0435\u0440\u0436\u0438\u0432\u0430\u044e\u0442\u0441\u044f \u0442\u0438\u043f\u044b date \u0438 timestamp PostgreSQL)  <\/td>\n<\/tr>\n<tr>\n<td><b>key<\/b>  <\/td>\n<td>\u041a\u043b\u044e\u0447, \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u043c\u044b\u0439 \u0432 \u0444\u0440\u0430\u0437\u0435 group by, \u043f\u0440\u0438 \u0430\u0433\u0440\u0435\u0433\u0430\u0446\u0438\u0438 \u0434\u0430\u043d\u043d\u044b\u0445 (\u043f\u043e\u0434\u0434\u0435\u0440\u0436\u0438\u0432\u0430\u044e\u0442\u0441\u044f \u0432\u0441\u0435 \u0446\u0435\u043b\u043e\u0447\u0438\u0441\u043b\u0435\u043d\u043d\u044b\u0435 \u0442\u0438\u043f\u044b PostgreSQL)  <\/td>\n<\/tr>\n<tr>\n<td><b>nullable<\/b>  <\/td>\n<td>\u041a\u043b\u044e\u0447, \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u043c\u044b\u0439 \u043f\u0440\u0438 \u0430\u0433\u0440\u0435\u0433\u0430\u0446\u0438\u0438 \u0434\u0430\u043d\u043d\u044b\u0445, \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e \u0441\u043e\u0434\u0435\u0440\u0436\u0430\u0449\u0438\u0439 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 null  <\/td>\n<\/tr>\n<tr>\n<td><b>sum<\/b>  <\/td>\n<td>\u0421\u0443\u043c\u043c\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0439  <\/td>\n<\/tr>\n<tr>\n<td><b>min<\/b>  <\/td>\n<td>\u041c\u0438\u043d\u0438\u043c\u0430\u043b\u044c\u043d\u043e\u0435 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435  <\/td>\n<\/tr>\n<tr>\n<td><b>max<\/b>  <\/td>\n<td>\u041c\u0430\u043a\u0441\u0438\u043c\u0430\u043b\u044c\u043d\u043e\u0435 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435  <\/td>\n<\/tr>\n<tr>\n<td><b>cnt<\/b>  <\/td>\n<td>\u041f\u043e\u0434\u0441\u0447\u0435\u0442 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0430 \u043d\u0435 null-\u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0439  <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>  \u041e\u0441\u043d\u043e\u0432\u043e\u0439 \u0432\u0441\u0435\u0433\u043e \u0440\u0435\u0448\u0435\u043d\u0438\u044f \u0441\u0442\u0430\u043b\u0430 \u0444\u0443\u043d\u043a\u0446\u0438\u044f, \u0432\u044b\u043f\u043e\u043b\u043d\u044f\u044e\u0449\u0430\u044f \u043f\u0435\u0440\u0435\u0441\u0442\u0440\u043e\u0435\u043d\u0438\u0435 \u0444\u0443\u043d\u043a\u0446\u0438\u0439 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u043e\u0432 \u0434\u043b\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u044b, \u0441\u043e\u0434\u0435\u0440\u0436\u0430\u0449\u0435\u0439 \u0438\u0441\u0445\u043e\u0434\u043d\u044b\u0435 \u0434\u0430\u043d\u043d\u044b\u0435:<\/p>\n<div class=\"spoiler\"><b class=\"spoiler_title\">ps_trigger_regenerate(bigint)<\/b><\/p>\n<div class=\"spoiler_text\">\n<pre><code class=\"sql\">create or replace function ps_trigger_regenerate(in p_table bigint) returns void as $$ declare   l_sql         text;   l_table_name  varchar(50);   l_date_column varchar(50);   l_flag        boolean;   tabs          record;   columns       record; begin   select name into l_table_name   from   ps_table where id = p_table;    l_sql :=   'create or replace function ps_' || l_table_name || '_insert_trigger() returns trigger ' ||  'as $'|| '$ ' ||  'begin ';   for tabs in     select a.snapshot_id as id,            b.name as table_name,            a.type_name as snapshot_type     from   ps_snapshot a, ps_table b     where  a.table_id = p_table     and    b.id = a.snapshot_id     loop       l_flag = FALSE;       l_sql := l_sql ||      'update ' || tabs.table_name || ' set ';       for columns in         select name, parent_name, type_name         from   ps_column         where  table_id = tabs.id         and    not type_name in ('date', 'key', 'nullable')         loop           if l_flag then              l_sql := l_sql || ', ';           end if;           l_flag := TRUE;           if columns.type_name = 'sum' then              l_sql := l_sql ||              columns.name || ' = ' || columns.name || ' + coalesce(NEW.' || columns.parent_name || ', 0) ';           end if;           if columns.type_name = 'min' then              l_sql := l_sql ||              columns.name || ' = least(coalesce(' || columns.name || ', NEW.' || columns.parent_name || '), coalesce(NEW.' || columns.parent_name || ', ' || columns.name || ')) ';           end if;           if columns.type_name = 'max' then              l_sql := l_sql ||              columns.name || ' = greatest(coalesce(' || columns.name || ', NEW.' || columns.parent_name || '), coalesce(NEW.' || columns.parent_name || ', ' || columns.name || ')) ';           end if;           if columns.type_name = 'cnt' then              l_sql := l_sql ||              columns.name || ' = ' || columns.name || ' + case when NEW.' || columns.parent_name || ' is null then 0 else 1 end ';           end if;         end loop;       l_flag = FALSE;       l_sql := l_sql || 'where ';       for columns in         select name, parent_name, type_name         from   ps_column         where  table_id = tabs.id         and    type_name in ('date', 'key', 'nullable')         loop           if l_flag then              l_sql := l_sql || 'and ';           end if;           l_flag := TRUE;           if columns.type_name = 'date' then              l_sql := l_sql ||              columns.name || ' = date_trunc(lower(''' || tabs.snapshot_type || '''), NEW.' || columns.parent_name || ') ';           end if;           if columns.type_name = 'key' then              l_sql := l_sql ||              columns.name || ' = NEW.' || columns.parent_name || ' ';           end if;           if columns.type_name = 'nullable' then              l_sql := l_sql ||              columns.name || ' = coalesce(NEW.' || columns.parent_name || ', 0)';           end if;         end loop;       l_sql := l_sql || '; ' ||      'if not FOUND then ' ||      'insert into ' || tabs.table_name || '(';       l_flag = FALSE;       for columns in         select name, type_name         from   ps_column         where  table_id = tabs.id         loop           if l_flag then              l_sql := l_sql || ', ';           end if;           l_flag := TRUE;           l_sql := l_sql || columns.name;         end loop;       l_sql := l_sql || ') values (';       l_flag = FALSE;       for columns in         select name, parent_name, type_name         from   ps_column         where  table_id = tabs.id         loop           if l_flag then              l_sql := l_sql || ', ';           end if;           l_flag := TRUE;           if columns.type_name = 'date' then              l_sql := l_sql || 'date_trunc(lower(''' || tabs.snapshot_type || '''), NEW.' || columns.parent_name || ')';           elsif columns.type_name = 'cnt' then              l_sql := l_sql || 'case when NEW.' || columns.parent_name || ' is null then 0 else 1 end';           elsif columns.type_name in ('nullable', 'sum') then              l_sql := l_sql || 'coalesce(NEW.' || columns.parent_name || ', 0)';           else              l_sql := l_sql || 'NEW.' || columns.parent_name;           end if;         end loop;       l_sql := l_sql || '); ' ||      'end if; ';     end loop;     select name into l_date_column     from   ps_column     where  table_id = p_table     and    type_name = 'date';     for tabs in       select to_char(start_value, 'YYYYMMDD') as start_value,              to_char(end_value, 'YYYYMMDD') as end_value,              type_name       from   ps_range_partition       where  table_id = p_table       order  by start_value desc       loop         l_sql := l_sql ||        'if NEW.' || l_date_column || ' &gt;= to_date(''' || tabs.start_value || ''', ''YYYYMMDD'') and NEW.' || l_date_column || ' &lt; to_date(''' || tabs.end_value || ''', ''YYYYMMDD'') then ' ||           'insert into ' || l_table_name || '_' || tabs.start_value || ' values (NEW.*); ' ||           'return null; ' ||        'end if; ';       end loop;   l_sql := l_sql ||  'return NEW; '||  'end; '||  '$'||'$ language plpgsql';   execute l_sql;    l_sql :=   'create or replace function ps_' || l_table_name || '_raise_trigger() returns trigger ' ||  'as $'|| '$ ' ||  'begin ' ||    'raise EXCEPTION ''Can''''t support % on MIN or MAX aggregate'', TG_OP;' ||  'end; '||  '$'||'$ language plpgsql';   execute l_sql;    l_sql :=   'create or replace function ps_' || l_table_name || '_delete_trigger() returns trigger ' ||  'as $'|| '$ ' ||  'begin ';   for tabs in     select a.snapshot_id as id,            b.name as table_name,            a.type_name as snapshot_type     from   ps_snapshot a, ps_table b     where  a.table_id = p_table     and    b.id = a.snapshot_id     loop       l_flag = FALSE;       l_sql := l_sql ||      'update ' || tabs.table_name || ' set ';       for columns in         select name, parent_name, type_name         from   ps_column         where  table_id = tabs.id         and    type_name in ('sum', 'cnt')         loop           if l_flag then              l_sql := l_sql || ', ';           end if;           l_flag := TRUE;           if columns.type_name = 'sum' then              l_sql := l_sql ||              columns.name || ' = ' || columns.name || ' - OLD.' || columns.parent_name || ' ';           end if;           if columns.type_name = 'cnt' then              l_sql := l_sql ||              columns.name || ' = ' || columns.name || ' - case when OLD.' || columns.parent_name || ' is null then 0 else 1 end ';           end if;         end loop;       l_flag = FALSE;       l_sql := l_sql || 'where ';       for columns in         select name, parent_name, type_name         from   ps_column         where  table_id = tabs.id         and    type_name in ('date', 'key', 'nullable')         loop           if l_flag  then              l_sql := l_sql || 'and ';           end if;           l_flag := TRUE;           if columns.type_name = 'date' then              l_sql := l_sql ||              columns.name || ' = date_trunc(lower(''' || tabs.snapshot_type || '''), NEW.' || columns.parent_name || ') ';           end if;           if columns.type_name = 'key' then              l_sql := l_sql ||              columns.name || ' = NEW.' || columns.parent_name || ' ';           end if;           if columns.type_name = 'nullable' then              l_sql := l_sql ||              columns.name || ' = coalesce(NEW.' || columns.parent_name || ', 0)';           end if;         end loop;       l_sql := l_sql || '; ';     end loop;   l_sql := l_sql ||  'return null; '||  'end; '||  '$'||'$ language plpgsql';   execute l_sql;    l_sql :=   'create or replace function ps_' || l_table_name || '_update_trigger() returns trigger ' ||  'as $'|| '$ ' ||  'begin ';   for tabs in     select a.snapshot_id as id,            b.name as table_name,            a.type_name as snapshot_type     from   ps_snapshot a, ps_table b     where  a.table_id = p_table     and    b.id = a.snapshot_id     loop       l_flag = FALSE;       l_sql := l_sql ||      'update ' || tabs.table_name || ' set ';       for columns in         select name, parent_name, type_name         from   ps_column         where  table_id = tabs.id         and    type_name in ('sum', 'cnt')         loop           if l_flag then              l_sql := l_sql || ', ';           end if;           l_flag := TRUE;           if columns.type_name = 'sum' then              l_sql := l_sql ||              columns.name || ' = ' || columns.name || ' - OLD.' || columns.parent_name || ' + NEW.' || columns.parent_name || ' ';           end if;           if columns.type_name = 'cnt' then              l_sql := l_sql ||              columns.name || ' = ' || columns.name ||              ' - case when OLD.' || columns.parent_name || ' is null then 0 else 1 end ' ||              ' + case when NEW.' || columns.parent_name || ' is null then 0 else 1 end ';           end if;         end loop;       l_flag = FALSE;       l_sql := l_sql || 'where ';       for columns in         select name, parent_name, type_name         from   ps_column         where  table_id = tabs.id         and    type_name in ('date', 'key', 'nullable')         loop           if l_flag then              l_sql := l_sql || 'and ';           end if;           l_flag := TRUE;           if columns.type_name = 'date' then              l_sql := l_sql ||              columns.name || ' = date_trunc(lower(''' || tabs.snapshot_type || '''), NEW.' || columns.parent_name || ') ';           end if;           if columns.type_name = 'key' then              l_sql := l_sql ||              columns.name || ' = NEW.' || columns.parent_name || ' ';           end if;           if columns.type_name = 'nullable' then              l_sql := l_sql ||              columns.name || ' = coalesce(NEW.' || columns.parent_name || ', 0)';           end if;         end loop;       l_sql := l_sql || '; ';     end loop;   l_sql := l_sql ||  'return null; '||  'end; '||  '$'||'$ language plpgsql';   execute l_sql; end; $$ language plpgsql; <\/code><\/pre>\n<p>  <\/div>\n<\/div>\n<p>  \u041d\u0435\u0441\u043c\u043e\u0442\u0440\u044f \u043d\u0430 \u0441\u0432\u043e\u0439 \u0443\u0441\u0442\u0440\u0430\u0448\u0430\u044e\u0449\u0438\u0439 \u0432\u0438\u0434, \u044d\u0442\u0430 \u0444\u0443\u043d\u043a\u0446\u0438\u044f \u0434\u043e\u0432\u043e\u043b\u044c\u043d\u043e \u043f\u0440\u043e\u0441\u0442\u0430. \u0415\u0435 \u0437\u0430\u0434\u0430\u0447\u0430 \u2014 \u0441\u0444\u043e\u0440\u043c\u0438\u0440\u043e\u0432\u0430\u0442\u044c (\u043d\u0430 \u043e\u0441\u043d\u043e\u0432\u0435 \u0438\u043c\u0435\u044e\u0449\u0438\u0445\u0441\u044f \u043c\u0435\u0442\u0430\u0434\u0430\u043d\u043d\u044b\u0445), \u0447\u0435\u0442\u044b\u0440\u0435 \u0444\u0443\u043d\u043a\u0446\u0438\u0438, \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u043c\u044b\u0445 \u043f\u0440\u0438 \u043f\u043e\u0441\u0442\u0440\u043e\u0435\u043d\u0438\u0438 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u043e\u0432:<\/p>\n<ul>\n<li>ps_TABLE_insert_trigger() \u2014 \u0424\u0443\u043d\u043a\u0446\u0438\u044f \u0443\u043f\u0440\u0430\u0432\u043b\u044f\u044e\u0449\u0430\u044f \u0432\u0441\u0442\u0430\u0432\u043a\u043e\u0439 \u0434\u0430\u043d\u043d\u044b\u0445<\/li>\n<li>ps_TABLE_update_trigger() \u2014 \u0424\u0443\u043d\u043a\u0446\u0438\u044f \u0443\u043f\u0440\u0430\u0432\u043b\u044f\u044e\u0449\u0430\u044f \u043e\u0431\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u0435\u043c \u0434\u0430\u043d\u043d\u044b\u0445<\/li>\n<li>ps_TABLE_delete_trigger() \u2014 \u0424\u0443\u043d\u043a\u0446\u0438\u044f \u0443\u043f\u0440\u0430\u0432\u043b\u044f\u044e\u0449\u0430\u044f \u0443\u0434\u0430\u043b\u0435\u043d\u0438\u0435\u043c \u0434\u0430\u043d\u043d\u044b\u0445<\/li>\n<li>ps_TABLE_raise_trigger() \u2014 \u0424\u0443\u043d\u043a\u0446\u0438\u044f \u0437\u0430\u043f\u0440\u0435\u0449\u0430\u044e\u0449\u0430\u044f \u043e\u0431\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u0435 \u0438 \u0443\u0434\u0430\u043b\u0435\u043d\u0438\u0435 \u0434\u0430\u043d\u043d\u044b\u0445<\/li>\n<\/ul>\n<p>  \u0417\u0434\u0435\u0441\u044c, \u0432\u043c\u0435\u0441\u0442\u043e TABLE \u043f\u043e\u0434\u0441\u0442\u0430\u0432\u043b\u044f\u0435\u0442\u0441\u044f \u0438\u043c\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u044b, \u0441\u043e\u0434\u0435\u0440\u0436\u0430\u0449\u0435\u0439 \u0438\u0441\u0445\u043e\u0434\u043d\u044b\u0435 \u0434\u0430\u043d\u043d\u044b\u0435. \u0422\u0438\u043f\u0438\u0447\u043d\u043e\u0435 \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u0438\u0435 \u0444\u0443\u043d\u043a\u0446\u0438\u0438 ps_TABLE_insert_trigger() \u0431\u0443\u0434\u0435\u0442 \u0432\u044b\u0433\u043b\u044f\u0434\u0435\u0442\u044c \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u043c \u043e\u0431\u0440\u0430\u0437\u043e\u043c:<\/p>\n<pre><code class=\"sql\">create or replace function ps_data_insert_trigger() returns trigger as $$ begin   update data_month set     sum_field = sum_field + NEW.sum_field   , min_field = least(min_field, NEW.min_field)   where date_field = date_trunc('month', NEW.date_field)   and   key_field = NEW.key_field;   if not FOUND then      insert into data_month(date_field, key_field, sum_field, min_field)      values (date_trunc('month', NEW.date_field), NEW.key_field, NEW.sum_field, NEW.min_field);   end if;   if NEW.date_field &gt;= to_date('20130101', 'YYYYMMDD') and       NEW.date_field &lt; to_date('20130201', 'YYYYMMDD') then      insert into data_20130101 values (NEW.*);      return null;   end if;   return NEW; end; $$ language plpgsql; <\/code><\/pre>\n<p>  \u041d\u0430 \u0441\u0430\u043c\u043e\u043c \u0434\u0435\u043b\u0435, \u0444\u0443\u043d\u043a\u0446\u0438\u044f \u0432\u044b\u0433\u043b\u044f\u0434\u0438\u0442 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0441\u043b\u043e\u0436\u043d\u0435\u0435, \u043f\u043e\u0441\u043a\u043e\u043b\u044c\u043a\u0443 \u043e\u0441\u043e\u0431\u044b\u043c \u043e\u0431\u0440\u0430\u0437\u043e\u043c \u043e\u0431\u0440\u0430\u0431\u0430\u0442\u044b\u0432\u0430\u044e\u0442\u0441\u044f null-\u0437\u043d\u0430\u0447\u0435\u043d\u0438\u044f. \u041d\u043e, \u0432 \u043a\u0430\u0447\u0435\u0441\u0442\u0432\u0435 \u0438\u043b\u043b\u044e\u0441\u0442\u0440\u0430\u0446\u0438\u0438, \u043f\u0440\u0438\u0432\u0435\u0434\u0435\u043d\u043d\u044b\u0439 \u0432\u044b\u0448\u0435 \u043f\u0440\u0438\u043c\u0435\u0440 \u0432\u043f\u043e\u043b\u043d\u0435 \u0430\u0434\u0435\u043a\u0432\u0430\u0442\u0435\u043d. \u041b\u043e\u0433\u0438\u043a\u0430 \u044d\u0442\u043e\u0433\u043e \u043a\u043e\u0434\u0430 \u043e\u0447\u0435\u0432\u0438\u0434\u043d\u0430:<\/p>\n<ul>\n<li>\u041f\u0440\u0438 \u0432\u0441\u0442\u0430\u0432\u043a\u0435 \u0432 \u0438\u0441\u0445\u043e\u0434\u043d\u0443\u044e \u0442\u0430\u0431\u043b\u0438\u0446\u0443 data, \u043f\u044b\u0442\u0430\u0435\u043c\u0441\u044f \u043e\u0431\u043d\u043e\u0432\u0438\u0442\u044c \u0441\u0447\u0435\u0442\u0447\u0438\u043a\u0438 \u0432 \u0430\u0433\u0440\u0435\u0433\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u043e\u043c \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u0438 data_month<\/li>\n<li>\u0415\u0441\u043b\u0438 \u044d\u0442\u043e \u043d\u0435 \u0443\u0434\u0430\u043b\u043e\u0441\u044c (\u0437\u0430\u043f\u0438\u0441\u044c \u0432 data_month \u043d\u0435 \u043d\u0430\u0439\u0434\u0435\u043d\u0430), \u0434\u043e\u0431\u0430\u0432\u043b\u044f\u0435\u043c \u043d\u043e\u0432\u0443\u044e \u0437\u0430\u043f\u0438\u0441\u044c<\/li>\n<li>\u0414\u0430\u043b\u0435\u0435, \u043f\u0440\u043e\u0432\u0435\u0440\u044f\u0435\u043c \u043f\u043e\u043f\u0430\u0434\u0430\u043d\u0438\u0435 \u0432 \u0438\u043d\u0442\u0435\u0440\u0432\u0430\u043b \u0434\u0430\u0442 \u0434\u043b\u044f \u043a\u0430\u0436\u0434\u043e\u0439 \u0441\u0435\u043a\u0446\u0438\u0438 (\u0432 \u043f\u0440\u0438\u043c\u0435\u0440\u0435 \u043e\u0434\u043d\u0430 \u0441\u0435\u043a\u0446\u0438\u044f), \u0438 \u043f\u0440\u0438 \u0443\u0441\u043f\u0435\u0445\u0435, \u0432\u0441\u0442\u0430\u0432\u043b\u044f\u0435\u043c \u0437\u0430\u043f\u0438\u0441\u044c \u0432 \u0441\u043e\u043e\u0442\u0432\u0435\u0442\u0441\u0442\u0432\u0443\u044e\u0449\u0443\u044e \u0441\u0435\u043a\u0446\u0438\u044e (\u043f\u043e\u0441\u043a\u043e\u043b\u044c\u043a\u0443 \u0441\u0435\u043a\u0446\u0438\u044f \u043d\u0430\u0441\u043b\u0435\u0434\u0443\u0435\u0442\u0441\u044f \u043e\u0442 \u0433\u043b\u0430\u0432\u043d\u043e\u0439 \u0442\u0430\u0431\u043b\u0438\u0446\u044b, \u043c\u043e\u0436\u043d\u043e \u0441\u043c\u0435\u043b\u043e \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c \u0437\u0432\u0435\u0437\u0434\u043e\u0447\u043a\u0443) \u0438 \u0432\u043e\u0437\u0440\u0430\u0449\u0430\u0435\u043c null, \u0447\u0442\u043e\u0431\u044b \u043f\u0440\u0435\u0434\u043e\u0442\u0432\u0440\u0430\u0442\u0438\u0442\u044c \u0432\u0441\u0442\u0430\u0432\u043a\u0443 \u0437\u0430\u043f\u0438\u0441\u0438 \u0432 \u0433\u043b\u0430\u0432\u043d\u0443\u044e \u0442\u0430\u0431\u043b\u0438\u0446\u0443<\/li>\n<li>\u0415\u0441\u043b\u0438 \u043d\u0438 \u043e\u0434\u043d\u0430 \u0438\u0437 \u0441\u0435\u043a\u0446\u0438\u0439 \u043d\u0435 \u043f\u043e\u0434\u0445\u043e\u0434\u0438\u0442, \u0432\u043e\u0437\u0432\u0440\u0430\u0449\u0430\u0435\u043c NEW, \u043f\u043e\u0437\u0432\u043e\u043b\u044f\u044f \u0432\u044b\u043f\u043e\u043b\u043d\u0438\u0442\u044c \u0432\u0441\u0442\u0430\u0432\u043a\u0443 \u0432 \u0433\u043b\u0430\u0432\u043d\u0443\u044e \u0442\u0430\u0431\u043b\u0438\u0446\u0443<\/li>\n<\/ul>\n<p>  \u041f\u043e\u0441\u043b\u0435\u0434\u043d\u0438\u0439 \u043f\u0443\u043d\u043a\u0442 \u043f\u0440\u0438\u0432\u043e\u0434\u0438\u0442 \u043a \u0442\u043e\u043c\u0443, \u0447\u0442\u043e \u0435\u0441\u043b\u0438 \u043f\u043e\u0434\u0445\u043e\u0434\u044f\u0449\u0430\u044f \u0441\u0435\u043a\u0446\u0438\u044f \u043d\u0435 \u043d\u0430\u0439\u0434\u0435\u043d\u0430, \u0434\u0430\u043d\u043d\u044b\u0435 \u0434\u043e\u0431\u0430\u0432\u043b\u044f\u044e\u0442\u0441\u044f \u0432 \u0433\u043b\u0430\u0432\u043d\u0443\u044e \u0442\u0430\u0431\u043b\u0438\u0446\u0443. \u041d\u0430 \u043f\u0440\u0430\u043a\u0442\u0438\u043a\u0435 \u044d\u0442\u043e \u0434\u043e\u0432\u043e\u043b\u044c\u043d\u043e \u0443\u0434\u043e\u0431\u043d\u043e. \u0414\u0430\u0436\u0435 \u0435\u0441\u043b\u0438 \u043c\u044b \u043d\u0435 \u0441\u043e\u0437\u0434\u0430\u0434\u0438\u043c \u0441\u0435\u043a\u0446\u0438\u044e \u0437\u0430\u0440\u0430\u043d\u0435\u0435 \u0438\u043b\u0438 \u043f\u043e\u043b\u0443\u0447\u0438\u043c \u0434\u0430\u043d\u043d\u044b\u0435 \u0441 \u043d\u0435\u043a\u043e\u0440\u0440\u0435\u043a\u0442\u043d\u043e\u0439 \u0434\u0430\u0442\u043e\u0439, \u0432\u0441\u0442\u0430\u0432\u043a\u0430 \u0434\u0430\u043d\u043d\u044b\u0445 \u043f\u0440\u043e\u0439\u0434\u0435\u0442 \u0443\u0441\u043f\u0435\u0448\u043d\u043e. \u0412\u043f\u043e\u0441\u043b\u0435\u0434\u0441\u0442\u0432\u0438\u0438 \u043c\u043e\u0436\u043d\u043e \u043f\u0440\u043e\u0430\u043d\u0430\u043b\u0438\u0437\u0438\u0440\u043e\u0432\u0430\u0442\u044c \u0441\u043e\u0434\u0435\u0440\u0436\u0438\u043c\u043e\u0435 \u0433\u043b\u0430\u0432\u043d\u043e\u0439 \u0442\u0430\u0431\u043b\u0438\u0446\u044b, \u0432\u044b\u043f\u043e\u043b\u043d\u0438\u0432 \u0437\u0430\u043f\u0440\u043e\u0441:<\/p>\n<pre><code class=\"sql\">select * from only data <\/code><\/pre>\n<p>  \u041f\u043e\u0441\u043b\u0435 \u0447\u0435\u0433\u043e, \u0441\u043e\u0437\u0434\u0430\u0442\u044c \u043d\u0435\u0434\u043e\u0441\u0442\u0430\u044e\u0449\u0438\u0435 \u0441\u0435\u043a\u0446\u0438\u0438 (\u043a\u0430\u043a \u0431\u0443\u0434\u0435\u0442 \u043f\u043e\u043a\u0430\u0437\u0430\u043d\u043e \u043d\u0438\u0436\u0435, \u0434\u0430\u043d\u043d\u044b\u0435 \u0431\u0443\u0434\u0443\u0442 \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u043f\u0435\u0440\u0435\u043d\u0435\u0441\u0435\u043d\u044b \u0438\u0437 \u0433\u043b\u0430\u0432\u043d\u043e\u0439 \u0442\u0430\u0431\u043b\u0438\u0446\u044b \u0432 \u0441\u043e\u0437\u0434\u0430\u043d\u043d\u0443\u044e \u0441\u0435\u043a\u0446\u0438\u044e). \u0412 \u043f\u043e\u0434\u043e\u0431\u043d\u044b\u0445 \u0441\u043b\u0443\u0447\u0430\u044f\u0445, \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0437\u0430\u043f\u0438\u0441\u0435\u0439, \u043d\u0435 \u043f\u043e\u043f\u0430\u0432\u0448\u0438\u0445 \u0432 \u0441\u0432\u043e\u044e \u0441\u0435\u043a\u0446\u0438\u044e, \u043a\u0430\u043a \u043f\u0440\u0430\u0432\u0438\u043b\u043e, \u043d\u0435 \u0432\u0435\u043b\u0438\u043a\u043e \u0438 \u0438\u0437\u0434\u0435\u0440\u0436\u043a\u0438, \u043d\u0430 \u043f\u0435\u0440\u0435\u043d\u043e\u0441 \u0434\u0430\u043d\u043d\u044b\u0445, \u043d\u0435\u0437\u043d\u0430\u0447\u0438\u0442\u0435\u043b\u044c\u043d\u044b.<\/p>\n<p>  \u0422\u0435\u043f\u0435\u0440\u044c \u043e\u0441\u0442\u0430\u043b\u043e\u0441\u044c \u0441\u0434\u0435\u043b\u0430\u0442\u044c \u043e\u0431\u0432\u044f\u0437\u043a\u0443. \u041d\u0430\u0447\u043d\u0435\u043c \u0441 \u0444\u0443\u043d\u043a\u0446\u0438\u0438 \u0441\u043e\u0437\u0434\u0430\u043d\u0438\u044f \u043d\u043e\u0432\u043e\u0439 \u0441\u0435\u043a\u0446\u0438\u0438:<\/p>\n<div class=\"spoiler\"><b class=\"spoiler_title\">ps_add_range_partition(varchar, varchar, varchar, date)<\/b><\/p>\n<div class=\"spoiler_text\">\n<pre><code class=\"sql\">create or replace function ps_add_range_partition(in p_table varchar, in p_column varchar,                  in p_type varchar, in p_start date) returns void as $$ declare   l_sql       text;   l_end       date;   l_start_str varchar(10);   l_end_str   varchar(10);   l_table     bigint;   l_flag      boolean;   columns     record; begin   perform 1   from   ps_table a, ps_column b   where  a.id = b.table_id and lower(a.name) = lower(p_table)   and    b.type_name = 'date' and lower(b.name) &lt;&gt; lower(p_column);   if FOUND then      raise EXCEPTION 'Conflict DATE columns';   end if;    l_end := p_start + ('1 ' || p_type)::INTERVAL;    perform 1   from   ps_table a, ps_range_partition b   where  a.id = b.table_id and lower(a.name) = lower(p_table)   and (( p_start &gt;= b.start_value and p_start &lt; b.end_value ) or        ( b.start_value &gt;= p_start and b.start_value &lt; l_end ));   if FOUND then      raise EXCEPTION 'Range intervals intersects';   end if;    perform 1   from   ps_table   where  lower(name) = lower(p_table);   if not FOUND then      insert into ps_table(name) values (lower(p_table));   end if;    select id into l_table   from   ps_table   where  lower(name) = lower(p_table);    perform 1   from   ps_column   where  table_id = l_table and type_name = 'date'   and    lower(name) = lower(p_column);   if not FOUND then      insert into ps_column(table_id, name, type_name)      values (l_table, lower(p_column), 'date');   end if;    insert into ps_range_partition(table_id, type_name, start_value, end_value)   values (l_table, p_type, p_start, l_end);    l_start_str = to_char(p_start, 'YYYYMMDD');   l_end_str = to_char(l_end, 'YYYYMMDD');    l_sql :=  'create table ' || p_table || '_' || l_start_str || '(' ||    'check (' || p_column || ' &gt;= to_date(''' || l_start_str || ''', ''YYYYMMDD'') and ' ||                 p_column || ' &lt; to_date(''' || l_end_str || ''', ''YYYYMMDD'')), ' ||    'primary key (';      l_flag := FALSE;     for columns in       select f.name as name       from ( select ps_array_to_set(a.conkey) as nn              from   pg_constraint a, pg_class b              where  b.oid = a.conrelid              and    a.contype = 'p'              and    b.relname = p_table ) c,             ( select d.attname as name, d.attnum as nn              from   pg_attribute d, pg_class e              where  e.oid = d.attrelid              and    e.relname = p_table ) f       where  f.nn = c.nn       order  by f.nn       loop         if l_flag then            l_sql := l_sql || ', ';         end if;         l_flag := TRUE;         l_sql := l_sql || columns.name;       end loop;    l_sql := l_sql ||  ')) inherits (' || p_table || ')';   execute l_sql;    l_sql :=   'create index ' || p_table || '_' || l_start_str || '_date on ' || p_table || '_' || l_start_str || '(' || p_column || ')';   execute l_sql;    perform ps_trigger_regenerate(l_table);    execute 'drop trigger if exists ps_' || p_table || '_before_insert on ' || p_table;   execute 'drop trigger if exists ps_' || p_table || '_after_update on '  || p_table;   execute 'drop trigger if exists ps_' || p_table || '_after_delete on '  || p_table;    l_sql :=   'insert into ' || p_table || '_' || l_start_str || ' ' ||  'select * from ' || p_table || ' where ' ||   p_column || ' &gt;= to_date(''' || l_start_str || ''', ''YYYYMMDD'') and ' ||   p_column || ' &lt; to_date(''' || l_end_str || ''', ''YYYYMMDD'')';   execute l_sql;    l_sql :=   'delete from only ' || p_table || ' where ' ||   p_column || ' &gt;= to_date(''' || l_start_str || ''', ''YYYYMMDD'') and ' ||   p_column || ' &lt; to_date(''' || l_end_str || ''', ''YYYYMMDD'')';   execute l_sql;    l_sql :=   'create trigger ps_' || p_table || '_before_insert ' ||  'before insert on ' || p_table || ' for each row ' ||  'execute procedure ps_' || p_table || '_insert_trigger()';   execute l_sql;   perform 1   from   ps_snapshot a, ps_column b   where  b.table_id = a.snapshot_id and a.table_id = l_table   and    b.type_name in ('min', 'max');   if FOUND then      l_sql :=      'create trigger ps_' || p_table || '_after_update ' ||     'after update on ' || p_table || ' for each row ' ||     'execute procedure ps_' || p_table || '_raise_trigger()';      execute l_sql;      l_sql :=      'create trigger ps_' || p_table || '_after_delete ' ||     'after delete on ' || p_table || ' for each row ' ||     'execute procedure ps_' || p_table || '_raise_trigger()';      execute l_sql;      l_sql :=      'create trigger ps_' || p_table || '_' || l_start_str || '_after_update ' ||     'after update on ' || p_table || '_' || l_start_str || ' for each row ' ||     'execute procedure ps_' || p_table || '_raise_trigger()';      execute l_sql;      l_sql :=      'create trigger ps_' || p_table || '_' || l_start_str || '_after_delete ' ||     'after delete on ' || p_table || '_' || l_start_str || ' for each row ' ||     'execute procedure ps_' || p_table || '_raise_trigger()';      execute l_sql;   else      l_sql :=      'create trigger ps_' || p_table || '_after_update ' ||     'after update on ' || p_table || ' for each row ' ||     'execute procedure ps_' || p_table || '_update_trigger()';      execute l_sql;      l_sql :=      'create trigger ps_' || p_table || '_after_delete ' ||     'after delete on ' || p_table || ' for each row ' ||     'execute procedure ps_' || p_table || '_delete_trigger()';      execute l_sql;      l_sql :=      'create trigger ps_' || p_table || '_' || l_start_str || '_after_update ' ||     'after update on ' || p_table || '_' || l_start_str || ' for each row ' ||     'execute procedure ps_' || p_table || '_update_trigger()';      execute l_sql;      l_sql :=      'create trigger ps_' || p_table || '_' || l_start_str || '_after_delete ' ||     'after delete on ' || p_table || '_' || l_start_str || ' for each row ' ||     'execute procedure ps_' || p_table || '_delete_trigger()';      execute l_sql;   end if; end; $$ language plpgsql; <\/code><\/pre>\n<p>  <\/div>\n<\/div>\n<p>  \u0417\u0434\u0435\u0441\u044c, \u043f\u043e\u0441\u043b\u0435 \u043f\u0440\u043e\u0432\u0435\u0440\u043a\u0438 \u043a\u043e\u0440\u0440\u0435\u043a\u0442\u043d\u043e\u0441\u0442\u0438 \u0432\u0445\u043e\u0434\u043d\u044b\u0445 \u0434\u0430\u043d\u043d\u044b\u0445, \u043c\u044b \u0434\u043e\u0431\u0430\u0432\u043b\u044f\u0435\u043c \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u044b\u0435 \u043c\u0435\u0442\u0430\u0434\u0430\u043d\u043d\u044b\u0435, \u043f\u043e\u0441\u043b\u0435 \u0447\u0435\u0433\u043e, \u0441\u043e\u0437\u0434\u0430\u0435\u043c \u0443\u043d\u0430\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u043d\u043d\u0443\u044e \u0442\u0430\u0431\u043b\u0438\u0446\u0443. \u0417\u0430\u0442\u0435\u043c, \u043c\u044b \u043f\u0435\u0440\u0435\u0441\u043e\u0437\u0434\u0430\u0435\u043c \u0444\u0443\u043d\u043a\u0446\u0438\u0438 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u043e\u0432 \u0432\u044b\u0437\u043e\u0432\u043e\u043c ps_trigger_regenerate, \u043f\u043e\u0441\u043b\u0435 \u0447\u0435\u0433\u043e \u043f\u0435\u0440\u0435\u043d\u043e\u0441\u0438\u043c \u0434\u0430\u043d\u043d\u044b\u0435, \u043f\u043e\u0434\u043f\u0430\u0434\u0430\u044e\u0449\u0438\u0435 \u043f\u043e\u0434 \u0443\u0441\u043b\u043e\u0432\u0438\u0435 \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u0432 \u0441\u043e\u0437\u0434\u0430\u043d\u043d\u0443\u044e \u0441\u0435\u043a\u0446\u0438\u044e \u0434\u0438\u043d\u0430\u043c\u0438\u0447\u0435\u0441\u043a\u0438\u043c \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u043c \u0438 \u043f\u0435\u0440\u0435\u0441\u043e\u0437\u0434\u0430\u0435\u043c \u0441\u0430\u043c\u0438 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u044b.<\/p>\n<p>  \u0421\u043b\u043e\u0436\u043d\u043e\u0441\u0442\u0438 \u0432\u043e\u0437\u043d\u0438\u043a\u043b\u0438 \u0441 \u0434\u0432\u0443\u043c\u044f \u043c\u043e\u043c\u0435\u043d\u0442\u0430\u043c\u0438.<\/p>\n<ol>\n<li>\u041f\u0440\u0438\u0448\u043b\u043e\u0441\u044c \u043d\u0435\u043c\u043d\u043e\u0433\u043e \u043f\u043e\u043c\u0443\u0447\u0438\u0442\u044c\u0441\u044f \u0441 \u043f\u0440\u0438\u0431\u0430\u0432\u043b\u0435\u043d\u0438\u0435\u043c \u043a \u0441\u0442\u0430\u0440\u0442\u043e\u0432\u043e\u0439 \u0434\u0430\u0442\u0435 \u043c\u0435\u0441\u044f\u0446\u0430, \u0434\u043d\u044f \u0438\u043b\u0438 \u0433\u043e\u0434\u0430 (\u0432 \u0437\u0430\u0432\u0438\u0441\u0438\u043c\u043e\u0441\u0442\u0438 \u043e\u0442 \u0432\u0445\u043e\u0434\u043d\u043e\u0433\u043e \u043f\u0430\u0440\u0430\u043c\u0435\u0442\u0440\u0430 p_type:\n<pre><code>l_end := p_start + ('1 ' || p_type)::INTERVAL; <\/code><\/pre>\n<p>  <\/li>\n<li>\u041f\u043e\u0441\u043a\u043e\u043b\u044c\u043a\u0443 \u043f\u0435\u0440\u0432\u0438\u0447\u043d\u044b\u0439 \u043a\u043b\u044e\u0447 \u043d\u0435 \u043d\u0430\u0441\u043b\u0435\u0434\u0443\u0435\u0442\u0441\u044f, \u043f\u0440\u0438\u0448\u043b\u043e\u0441\u044c \u0441\u043e\u0447\u0438\u043d\u044f\u0442\u044c \u0437\u0430\u043f\u0440\u043e\u0441 \u043a <a href=\"http:\/\/www.postgresql.org\/docs\/9.2\/static\/catalogs.html\">System Catalogs<\/a>, \u0434\u043b\u044f \u043f\u043e\u043b\u0443\u0447\u0435\u043d\u0438\u044f \u0441\u043f\u0438\u0441\u043a\u0430 \u043a\u043e\u043b\u043e\u043d\u043e\u043a \u043f\u0435\u0440\u0432\u0438\u0447\u043d\u043e\u0433\u043e \u043a\u043b\u044e\u0447\u0430 \u0438\u0441\u0445\u043e\u0434\u043d\u043e\u0439 \u0442\u0430\u0431\u043b\u0438\u0446\u044b (\u0445\u0440\u0430\u043d\u0438\u0442\u044c \u0432 \u0441\u0432\u043e\u0438\u0445 \u043c\u0435\u0442\u0430\u0434\u0430\u043d\u043d\u044b\u0445 \u0435\u0449\u0435 \u0438 \u043e\u043f\u0438\u0441\u0430\u043d\u0438\u0435 \u043f\u0435\u0440\u0432\u0438\u0447\u043d\u043e\u0433\u043e \u043a\u043b\u044e\u0447\u0430 \u044f \u0441\u0447\u0435\u043b \u043d\u0435\u0446\u0435\u043b\u0435\u0441\u043e\u043e\u0431\u0440\u0430\u0437\u043d\u044b\u043c):\n<pre><code class=\"sql\">      select f.name as name       from ( select ps_array_to_set(a.conkey) as nn              from   pg_constraint a, pg_class b              where  b.oid = a.conrelid              and    a.contype = 'p'              and    b.relname = p_table ) c,             ( select d.attname as name, d.attnum as nn              from   pg_attribute d, pg_class e              where  e.oid = d.attrelid              and    e.relname = p_table ) f       where  f.nn = c.nn       order  by f.nn <\/code><\/pre>\n<p>  <\/li>\n<\/ol>\n<p>  \u0422\u0430\u043a\u0436\u0435, \u0441\u043b\u0435\u0434\u0443\u0435\u0442 \u043e\u0442\u043c\u0435\u0442\u0438\u0442\u044c, \u0447\u0442\u043e \u043f\u0435\u0440\u0435\u0434 \u0441\u043e\u0437\u0434\u0430\u043d\u0438\u0435\u043c \u0438\u043d\u0434\u0435\u043a\u0441\u0430, \u043d\u0430 \u043a\u043b\u044e\u0447 \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f (\u0434\u043b\u044f \u0441\u043e\u0437\u0434\u0430\u043d\u043d\u043e\u0439 \u0441\u0435\u043a\u0446\u0438\u0438), \u0441\u0442\u043e\u0438\u043b\u043e \u0431\u044b \u043f\u0440\u0435\u0434\u0432\u0430\u0440\u0438\u0442\u0435\u043b\u044c\u043d\u043e \u043f\u0440\u043e\u0432\u0435\u0440\u0438\u0442\u044c, \u043d\u0435 \u044f\u0432\u043b\u044f\u0435\u0442\u0441\u044f \u043b\u0438 \u043e\u043d \u043b\u0438\u0434\u0438\u0440\u0443\u044e\u0449\u0438\u043c \u0441\u0442\u043e\u043b\u0431\u0446\u043e\u043c \u043f\u0435\u0440\u0432\u0438\u0447\u043d\u043e\u0433\u043e \u043a\u043b\u044e\u0447\u0430 (\u0447\u0442\u043e\u0431\u044b \u043d\u0435 \u0441\u043e\u0437\u0434\u0430\u0432\u0430\u0442\u044c \u0434\u0443\u0431\u043b\u0438\u0440\u0443\u044e\u0449\u0438\u0439 \u0438\u043d\u0434\u0435\u043a\u0441).<\/p>\n<p>  \u0424\u0443\u043d\u043a\u0446\u0438\u044f \u0443\u0434\u0430\u043b\u0435\u043d\u0438\u044f \u0441\u0435\u043a\u0446\u0438\u0438 \u0441\u0443\u0449\u0435\u0441\u0442\u0432\u0435\u043d\u043d\u043e \u043f\u0440\u043e\u0449\u0435 \u0438 \u0432 \u043e\u0441\u043e\u0431\u044b\u0445 \u043a\u043e\u043c\u043c\u0435\u043d\u0442\u0430\u0440\u0438\u044f\u0445 \u043d\u0435 \u043d\u0443\u0436\u0434\u0430\u0435\u0442\u0441\u044f:<\/p>\n<div class=\"spoiler\"><b class=\"spoiler_title\">ps_del_range_partition(varchar, date)<\/b><\/p>\n<div class=\"spoiler_text\">\n<pre><code class=\"sql\">create or replace function ps_del_range_partition(in p_table varchar, in p_start date)        returns void as $$ declare   l_sql       text;   l_start_str varchar(10);   l_table     bigint; begin   select id into l_table   from   ps_table   where  lower(name) = lower(p_table);    l_start_str = to_char(p_start, 'YYYYMMDD');    delete from ps_range_partition    where  table_id = l_table   and    start_value = p_start;    perform ps_trigger_regenerate(l_table);    l_sql :=   'insert into ' || p_table || ' ' ||  'select * from ' || p_table || '_' || l_start_str;   execute l_sql;    perform 1   from ( select 1          from   ps_range_partition          where  table_id = l_table          union  all          select 1          from   ps_snapshot          where  table_id = l_table ) a;   if not FOUND then      execute 'drop trigger if exists ps_' || p_table || '_before_insert on ' || p_table;      execute 'drop trigger if exists ps_' || p_table || '_after_update on '  || p_table;      execute 'drop trigger if exists ps_' || p_table || '_after_delete on '  || p_table;       execute 'drop function ps_' || p_table || '_insert_trigger() cascade';      execute 'drop function ps_' || p_table || '_raise_trigger()  cascade';      execute 'drop function ps_' || p_table || '_update_trigger() cascade';      execute 'drop function ps_' || p_table || '_delete_trigger() cascade';       delete from ps_column where table_id = l_table;      delete from ps_table where id = l_table;   end if;    perform 1   from   ps_range_partition   where  table_id = l_table;   if not FOUND then      delete from ps_column       where  table_id = l_table      and    type_name = 'date';   end if;    execute 'drop table ' || p_table || '_' || l_start_str; end; $$ language plpgsql; <\/code><\/pre>\n<p>  <\/div>\n<\/div>\n<p>  \u041f\u0440\u0438 \u0443\u0434\u0430\u043b\u0435\u043d\u0438\u0438 \u0441\u0435\u043a\u0446\u0438\u0438, \u0434\u0430\u043d\u043d\u044b\u0435, \u0435\u0441\u0442\u0435\u0441\u0442\u0432\u0435\u043d\u043d\u043e, \u043d\u0435 \u0442\u0435\u0440\u044f\u044e\u0442\u0441\u044f, \u0430 \u043f\u0435\u0440\u0435\u043d\u043e\u0441\u044f\u0442\u0441\u044f \u0432 \u0433\u043b\u0430\u0432\u043d\u0443\u044e \u0442\u0430\u0431\u043b\u0438\u0446\u0443 (\u043f\u0440\u0435\u0434\u0432\u0430\u0440\u0438\u0442\u0435\u043b\u044c\u043d\u043e \u0443\u0434\u0430\u043b\u044f\u044e\u0442\u0441\u044f \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u044b, \u043f\u043e\u0441\u043a\u043e\u043b\u044c\u043a\u0443, \u043a\u0430\u043a \u0432\u044b\u044f\u0441\u043d\u0438\u043b\u043e\u0441\u044c, \u043a\u043b\u044e\u0447\u0435\u0432\u043e\u0435 \u0441\u043b\u043e\u0432\u043e only \u043d\u0435 \u0440\u0430\u0431\u043e\u0442\u0430\u0435\u0442 \u0432 \u043e\u043f\u0435\u0440\u0430\u0442\u043e\u0440\u0435 insert).<\/p>\n<p>  \u041e\u0441\u0442\u0430\u043b\u043e\u0441\u044c \u0434\u043e\u0431\u0430\u0432\u0438\u0442\u044c \u0444\u0443\u043d\u043a\u0446\u0438\u0438 \u0443\u043f\u0440\u0430\u0432\u043b\u0435\u043d\u0438\u044f \u00ab\u0436\u0438\u0432\u044b\u043c\u0438\u00bb \u0441\u043d\u0438\u043c\u043a\u0430\u043c\u0438 \u0434\u0430\u043d\u043d\u044b\u0445:<\/p>\n<div class=\"spoiler\"><b class=\"spoiler_title\">ps_add_snapshot_column(varchar, varchar, varchar, varchar)<\/b><\/p>\n<div class=\"spoiler_text\">\n<pre><code class=\"sql\">create or replace function ps_add_snapshot_column(in p_snapshot varchar,       in p_column varchar, in p_parent varchar, in p_type varchar) returns void as $$ declare   l_table bigint; begin   perform 1   from   ps_table   where  lower(name) = lower(p_snapshot);   if not FOUND then      insert into ps_table(name) values (lower(p_snapshot));   end if;    select id into l_table   from   ps_table   where  lower(name) = lower(p_snapshot);    insert into ps_column(table_id, name, parent_name, type_name)   values (l_table, lower(p_column), lower(p_parent), p_type); end; $$ language plpgsql; <\/code><\/pre>\n<p>  <\/div>\n<\/div>\n<p>  <\/p>\n<div class=\"spoiler\"><b class=\"spoiler_title\">ps_add_snapshot(varchar, varchar, varchar)<\/b><\/p>\n<div class=\"spoiler_text\">\n<pre><code class=\"sql\">create or replace function ps_add_snapshot(in p_table varchar, in p_snapshot varchar,       in p_type varchar) returns void as $$ declare   l_sql      text;   l_table    bigint;   l_snapshot bigint;   l_flag     boolean;   columns    record; begin   select id into l_snapshot   from   ps_table   where  lower(name) = lower(p_snapshot);    perform 1   from   ps_column   where  table_id = l_snapshot   and    type_name in ('date', 'key');   if not FOUND then      raise EXCEPTION 'Key columns not found';   end if;    perform 1   from   ps_column   where  table_id = l_snapshot   and    not type_name in ('date', 'key', 'nullable');   if not FOUND then      raise EXCEPTION 'Aggregate columns not found';   end if;    perform 1   from   ps_table   where  lower(name) = lower(p_table);   if not FOUND then      insert into ps_table(name) values (lower(p_table));   end if;    select id into l_table   from   ps_table   where  lower(name) = lower(p_table);    insert into ps_snapshot(table_id, snapshot_id, type_name)   values (l_table, l_snapshot, p_type);    perform ps_trigger_regenerate(l_table);    l_sql := 'create table ' || p_snapshot || ' (';   l_flag := FALSE;   for columns in     select name, type_name     from   ps_column     where  table_id = l_snapshot     loop       if l_flag then          l_sql := l_sql || ', ';       end if;       l_flag := TRUE;       if columns.type_name = 'date' then          l_sql := l_sql || columns.name || ' date not null';       else          l_sql := l_sql || columns.name || ' bigint not null';       end if;     end loop;   l_sql := l_sql || ', primary key (';   l_flag := FALSE;   for columns in     select name     from   ps_column     where  table_id = l_snapshot     and    type_name in ('date', 'key', 'nullable')     loop       if l_flag then          l_sql := l_sql || ', ';       end if;       l_flag := TRUE;       l_sql := l_sql || columns.name;     end loop;   l_sql := l_sql || '))';   execute l_sql;    execute 'drop trigger if exists ps_' || p_table || '_before_insert on ' || p_table;   execute 'drop trigger if exists ps_' || p_table || '_after_update on '  || p_table;   execute 'drop trigger if exists ps_' || p_table || '_after_delete on '  || p_table;    l_sql :=   'create trigger ps_' || p_table || '_before_insert ' ||  'before insert on ' || p_table || ' for each row ' ||  'execute procedure ps_' || p_table || '_insert_trigger()';   execute l_sql;    perform 1   from   ps_snapshot a, ps_column b   where  b.table_id = a.snapshot_id and a.table_id = l_table   and    b.type_name in ('min', 'max');   if FOUND then      l_sql :=      'create trigger ps_' || p_table || '_after_update ' ||     'after update on ' || p_table || ' for each row ' ||     'execute procedure ps_' || p_table || '_raise_trigger()';      execute l_sql;      l_sql :=      'create trigger ps_' || p_table || '_after_delete ' ||     'after delete on ' || p_table || ' for each row ' ||     'execute procedure ps_' || p_table || '_raise_trigger()';      execute l_sql;   else      l_sql :=      'create trigger ps_' || p_table || '_after_update ' ||     'after update on ' || p_table || ' for each row ' ||     'execute procedure ps_' || p_table || '_update_trigger()';      execute l_sql;      l_sql :=      'create trigger ps_' || p_table || '_after_delete ' ||     'after delete on ' || p_table || ' for each row ' ||     'execute procedure ps_' || p_table || '_delete_trigger()';      execute l_sql;   end if;    l_sql := 'insert into ' || p_snapshot || '(';   l_flag := FALSE;   for columns in     select name     from   ps_column     where  table_id = l_snapshot     loop       if l_flag then          l_sql := l_sql || ', ';       end if;       l_flag := TRUE;       l_sql := l_sql || columns.name;     end loop;   l_sql := l_sql || ') select ';   l_flag := FALSE;   for columns in     select parent_name as name, type_name     from   ps_column     where  table_id = l_snapshot     loop       if l_flag then          l_sql := l_sql || ', ';       end if;       l_flag := TRUE;       if columns.type_name = 'date' then          l_sql := l_sql || 'date_trunc(lower(''' || p_type || '''), ' || columns.name || ')';       end if;       if columns.type_name = 'key' then          l_sql := l_sql || columns.name;       end if;       if columns.type_name = 'nullable' then          l_sql := l_sql || 'coalesce(' || columns.name || ', 0)';       end if;       if columns.type_name = 'sum' then          l_sql := l_sql || 'sum(' || columns.name || ')';       end if;       if columns.type_name = 'min' then          l_sql := l_sql || 'min(' || columns.name || ')';       end if;       if columns.type_name = 'max' then          l_sql := l_sql || 'max(' || columns.name || ')';       end if;       if columns.type_name = 'cnt' then          l_sql := l_sql || 'count(' || columns.name || ')';       end if;     end loop;   l_sql := l_sql || 'from ' || p_table || ' group by ';   l_flag := FALSE;   for columns in     select parent_name as name, type_name     from   ps_column     where  table_id = l_snapshot     and    type_name in ('date', 'key', 'nullable')     loop       if l_flag then          l_sql := l_sql || ', ';       end if;       l_flag := TRUE;       if columns.type_name = 'date' then          l_sql := l_sql || 'date_trunc(lower(''' || p_type || '''), ' || columns.name || ')';       else          l_sql := l_sql || columns.name;       end if;     end loop;   execute l_sql; end; $$ language plpgsql; <\/code><\/pre>\n<p>  <\/div>\n<\/div>\n<p>  <\/p>\n<div class=\"spoiler\"><b class=\"spoiler_title\">ps_del_snapshot(varchar)<\/b><\/p>\n<div class=\"spoiler_text\">\n<pre><code class=\"sql\">create or replace function ps_del_snapshot(in p_snapshot varchar) returns void as $$ declare   l_sql      text;   p_table    varchar(50);   l_table    bigint;   l_snapshot bigint; begin   select a.table_id, c.name into l_table, p_table   from   ps_snapshot a, ps_table b, ps_table c   where  b.id = a.snapshot_id and c.id = a.table_id   and    lower(b.name) = lower(p_snapshot);    select id into l_snapshot   from   ps_table   where  lower(name) = lower(p_snapshot);    delete from ps_snapshot where snapshot_id = l_snapshot;   delete from ps_column where table_id = l_snapshot;   delete from ps_table where id = l_snapshot;    execute 'drop trigger if exists ps_' || p_table || '_before_insert on ' || p_table;   execute 'drop trigger if exists ps_' || p_table || '_after_update  on ' || p_table;   execute 'drop trigger if exists ps_' || p_table || '_after_delete  on ' || p_table;      perform 1   from ( select 1          from   ps_range_partition          where  table_id = l_table          union  all          select 1          from   ps_snapshot          where  table_id = l_table ) a;   if not FOUND then      execute 'drop function if exists ps_' || p_table || '_insert_trigger() cascade';      execute 'drop function if exists ps_' || p_table || '_raise_trigger()  cascade';      execute 'drop function if exists ps_' || p_table || '_update_trigger() cascade';      execute 'drop function if exists ps_' || p_table || '_delete_trigger() cascade';   else      perform ps_trigger_regenerate(l_table);       l_sql :=      'create trigger ps_' || p_table || '_before_insert ' ||     'before insert on ' || p_table || ' for each row ' ||     'execute procedure ps_' || p_table || '_insert_trigger()';      execute l_sql;       perform 1      from   ps_snapshot a, ps_column b      where  b.table_id = a.snapshot_id and a.table_id = l_table      and    b.type_name in ('min', 'max');      if FOUND then         l_sql :=         'create trigger ps_' || p_table || '_after_update ' ||        'after update on ' || p_table || ' for each row ' ||        'execute procedure ps_' || p_table || '_raise_trigger()';         execute l_sql;         l_sql :=         'create trigger ps_' || p_table || '_after_delete ' ||        'after delete on ' || p_table || ' for each row ' ||        'execute procedure ps_' || p_table || '_raise_trigger()';         execute l_sql;      else         l_sql :=         'create trigger ps_' || p_table || '_after_update ' ||        'after update on ' || p_table || ' for each row ' ||        'execute procedure ps_' || p_table || '_update_trigger()';         execute l_sql;         l_sql :=         'create trigger ps_' || p_table || '_after_delete ' ||        'after delete on ' || p_table || ' for each row ' ||        'execute procedure ps_' || p_table || '_delete_trigger()';         execute l_sql;      end if;   end if;    execute 'drop table if exists ' || p_snapshot; end; $$ language plpgsql; <\/code><\/pre>\n<p>  <\/div>\n<\/div>\n<p>  \u0417\u0434\u0435\u0441\u044c \u0442\u043e\u0436\u0435 \u043d\u0435\u0442 \u043d\u0438\u0447\u0435\u0433\u043e \u043f\u0440\u0438\u043d\u0446\u0438\u043f\u0438\u0430\u043b\u044c\u043d\u043e \u043d\u043e\u0432\u043e\u0433\u043e \u0438 \u0435\u0434\u0438\u043d\u0441\u0442\u0432\u0435\u043d\u043d\u043e\u0435, \u043e \u0447\u0435\u043c \u0445\u043e\u0442\u0435\u043b\u043e\u0441\u044c \u0431\u044b \u0437\u0430\u043c\u0435\u0442\u0438\u0442\u044c, \u044d\u0442\u043e \u0442\u043e, \u0447\u0442\u043e, \u0432 \u0441\u043b\u0443\u0447\u0430\u0435 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0430\u0433\u0440\u0435\u0433\u0430\u0442\u043e\u0432 &#8216;min&#8217; \u0438\u043b\u0438 &#8216;max&#8217;, \u043f\u0440\u0438 \u0441\u043e\u0437\u0434\u0430\u043d\u0438\u0438 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u043e\u0432, \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442\u0441\u044f \u0444\u0443\u043d\u043a\u0446\u0438\u044f ps_TABLE_raise_trigger(), \u0437\u0430\u043f\u0440\u0435\u0449\u0430\u044e\u0449\u0430\u044f \u0443\u0434\u0430\u043b\u0435\u043d\u0438\u044f \u0438 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u044f \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0435, \u043f\u043e \u043a\u043e\u0442\u043e\u0440\u043e\u0439 \u043f\u043e\u0441\u0442\u0440\u043e\u0435\u043d snapshot. \u042d\u0442\u043e \u0441\u0434\u0435\u043b\u0430\u043d\u043e \u043f\u043e\u0442\u043e\u043c\u0443, \u0447\u0442\u043e \u044f \u043d\u0435 \u0441\u043c\u043e\u0433 \u043f\u0440\u0438\u0434\u0443\u043c\u0430\u0442\u044c \u0430\u0434\u0435\u043a\u0432\u0430\u0442\u043d\u0443\u044e \u043f\u043e \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0440\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u044e \u043e\u0431\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f \u044d\u0442\u0438\u0445 \u0430\u0433\u0440\u0435\u0433\u0430\u0442\u043e\u0432 \u043f\u0440\u0438 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u0438 \u043e\u043f\u0435\u0440\u0430\u0442\u043e\u0440\u043e\u0432 update \u0438 delete \u0432 \u0438\u0441\u0445\u043e\u0434\u043d\u043e\u0439 \u0442\u0430\u0431\u043b\u0438\u0446\u0435.<\/p>\n<p>  \u041f\u043e\u0441\u043c\u043e\u0442\u0440\u0438\u043c, \u043a\u0430\u043a \u0432\u0441\u0435 \u044d\u0442\u043e \u0440\u0430\u0431\u043e\u0442\u0430\u0435\u0442. \u0421\u043e\u0437\u0434\u0430\u0434\u0438\u043c \u0442\u0435\u0441\u0442\u043e\u0432\u0443\u044e \u0442\u0430\u0431\u043b\u0438\u0446\u0443:<\/p>\n<pre><code class=\"sql\">create sequence test_seq;  create table test (   id            bigint         default nextval('test_seq') not null,   event_time    timestamp      not null,   customer_id   bigint         not null,   value         bigint         not null,   primary key(id) ); <\/code><\/pre>\n<p>  \u0422\u0435\u043f\u0435\u0440\u044c, \u0434\u043b\u044f \u0434\u043e\u0431\u0430\u0432\u043b\u0435\u043d\u0438\u044f \u0441\u0435\u043a\u0446\u0438\u0438, \u0434\u043e\u0441\u0442\u0430\u0442\u043e\u0447\u043d\u043e \u0432\u044b\u043f\u043e\u043b\u043d\u0438\u0442\u044c \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u0439 \u0437\u0430\u043f\u0440\u043e\u0441:<\/p>\n<pre><code class=\"sql\">select ps_add_range_partition('test', 'event_time', 'month', to_date('20130501', 'YYYYMMDD')) <\/code><\/pre>\n<p>  \u0412 \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442\u0435, \u0431\u0443\u0434\u0435\u0442 \u0441\u043e\u0437\u0434\u0430\u043d\u0430 \u0443\u043d\u0430\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u043d\u043d\u0430\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u0430 test_20130501, \u0432 \u043a\u043e\u0442\u043e\u0440\u0443\u044e \u0431\u0443\u0434\u0443\u0442 \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u043f\u043e\u043f\u0430\u0434\u0430\u0442\u044c \u0432\u0441\u0435 \u0437\u0430\u043f\u0438\u0441\u0438 \u0437\u0430 \u043c\u0430\u0439 \u043c\u0435\u0441\u044f\u0446.<\/p>\n<p>  \u0414\u043b\u044f \u0443\u0434\u0430\u043b\u0435\u043d\u0438\u044f \u0441\u0435\u043a\u0446\u0438\u0438, \u043c\u043e\u0436\u043d\u043e \u0432\u044b\u043f\u043e\u043b\u043d\u0438\u0442\u044c \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u0439 \u0437\u0430\u043f\u0440\u043e\u0441:<\/p>\n<pre><code class=\"sql\">select ps_del_range_partition('test', to_date('20130501', 'YYYYMMDD')) <\/code><\/pre>\n<p>  \u0421\u043e\u0437\u0434\u0430\u043d\u0438\u0435 snapshot \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0441\u043b\u043e\u0436\u043d\u0435\u0435, \u043f\u043e\u0441\u043a\u043e\u043b\u044c\u043a\u0443 \u043f\u0440\u0435\u0434\u0432\u0430\u0440\u0438\u0442\u0435\u043b\u044c\u043d\u043e \u0442\u0440\u0435\u0431\u0443\u0435\u0442\u0441\u044f \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0438\u0442\u044c \u0438\u043d\u0442\u0435\u0440\u0435\u0441\u0443\u044e\u0449\u0438\u0435 \u043d\u0430\u0441 \u0441\u0442\u043e\u043b\u0431\u0446\u044b:<\/p>\n<pre><code class=\"sql\">select ps_add_snapshot_column('test_month', 'customer_id', 'key') select ps_add_snapshot_column('test_month', 'event_time', 'date') select ps_add_snapshot_column('test_month', 'value_sum', 'value', 'sum') select ps_add_snapshot_column('test_month', 'value_cnt', 'value', 'cnt') select ps_add_snapshot_column('test_month', 'value_max', 'value', 'max') select ps_add_snapshot('test', 'test_month', 'month') <\/code><\/pre>\n<p>  \u0412 \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442\u0435, \u0431\u0443\u0434\u0435\u0442 \u0441\u043e\u0437\u0434\u0430\u043d\u0430 \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u043e\u0431\u043d\u043e\u0432\u043b\u044f\u0435\u043c\u0430\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u0430, \u043d\u0430 \u043e\u0441\u043d\u043e\u0432\u0430\u043d\u0438\u0438 \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0435\u0433\u043e \u0437\u0430\u043f\u0440\u043e\u0441\u0430:<\/p>\n<pre><code class=\"sql\">select customer_id, date_trunc('month', event_time),        sum(value) as value_sum,        count(value) as value_cnt,        max(value) as value_max from   test group by customer_id, date_trunc('month', event_time) <\/code><\/pre>\n<p>  \u0423\u0434\u0430\u043b\u0438\u0442\u044c snapshot, \u043c\u043e\u0436\u043d\u043e \u0432\u044b\u043f\u043e\u043b\u043d\u0438\u0432 \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u0439 \u0437\u0430\u043f\u0440\u043e\u0441:<\/p>\n<pre><code class=\"sql\">select ps_del_snapshot('test_month') <\/code><\/pre>\n<p>  \u041d\u0430 \u044d\u0442\u043e\u043c, \u043d\u0430 \u0441\u0435\u0433\u043e\u0434\u043d\u044f, \u0432\u0441\u0435. \u0421\u043a\u0440\u0438\u043f\u0442\u044b \u043c\u043e\u0436\u043d\u043e \u0437\u0430\u0431\u0440\u0430\u0442\u044c \u043d\u0430 <a href=\"https:\/\/github.com\/GlukKazan\/ps\">GitHub<\/a>.      \t \t\t   \t<\/p>\n<div class=\"clear\"><\/div>\n<\/p><\/div>\n<p> \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438 <a href=\"http:\/\/habrahabr.ru\/post\/177957\/\"> http:\/\/habrahabr.ru\/post\/177957\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<div class=\"content html_format\">   \t\u0425\u043e\u0442\u044f \u0442\u0435\u043c\u0430 \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u0443\u0436\u0435 \u043f\u043e\u0434\u043d\u0438\u043c\u0430\u043b\u0430\u0441\u044c <a href=\"http:\/\/habrahabr.ru\/post\/74984\/\">\u0440\u0430\u043d\u0435\u0435<\/a>, \u044f \u0445\u043e\u0447\u0443 \u043a \u043d\u0435\u0439 \u0432\u0435\u0440\u043d\u0443\u0442\u044c\u0441\u044f, \u0447\u0442\u043e\u0431\u044b \u0440\u0430\u0441\u0441\u043a\u0430\u0437\u0430\u0442\u044c \u043e \u0441\u0432\u043e\u0435\u043c \u043e\u043f\u044b\u0442\u0435 \u0440\u0435\u0448\u0435\u043d\u0438\u044f \u044d\u0442\u043e\u0439 \u0437\u0430\u0434\u0430\u0447\u0438, \u0432\u043e\u0437\u043d\u0438\u043a\u0448\u0435\u0439 \u0432 \u0441\u0432\u044f\u0437\u0438 \u0441 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e\u0441\u0442\u044c\u044e \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u043e\u0439 \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u043a\u043e\u0439 \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u043e\u0431\u044a\u0435\u043c\u043e\u0432 \u0434\u0430\u043d\u043d\u044b\u0445. \u041f\u043e\u043c\u0438\u043c\u043e \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f, \u044f \u0440\u0430\u0441\u0441\u043c\u043e\u0442\u0440\u044e \u043f\u0440\u0435\u0434\u0435\u043b\u044c\u043d\u043e \u0443\u043f\u0440\u043e\u0449\u0435\u043d\u043d\u0443\u044e \u0440\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u044e \u00ab\u0441\u043d\u0438\u043c\u043a\u043e\u0432\u00bb \u0430\u0433\u0440\u0435\u0433\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0445 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432, \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u043e\u0431\u043d\u043e\u0432\u043b\u044f\u0435\u043c\u044b\u0445 \u043f\u0440\u0438 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u0438 \u0438\u0441\u0445\u043e\u0434\u043d\u044b\u0445 \u0434\u0430\u043d\u043d\u044b\u0445..  <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-177957","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/177957","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=177957"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/177957\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=177957"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=177957"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=177957"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}