how to capture an id dynamically and in sequence in postgresql?

Question

how to capture an id dynamically and in sequence in postgresql?

Navigation

#1 by (0 votes)

0

It refers to an audit table that I am creating, the purpose is to register the fields of the table in question. But I can not capture the ex id:

create or replace function audit_unidade.log_unidade_escola()
returns trigger as
$body$ 
begin
-- Aqui temos um bloco IF que confirmará o tipo de operação.
IF (TG_OP = 'INSERT') THEN
    INSERT INTO audit_unidade.escola (after_updade) 
        VALUES (
        (select 'id: ' || id || ' || ' || 'Tipo: ' || tipo_unidade || ' || ' || 'Escola: ' || nome_escola || ' || ' || 'CEP: ' || cep || ' || ' 
            || 'Bairro: ' || bairro || ' || ' || 'Rua: ' || ' || ' || rua || 'Nº: ' || numero || ' || ' || 'Email: ' || email || ' || ' || 'telefone1: ' ||
             coalesce(telefone1,'') || ' || ' || 'Telefone2: ' || coalesce(telefone2,'') || ' || ' || 'id_diretor: ' || id_diretor || 'id_secretario: ' ||
             id_secretario || ' || '  || 'id_coordenador1: ' || id_coordenador1 || ' || ' || 'id_coordenador2: ' || coalesce(id_coordenador2, 0)  
        from unidade.escola where id = (select nextval('unidade.escola_id_seq'))));
RETURN NEW;
END IF;
RETURN NULL;
end; $body$
language 'plpgsql'

I tried the (select nextval('unidade.escola_id_seq') command above but it does not work, the table field "audit_unit_select" in the after_update field does not give an error, it does not record anything referring to the table "unit_collection" because of the id I can not capture it would be grateful for that help.

postgresql id dinamico

asked by anonymous 07.12.2018 / 14:17

1 answer

Print page with Background Red item on screen prevent submitting - Javascript / Jquery

score 0 · Answer 1

This is possibly happening because the trigger is being executed before the data is actually inserted into the table. This can be corrected by adding the AFTER INSERT condition in the trigger creation:

CREATE TRIGGER audit_unidade.log_unidade_escola_trigger AFTER INSERT OR UPDATE
    ON unidade.escola FOR EACH ROW EXECUTE PROCEDURE audit_unidade.log_unidade_escola();