3

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');
Improve this question
3
  • 1
    Preface: I do MS SQL not usually postgres. Can you make a table to hold and calculate your key values? Store the date and the incremented number. Now you can compare the date and the number and using a system date function to validate the date you can tell whether to use the next number in sequence or restart the sequence at 000001. Commented Jun 7, 2017 at 20:17
  • CURRENT_TIMESTAMP(0)::DATE can be simplified to CURRENT_DATE Also: the ; after the cast is wrong Commented Jun 8, 2017 at 6:59
  • Using count(*) + 1 to generate that number is a broken approach if you have more then one concurrent user in your system (plus: it's not going to scale for many rows at all). Using a sequence is the only scalable and concurrency safe way to generate unique numbers. Evan's answer will be a lot more efficient as well Commented Jun 8, 2017 at 7:00

2 Answers 2

Reset to default
4

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.

Improve this answer
0

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');
Improve this answer
5
  • 1
    I've added your sorted out to your question, please delete this answer, you can edit your question using Edit button. Commented Jun 7, 2017 at 21:04
  • 1
    Please use the edit link on your question to add additional information. The Post Answer button should be used only for complete answers to the question. - From Review Commented Jun 7, 2017 at 21:34
  • @McNets: if that query solved the problem, then it should definitely be an answer. Not part of the question (and the answer should be accepted). Commented Jun 8, 2017 at 6:58
  • CURRENT_TIMESTAMP(0)::DATE can be simplified to CURRENT_DATE Commented Jun 8, 2017 at 6:58
  • This is not concurrent-safe. Good luck. Commented Jun 12, 2017 at 17:23

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.