How can I SELECT an 'instance' in PostgreSQL?

Question

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!

Answer 1

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.