From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | "Fontana Daniel C(dot) (Desartec S(dot)R(dot)L(dot))" <desartecsrl(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Schema in trigger in logical replication |
Date: | 2021-03-03 23:19:01 |
Message-ID: | 217507fd-1fde-20f7-3950-7341602277a2@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 3/3/21 2:35 PM, Fontana Daniel C. (Desartec S.R.L.) wrote:
> 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?
Because the search_path does include the schema?
> 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$;
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Stacey Haysler | 2021-03-04 02:20:39 | Re: Code of Conduct: Russian Translation for Review |
Previous Message | Fontana Daniel C. (Desartec S.R.L.) | 2021-03-03 22:35:04 | Schema in trigger in logical replication |