February 17, 2016

PostgresSql Inherit table Generator


Working in many projects and learning new technologies, now everything is about postgresSql and non-sql databases...fun.fun.fun.
So after most debate about creating partitions we decided to used inherit tables. 
So here is a function that will do that :-)
Cheers...

-- Function: SchemaName.batch_gen_complete(integer, character varying)

-- DROP FUNCTION SchemaName.batch_gen_complete(integer, character varying);

CREATE OR REPLACE FUNCTION generate_inherid_child(
    _batch_stage integer,
    _modified_by character varying)
  RETURNS void AS
$BODY$
DECLARE
               _count int;
               _gen_count int;
               _active_count int;
               _is_clean_unique bool;
               _is_clean_duplicate bool;
               _tablename text;
               _exist bool;
BEGIN

    --- Get count of records from stage
              
               SELECT count INTO _count FROM batch_stage WHERE promo_batch_stage = _promo_batch_stage;

               SELECT generated_count, is_clean_unique, is_clean_duplicate INTO _gen_count, _is_clean_unique, _is_clean_duplicate
               FROM batch_generator
               WHERE promo_batch_stage_id = _promo_batch_stage_id;

               IF _gen_count < _count Then

                              RAISE EXCEPTION 'Not enough promo codes available to be able to complete batch: %', _promo_batch_stage;
                              return;

               End If;

               IF _is_clean_unique = false Then

                              RAISE EXCEPTION 'Batch is not marked as clean for unique values, so unable to complete: %', _promo_batch_stage;
                              return;

               End If;

               IF _is_clean_duplicate = false Then

                              RAISE EXCEPTION 'Batch is not marked as clean for duplicate values against active promotions, so unable to complete: %', _promo_batch_stage;
                              return;

               End If;

               SELECT count(*) INTO _active_count FROM generator_parent WHERE promo_batch_stage_id = _promo_batch_stage_id;

               IF _active_count >= _count Then

                              --RAISE EXCEPTION 'Batch already has entries in promotion table, so unable to complete: %', _promo_batch_stage;
                              --return;
                              RAISE INFO '% - %', 'Batch already has entries in promotion table, marking batch complete.', now();

               Else

                              If _count > 9999 Then
                                            
                                             -- Put large batches into an inherited table

                                             _tablename := '_batch_' || _promo_batch_stage;

                                             EXECUTE 'SELECT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname=''SchemaName'' and tablename=''' || quote_ident(_tablename) || ''')' INTO _exist;

                                             IF _exist Then

                                                            -- Delete data from a previous run
                                                            EXECUTE 'TRUNCATE SchemaName.' || quote_ident(_tablename);
                                            
                                             Else
                                            
                                                            -- Create inherited table for large batches
                                                            EXECUTE 'CREATE TABLE SchemaName.' || quote_ident(_tablename) || ' (CHECK ( batch_stage = ' || _batch_stage || ' )) INHERITS (generator_parent)';

                                             End If;
                                            
                                             -- Copy data from temp table to inherited table
                                             EXECUTE format('Insert Into SchemaName.%s (promo_batch_stage, promo_number, is_active, created_by, created_date)       
                                                            Select promo_batch_stage, promo_number, 1::boolean, %L, now()
                                                            From generator_parent_temp
                                                            Where promo_batch_stage = %L
                                                            LIMIT %L'
                                                            , _tablename, _modified_by, _promo_batch_stage, _count);

                                             IF _exist = false Then

                                                            -- Assign owner of of inherited table
                                                            EXECUTE format('ALTER TABLE SchemaName.%s OWNER TO db_owner', _tablename);

                                                            -- Add primary key
                                                            EXECUTE format('ALTER TABLE SchemaName.%s ADD PRIMARY KEY(child_id)', _tablename, _tablename);
                                                           
                                                            --Add FK promo_batch_stage
                                                            EXECUTE format('ALTER TABLE SchemaName.%s ADD CONSTRAINT FK_%s FOREIGN KEY(batch_stage) REFERENCES SchemaName.promo_batch (batch_stage)  MATCH FULL',_tablename, _tablename);

                                                            -- Need to define indexes for inherited tables
                                                            EXECUTE format('CREATE INDEX ix_%s_promo_batch_stage ON SchemaName.%s USING btree ("batch_stage")', _tablename, _tablename);
                                                            //EXECUTE format('CREATE UNIQUE INDEX ix_%s_promo_number ON SchemaName.%s ("")', _tablename, _tablename);

                                             End If;
                                            
                              Else

                                             -- Put smaller batches into general table

                                             Insert Into generator_parent_general (promo_batch_stage, promo_number, is_active, created_by, created_date)
                                             Select batch_stage_id, batch_number, 1::boolean, _modified_by, now()
                                             From generator_parent_temp
                                             Where batch_stage_id = _batch_stage_id
                                             LIMIT _count;
                                            
               End If;

               End If;

               -- Remove codes from staging table
               Delete From generator_parent_temp
               Where batch_stage = _batch_stage;
                             
               -- Mark any incomplete log entries as complete

              
END      
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION SchemaName.batch_gen_complete(integer, character varying)
  OWNER TO db_owner;
GRANT EXECUTE ON FUNCTION SchemaName.batch_gen_complete(integer, character varying) TO public;
GRANT EXECUTE ON FUNCTION SchemaName.batch_gen_complete(integer, character varying) TO db_executor;
REVOKE ALL ON FUNCTION SchemaName.batch_gen_complete(integer, character varying) FROM db_owner;

No comments:

Post a Comment

Contact Form

Name

Email *

Message *