Schema in trigger in logical replication

From: "Fontana Daniel C(dot) \(Desartec S(dot)R(dot)L(dot)\)" <desartecsrl(at)gmail(dot)com>
To: <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Schema in trigger in logical replication
Date: 2021-03-03 22:35:04
Message-ID: 003501d7107d$75a67a80$60f36f80$@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Using postgres 12.5 in DBA schema, this trigger is executed when the table
is updated through a logical replication. Why is it necessary to name the
schema for it to work?
When I update the table manually, if it Works.

Example.

This trigger function does not work

CREATE FUNCTION dba.ft_pos_sync_eco_tx()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
begin
INSERT INTO pos_sync_eco_rx
( id_terminales,
fecha_y_hora_tx,
fecha_y_hora_rx,
dato,
usuario,
fecha_y_hora_proceso )
VALUES ( new.id_terminales,
localtimestamp,
localtimestamp,
new.dato,
new.usuario ,
localtimestamp )
ON CONFLICT (id_terminales)
DO UPDATE SET (fecha_y_hora_tx,
fecha_y_hora_rx,
dato,
usuario,
fecha_y_hora_proceso ) =
(new.fecha_y_hora_tx,
localtimestamp,
new.dato,
new.usuario,
new.fecha_y_hora_proceso );

return new;

end
$BODY$;

This trigger function, if it works

CREATE FUNCTION dba.ft_pos_sync_eco_tx()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
begin
INSERT INTO dba.pos_sync_eco_rx
( id_terminales,
fecha_y_hora_tx,
fecha_y_hora_rx,
dato,
usuario,
fecha_y_hora_proceso )
VALUES ( new.id_terminales,
localtimestamp,
localtimestamp,
new.dato,
new.usuario ,
localtimestamp )
ON CONFLICT (id_terminales)
DO UPDATE SET (fecha_y_hora_tx,
fecha_y_hora_rx,
dato,
usuario,
fecha_y_hora_proceso ) =
(new.fecha_y_hora_tx,
localtimestamp,
new.dato,
new.usuario,
new.fecha_y_hora_proceso );

return new;

end
$BODY$;

--
El software de antivirus Avast ha analizado este correo electrónico en busca de virus.
https://www.avast.com/antivirus

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2021-03-03 23:19:01 Re: Schema in trigger in logical replication
Previous Message Valeria Kaplan 2021-03-03 22:09:45 Re: Code of Conduct: Russian Translation for Review