June 28, 2017

PostgreSQL Quarterly Partition Function

Here is some quick code on how to create a partition function using inherit tables on PostgreSql, is simple but hopefully this will save some poor soul the documentation reading and for me save me some time in the future, currently things been very busy working in Ms Sql and PostgreSql doing several conversions from one DB to the other.
1a.-Create sequence
1b.- Create table
2.-Create function
3.-create trigger




-- FUNCTION:
 TableSchemaNameHere.TableName_partition_insert()

-- DROP FUNCTION TableSchemaNameHere.event_partiont_insert();
CREATE SEQUENCE seq_TableSchemaNameHere.TableNameHere_TableColumn
minvalue 1 nomaxvalue increment by 1;

create table TableSchemaNameHere.Tablename
(
 PKID int  DEFAULT nextval('seq_TableSchemaNameHere.TableNameHere_TableColumn') NOT NULL,
 insertdate timestamp(6) without time zone default now(),
 time timestamp without time zone,
 constraint pk_ primary key (pkid)
)

CREATE or Replace FUNCTION TableSchemaNameHere.TableName_partiont_insert()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100.0
    VOLATILE NOT LEAKPROOF
   
AS $BODY$

                              declare
                                             declare
                                             _partition_date varchar(50);
               _partition varchar(80);
            _IsNewChild boolean;
             _current_day int;
            _first_day varchar(2) := '01';
            _last_day varchar(2);
            _pick_day varchar(2);
            _tablename varchar(80);

    begin
               -- get current day
        _current_day  := date_part('day',new.time);
     
        -- find last day of the month function
         _last_day := date_part ('day',TableSchemaNameHere.last_day (new.time::date));
        -- get YYYY_MM format
               _partition_date := to_char (NEW.time,'YYYY_MM');
     
        -- find if first or second sequence
        if _current_day < 16
         then
            _pick_day := _first_day;
            _partition_date  := _partition_date||'_'|| _first_day;
         
          else
           _pick_day = _last_day;
           _partition_date := _partition_date||'_'|| _last_day;
         end if;
       
       _partition := TG_TABLE_NAME || '_' || _partition_date;
       _tablename := _partition;
        if not exists ( select 1 from pg_tables where schemaname= TG_TABLE_SCHEMA and tablename= _partition)
        then
     
               RAISE NOTICE 'A partition has been created %',TG_TABLE_SCHEMA ||'.'|| _partition;
            _IsNewChild = TRUE;
            EXECUTE 'create table '|| TG_TABLE_SCHEMA ||'.'|| _partition || ' (check( to_char (time,''YYYY_MM'||'_'|| _pick_day||''') = '''|| to_char (NEW.time,'YYYY_MM')||'_'||_pick_day||''')) INHERITS ( TableSchemaNameHere.TableNamehere);';
         
        end if;
        execute 'insert into '|| TG_TABLE_SCHEMA || '.' || _partition || ' SELECT(' || TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').* RETURNING eventid;';
        if (_IsNewChild)
         then
           -- Add primary key
                                                            EXECUTE format('ALTER TABLE '||TG_TABLE_SCHEMA||'.%s ADD PRIMARY KEY(pkid)', _partition);
                -- Assign owner of of inherited table
                                                             EXECUTE format('ALTER TABLE '||TG_TABLE_SCHEMA||'.%s OWNER TO db_owner', _partition);

                                                         
                                                            -- Add FK promo_batch_id
                                                            EXECUTE format('ALTER TABLE '||TG_TABLE_SCHEMA||'.%s ADD CONSTRAINT FK_%s FOREIGN KEY(columnId) REFERENCES TableSchemaNameHere.fkid (fkid)  MATCH FULL',_tablename, _tablename);

                                                            -- Need to define indexes for inherited tables
                                                            EXECUTE format('CREATE INDEX ix_%s_EventTypeID ON '||TG_TABLE_SCHEMA||'.%s USING btree ("indexkeycolumn")', _tablename, _tablename);
             
                                                         
         end if;
        RETURN NULL;
    end
 
$BODY$;

----Create trigger
CREATE TRIGGER tr_TableName_Partition
BEFORE INSERT ON TableSchemaNameHere.TableNameHere
FOR EACH ROW EXECUTE PROCEDURE myschema.server_partition_function();

Contact Form

Name

Email *

Message *