0

I'm creating an item record number generator. The goal is to have a table to house all record number/sequencers for a variety of different types. For example, for a "Part" you may want a number like "110-00001-00". The seqItem table would hold the definition of this number generator (SeqName, preFix, postFix, padding).

InventorySys=# SELECT * FROM information_schema.sequences;
 sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option
------------------+-----------------+---------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------+-----------+--------------
(0 rows)


InventorySys=# \d "SeqItem"
               Table "public.SeqItem"
 Column  |  Type   | Collation | Nullable | Default
---------+---------+-----------+----------+---------
 SeqName | text    |           | not null |
 prefix  | text    |           |          |
 postfix | text    |           |          |
 padding | integer |           | not null | 5
Indexes:
    "SeqItem_pkey" PRIMARY KEY, btree ("SeqName")
    "SeqName" UNIQUE CONSTRAINT, btree ("SeqName")
Triggers:
    dropsqeitem AFTER DELETE ON "SeqItem" FOR EACH ROW EXECUTE FUNCTION "RemoveSeq"()
    inssqeitem AFTER INSERT ON "SeqItem" FOR EACH ROW EXECUTE FUNCTION "CreateSeq"()


InventorySys=#

When a new record is added to this table, I want to create a new Sequence with the "SeqName". So, I've created the following Trigger/Function:

CREATE OR REPLACE FUNCTION public."CreateSeq"() RETURNS TRIGGER  as $CreateSeq$
BEGIN
EXECUTE  format('CREATE SEQUENCE %I INCREMENT BY 1 MINVALUE 1 NO MAXVALUE START WITH 1 NO CYCLE',  NEW."SeqName");
RETURN NEW;
END
$CreateSeq$ LANGUAGE plpgsql;

CREATE  TRIGGER insSqeItem AFTER INSERT ON "SeqItem"
    FOR EACH ROW EXECUTE FUNCTION "CreateSeq"();

This works perfect, and with each new record, I get a new sequencer created. I've also created a another function/trigger to delete the sequencer if the row is deleted.

CREATE OR REPLACE FUNCTION public."RemoveSeq"() RETURNS TRIGGER  as $RemoveSeq$
BEGIN
EXECUTE  format('DROP SEQUENCE IF EXISTS  %I',  OLD."SeqName");
RETURN NEW;
END
$RemoveSeq$ LANGUAGE plpgsql;

CREATE  TRIGGER dropSqeItem AFTER DELETE ON "SeqItem"
    FOR EACH ROW EXECUTE FUNCTION "RemoveSeq"();

So far so good! So, Let's add a new record and see that the Sequencer was added:

InventorySys=# INSERT into "SeqItem" ("SeqName", prefix, padding) Values ('testItem1', '115-',6);
INSERT 0 1
InventorySys=# SELECT * FROM "SeqItem";
  SeqName  | prefix | postfix | padding
-----------+--------+---------+---------
 testItem1 | 115-   |         |       6
(1 row)


InventorySys=# SELECT * FROM information_schema.sequences;
 sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value |    maximum_value    | increment | cycle_option
------------------+-----------------+---------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------
 InventorySys     | public          | testItem1     | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
(1 row)


InventorySys=#

However, when I try to use the newly created sequencer from the trigger I get the following error that the sequencer is not found.

InventorySys=# select CONCAT("prefix", LPAD((select nextval("SeqItem"."SeqName"))::text, "padding", '0') , "postfix") from "SeqItem" where "SeqName" = 'testItem1' ;
ERROR:  relation "testitem1" does not exist
InventorySys=#

ERROR: relation "testitem1" does not exist

If I create a new Sequencer without the Trigger, it works fine:

InventorySys=# CREATE SEQUENCE test1;
CREATE SEQUENCE
InventorySys=# SELECT NEXTVAL ('test1');
 nextval
---------
       1
(1 row)


InventorySys=#

And if I add that sequencer to my query, it works fine:

InventorySys=# select CONCAT("prefix", LPAD((select nextval('test1'))::text, "padding", '0') , "postfix") from "SeqItem" where "SeqName" = 'testItem1' ;
   concat
------------
 115-000002
(1 row)


InventorySys=#

Both sequencers look fine to me, but the one created by the Trigger I cannot get to work...

InventorySys=#  SELECT * FROM information_schema.sequences;
 sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value |    maximum_value    | increment | cycle_option
------------------+-----------------+---------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------
 InventorySys     | public          | testItem1     | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
 InventorySys     | public          | test1         | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
(2 rows)


InventorySys=#

Any help would be greatly appreciated!

1 Answer 1

0

Ok, I think I figured out my problem. It appears that the sequencer name needs to be all lower case? Or, I should say that if I use all lower case it works just fine...

InventorySys=# INSERT into "SeqItem" ("SeqName", prefix, padding) Values ('testitem3', '110-',4);
INSERT 0 1

InventorySys=# select CONCAT("prefix", LPAD((select nextval("SeqItem"."SeqName"))::text, "padding", '0') , "postfix") from "SeqItem" where "SeqName" = 'testitem3' ;
  concat
----------
 110-0001
(1 row)


InventorySys=# select CONCAT("prefix", LPAD((select nextval("SeqItem"."SeqName"))::text, "padding", '0') , "postfix") from "SeqItem" where "SeqName" = 'testitem3' ;
  concat
----------
 110-0002
(1 row)


InventorySys=#

I'm not sure why it will not accept upper and lower case characters...

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.