Postgresql – How to SELECT an ‘instance’ in PostgreSQL

change-data-captureetlpostgresqlstored-procedurestrigger

I am reading a paper called "Real Time Delta Extraction Based on Triggers to Support Data Warehousing" from the IEEE and they proposed the following code:

CREATE TRIGGER cdc_table ON db.table AS
DECLARE @OPER, @INST, @TAB, @PK, @ID
Select @TAB=table of altered tuple
Select @PK= primary key of @TAB
Select @INST= instance in which @TAB belongs
Begin
IF(@OPER = DELETE)
Select @ID= ID from tuple before deletion;
ELSE
Select @ID= ID from tuple after insertion or update;
ENDIF
Insert into log table @ID, @PK, @TAB, @INST, @OPER;
END;

In the line 5 they are asking me to SELECT the instance of the table I am altering. How can I do that?

The log table is defined as follows:

  • log id – an integer type field, having a sequential
    number, that stores the execution order of the
    operations;
  • instance – a text type field, stores the name of the
    instance whose tuple was altered;
  • table – a text type field, stores the name of the table
    whose tuple was altered;
  • operation – stores the type of operation that was
    executed: INSERT, UPDATE or DELETE;
  • pk column – a text type field, stores the name of the
    column that was used as a unique identifier in the
    table whose tuple was altered;
  • tuple id – value of the unique identifier of the altered
    tuple.

Thanks!

Best Answer

This is a really bad idea to begin with, you're recreating logical replication. Just refer to the PostgreSQL guide to logical replication.

That said, what you want is the PostgreSQL plpgsql Trigger "special variables.

  • TG_TABLE_NAME Data type name; the name of the table that caused the trigger invocation.

  • TG_TABLE_SCHEMA Data type name; the name of the schema of the table that caused the trigger invocation.

Related Question