CODEWITHSUNDEEP
sqlpostgresqlplpgsql
nilesh
nilesh

Reputation: 1336

Column reference is ambiguous for local variable in function

The following is my function 

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'

But its giving me following error when this function is called

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.

There is no column named entid present in any table. Why is it giving such an error?

Upvotes: 0

Views: 730

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 248285

The problem is that the subquery with the alias tmp you define in your query has a column named entid, so the parser doesn't know if the reference to entid in the WHERE condition refers to that column or to the function variable.

True, they contain the same value, so it doesn't matter in reality, but there is still a syntactic ambiguity.

You'd have to qualify the entid in the WHERE condition either with the alias tmp or with the name of the function add_config_to_enterprise to resolve the ambiguity.

Your query is unnecessarily complicated and ill-indented, so much so that I cannot understand and improve it. Simplifying it would get rid of the problem.

Upvotes: 0

klin
klin

Reputation: 121919

Use a subquery alias in the WHERE condition:

...
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));
...

or rename entId in the SELECT list of the subquery:

...
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));
...

Upvotes: 2

Related Questions