sql - 列引用对于函数中的局部变量不明确

Question

下面是我的函数

CREATE OR REPLACE FUNCTION  add_config_to_enterprise(configKey character varying, enterpriseValue character varying) RETURNS void AS 
$BODY$
DECLARE
entId character varying;
   BEGIN
        FOR entId IN
    SELECT enterprise_id
    FROM tenant
LOOP
    INSERT INTO enterprise_configuration(enterprise_configuration_id, product_configuration_id, enterprise_id, value)
SELECT *
FROM 
    (SELECT uuid_generate_v4(),
        (SELECT pc.product_configuration_id
        FROM product_configuration pc
        WHERE pc.configuration_key = configKey), 
            entId, enterpriseValue) AS tmp
            WHERE NOT EXISTS 
            (SELECT e.enterprise_configuration_id
            FROM enterprise_configuration e
            WHERE e.enterprise_id = entId AND e.product_configuration_id = 
                (SELECT p.product_configuration_id
                FROM product_configuration p
                WHERE p.configuration_key = configKey));

END LOOP;


   END;

$BODY$ LANGUAGE 'plpgsql'

但是当这个函数被调用时它给我以下错误

ERROR:  column reference "entid" is ambiguous
LINE 12:             WHERE e.enterprise_id = entId AND e.product_conf...
                                             ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

任何表中都没有名为 entid 的列。为什么会报这样的错误？

Answer 1

在 WHERE 条件中使用子查询别名:

...
SELECT *
FROM 
    (SELECT uuid_generate_v4(),
        (SELECT pc.product_configuration_id
        FROM product_configuration pc
        WHERE pc.configuration_key = configKey), 
            entId, enterpriseValue) AS tmp
            WHERE NOT EXISTS 
            (SELECT e.enterprise_configuration_id
            FROM enterprise_configuration e
            WHERE e.enterprise_id = tmp.entId AND e.product_configuration_id = -- here
                (SELECT p.product_configuration_id
                FROM product_configuration p
                WHERE p.configuration_key = configKey));
...

或者重命名子查询的SELECT列表中的entId:

...
SELECT *
FROM 
    (SELECT uuid_generate_v4(),
        (SELECT pc.product_configuration_id
        FROM product_configuration pc
        WHERE pc.configuration_key = configKey), 
            entId as eId, enterpriseValue) AS tmp -- here
            WHERE NOT EXISTS 
            (SELECT e.enterprise_configuration_id
            FROM enterprise_configuration e
            WHERE e.enterprise_id = entId AND e.product_configuration_id =
                (SELECT p.product_configuration_id
                FROM product_configuration p
                WHERE p.configuration_key = configKey));
...