how to see properties available for access with NEW plpgsql

Question

Say I have a PLPGSQL function

CREATE OR REPLACE FUNCTION function_name
RETURNS TRIGGER AS ...
BEGIN
   PERFORM
      1
   FROM
      table1 t1
   JOIN
      table2 t2 USING( column_name )
   WHERE
      t1.column_name = NEW.column_name;
   RETURN NEW;
END;

DROP TRIGGER IF EXISTS trigger_name
   ON table1;
CREATE TRIGGER trigger_name
   BEFORE INSERT ON table1
   FOR EACH ROW EXECUTE PROCEDURE function_name;

1. I noticed that only some columns in table1 and table2 are accessible with NEW.column_name. How can I see the full list of columns I can access with NEW?

2. Additionally, if there is a column in table1 or table2 that I cannot access with NEW, how can I make it accessible to NEW?

Answer 1

Look at this line:

BEFORE INSERT ON table1

This tells you that the trigger is executed before an INSERT ON table1. This means that you will have NEW.column_name for any column of table1, while table2 is unchanged by the trigger and makes no sense to use OLD or NEW on it, hence it illegal. So, to be precise: NEW works for table1 columns and does not work on table2 columns.