Trigger to generate protocol number

I need help with creating a trigger that will generate a protocol number. The following sequence must be followed:

323926YYYYMMDDXXXXXX

• 323926 = fixed number

• YYYY = year

• MM = month

• DD = day

• XXXXXX = Number we must automatically generate from 1 that will go to 999999

My difficulty is that this incremental number should return to 000001 after the course of the day, summarizing: Every day the first protocol generated must start with the number 000001

My Code :

CREATE OR REPLACE FUNCTION crm.novo_ticket()
RETURNS trigger AS
$BODY$
BEGIN

NEW.protocolo =  '323926' || TO_CHAR(NEW.data_cadastro,'YYYYMMDD') || '000001';

RETURN NEW;

END $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Sorted out

SELECT 
    lpad(CAST((SELECT COUNT(1) + 1 INTO quant 
FROM   
    crm.ticket a 
WHERE 
    a.data_cadastro::DATE = CURRENT_TIMESTAMP(0)::DATE;)as VARCHAR),6,'0');

I wouldn't do this, I would store them all separate and generate them with a function.

CREATE TABLE foo (
  protodate date    DEFAULT CURRENT_DATE,
  id        serial,
  whatever  text
);
INSERT INTO foo (protodate, whatever)
VALUES
  ('2000-01-01', 'foo'),
  ('2000-01-01', 'bar'),
  ('2000-01-02', 'foo'),
  ('2000-01-03', 'baz');

CREATE OR REPLACE FUNCTION to_protoid( date, int )
RETURNS char(20)
AS $
  SELECT '323926' || to_char($1, 'YYYYMMDD') || to_char($2, 'FM099999');
$
LANGUAGE 'sql'
IMMUTABLE;

Now to query it,

SELECT to_protoid(
  protodate,
  row_number() OVER (PARTITION BY protodate ORDER BY id)::int
) AS protoid, *
FROM foo;
       protoid        | protodate  | id | whatever 
----------------------+------------+----+----------
 32392620000101000001 | 2000-01-01 |  1 | foo
 32392620000101000002 | 2000-01-01 |  2 | bar
 32392620000102000001 | 2000-01-02 |  3 | foo
 32392620000103000001 | 2000-01-03 |  4 | baz
(4 rows)

If that's not good enough you can always create a view or materialized view out of it...

CREATE VIEW myfoo
AS
  SELECT to_protoid(
    protodate,
    row_number() OVER (PARTITION BY protodate ORDER BY id)::int
  ) AS protoid, *
  FROM foo;

Why would I do this? More efficient storage. You store the inputs to the function rather than output which I find cleaner. You have easy use of indexes on date or int.

Lastly, if you want to, you can have a trigger insert into another table the id and the result of the call to to_protoid you can do that, though I wouldn't. I wouldn't use the protoid for anything internal. I would use your regular id and generate it as demonstrated.

StackExchange Database Administrators Q#175714, answer score: 4