How to get system columns in on statement trigger?

Question

I have an extension in my PostgreSQL 11 installation which adds malabel system column to each row. I need to get this column from OLD table but I can't get any of system columns in statement level trigger.

I can get system columns in a row level triggers, but I think using row level triggers can slow down database.

Answer 1

In PostgreSQL, system columns like ctid are not directly accessible in statement-level triggers. They are only available in row-level triggers. If you need to access system columns in a statement-level trigger, you will need to use a workaround.

You can create a custom function that calculates the system column values like this:

CREATE OR REPLACE FUNCTION get_custom_system_column(
    _table_name text,
    _primary_key_column text,
    _primary_key_value anyelement
) RETURNS tid AS
$$
DECLARE
    _ctid tid;
BEGIN
    EXECUTE FORMAT('SELECT ctid FROM %I WHERE %I = $1', _table_name, _primary_key_column)
    INTO _ctid
    USING _primary_key_value;
    
    RETURN _ctid;
END;
$$
LANGUAGE plpgsql;

In your statement-level trigger, you can now call this function to get the ctid:

CREATE OR REPLACE FUNCTION your_statement_level_trigger_function() RETURNS TRIGGER AS
$$
DECLARE
    _ctid tid;
BEGIN
    -- Assuming your primary key is "id" and your table name is "your_table"
    _ctid := get_custom_system_column('your_table', 'id', NEW.id);
    
    -- Now you can use _ctid in your trigger logic
    -- ...
    
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

Due to the limited information you provided in the question this may not be as comprehensive but should get you started.