0

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.

2
  • What is your question? Nov 9, 2023 at 7:13
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Nov 9, 2023 at 10:04

1 Answer 1

0

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.

1
  • That would very likely perform worse than a row level trigger. Nov 10, 2023 at 7:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.