Postgresql sequencer not found

Question

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!

Answer 1

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...