postgresql - Postgres function NULL value for row that references NEW

Question

I have a trigger setup to fire after a row is updated or inserted for table A. There is also a table B that references table A, but also has another column which I want to analyze, a boolean (so this function would only be usable on an UPDATE).

When I try to access the column:

SELECT col1 FROM B WHERE B.aID = NEW.ID;

This column col1 is always NULL.

Why is this? How can I get the boolean value upon an update?

Answer 1

Most probably you are running into a naming conflict. Parameter names (IN and OUT parameters) are visible in the function body (almost) anywhere and take precedence over unqualified column names. Did you declare col1 as variable in the function?

To avoid the conflict, table-qualify the column name:

SELECT b.col1 FROM tableb b WHERE b.aID = NEW.ID;

This is good practice in any case.

It is also good practice to prefix variable names, so they wouldn't normally conflict with table columns. Like: _col1.