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;