How to make an auto increment ID relative to another field in PostgreSQL "PostgreSQL"? [duplicate]

Question

How to make an auto increment ID relative to another field in PostgreSQL "PostgreSQL"? [duplicate]

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

id_empresa | id_pedido
 1         | 1
 1         | 2
 1         | 3
 1         | 4
 2         | 1
 2         | 2
 3         | 1

I already thought of doing only one trigger and procedure for all the tables, but I do not know how to do it.

A co-worker told me to do this:

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

3 Every time I place an order I make an UPDATE with RETURNING on the string_table table.

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

postgresql chave-composta auto-incremento

asked by anonymous 03.12.2018 / 14:07

2 answers

1

At first, as rLinhares said, it is 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 should return 0.

Notice that the value of the business_id is used twice.

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

03.12.2018 / 14:42

Laravel and React Native Push Notification SELECT column type DATE by HH24: mm: ss

score 0 · Accepted Answer

I've got people

Based on this link and with a little improvement, below is 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');