How to make an auto ID increment relative to another field in Postgresql "Postgresql"?

Question

Personal it has been a long time since I look for a way to do this, what would be the correct way to make 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 Rigger and process for all tables, only I don’t know how to do.

A coworker told me to do it this way:

1 This field would not be part of the primary key, so it would be a PK serial id, integer id id FK PK, integer num_ped.

2 I create a sequential table only 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 do an UPDATE with RETURNING in the sequential table.

4 Receiving this value I give an INSERT in the requested table

Answer 1

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 order, which is when there is none, it must return 0.

Note that the id_company value is used twice.

Escaping a little the question, and in advance, every request must have items, in this case I used the returning that will be the FK in the table of items (must be a FK composed with company, order, item) and to maintain integrity, this must all be within a transaction.

Answer 2

I got you guys

Based on this link and with a little improvement, follows below the final result

-- tabela onde ficará guardado as sequências de cada loja tabela e coluna
    CREATE TABLE public.sequencia_tabelas (
    id_empr integer NOT NULL,
    id_filial character(14) NOT NULL,
    tabela varchar(100) NOT NULL,
    coluna varchar(100) NOT NULL,
    proximo_indice integer NOT NULL
    );

ALTER TABLE public.sequencia_tabelas OWNER TO postgres;

ALTER TABLE ONLY public.sequencia_tabelas
    ADD CONSTRAINT sequencia_tabelas_pkey PRIMARY KEY (id_empr, id_filial, tabela, coluna);

COMMENT ON TABLE public.sequencia_tabelas
    IS 'Tabela de sequências amigáveis por empresa, filial, tabela e coluna';

-- Exemplo de inserção
-- INSERT INTO sequencia_tabelas(id_empr, id_filial, tabela, coluna, proximo_indice) 
-- VALUES ('1', '12345678910111', 'pedido', 'numero_pedido', '0');



-- function proximo id
CREATE OR REPLACE FUNCTION proximo_id(id_empr text, id_filial text, tabela text, coluna text) RETURNS integer AS $$
DECLARE
    proximo_valor integer;
BEGIN
    EXECUTE format('UPDATE sequencia_tabelas SET proximo_indice = proximo_indice + 1 WHERE id_empr = %L AND id_filial = %L AND tabela = %L AND coluna = %L RETURNING proximo_indice', id_empr, id_filial, tabela, coluna) INTO proximo_valor;
    RETURN proximo_valor;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION proximo_id(text, text, text, text) IS 'Incrementa e retorna o valor da coluna inteira $2 na tabela $1';

-- Exemplo de inserção
-- INSERT INTO pedido(id_empr, id_filial, numero_pedido, emissao, observacoes) 
-- VALUES ('1', '12345678910111', proximo_id('1','12345678910111','pedido','numero_pedido'), LOCALTIMESTAMP, 'teste id');