Question:
Guys, it's been a while since I've been looking for a way to do this, what would be the correct way to do an auto increment that depends on another field as in the example below?
id_empresa | id_pedido
1 | 1
1 | 2
1 | 3
1 | 4
2 | 1
2 | 2
3 | 1
I've already thought about making only one trigger and procedure for all tables, but I don't know how to do it.
A co-worker told me to do as follows:
1 This field would not be part of the primary key, so it would be a serial PK id, company_id integer FK PK, num_ped integer.
2 I create a friendly_sequence table just to reference these ids, for example a record in this table would be, (request, 1) and its columns would be (table, sequence).
3 Every time I place an order I make an UPDATE with RETURNING in the friendly_sequence table.
4 Receiving this value, I INSERT the order table
Answer:
At first, as rLinhares said, it's not a PK, so you should send it to the bank.
a simple sub-query solves the problem:
insert into pedidos (id_pedido,id_empresa) values ((select coalesce(max(id_pedido),0) + 1 from pedidos where id_empresa = 1 ), 1) returning id_pedido;
The coalesce, serves for the first request, which is when there is none, it must return 0.
Note that the company_id value is used twice.
Evading the question a little, and getting ahead of myself, every order must have items, in this case I used the returning which will be the FK in the items table (it must be a FK composed with company, order, item) and to maintain integrity, all this must be within a transaction.