引用NEW的行的Postgres函数NULL值 [英] Postgres Function NULL value for row that references NEW

问题描述

我有一个触发器设置要在为表A更新或插入一行后触发.还有一个表B引用了表A，但是还有另一个要分析的列，即布尔值(因此此函数只能在UPDATE上使用).

I have a trigger setup to fire off 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).

当我尝试访问该列时: SELECT col1 FROM B WHERE B.aID = NEW.ID;

When I try to access the column: SELECT col1 FROM B WHERE B.aID = NEW.ID;

此列col1始终为NULL.

这是为什么?如何在更新时获取布尔值?

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

推荐答案

很可能您正在遇到命名冲突.参数名称(IN和OUT参数)在函数主体中(几乎)任何位置都可见，并且优先于不合格的列名称.您是否在函数中将col1声明为变量?

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?

为避免冲突，请表限定列名:

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

在任何情况下，这都是一个好习惯.

This is good practice in any case.

在变量名前添加前缀也是一种好习惯，因此它们通常不会与表列冲突.就像:_col1.

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

这篇关于引用NEW的行的Postgres函数NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！