CODEWITHSUNDEEP
postgresqlplpgsql
peter.petrov
peter.petrov

Reputation: 39457

Column reference "id" is ambiguous - It could refer to either a PL/pgSQL variable or a table column

I have this simple test function in Postgres (in my test schema).

CREATE OR REPLACE FUNCTION test.func_001
(
par_id int
)
RETURNS TABLE
(
id int
)

AS
$BODY$
DECLARE

    var_id int;

BEGIN

    update test.item    --- this is a table 
    set 
    id = 4
    WHERE
    id = 44;

return query 
select 1000 as id;

END;
$BODY$
LANGUAGE  plpgsql;

The test.item table has a single id column.

I get the error below while trying to run the function.

Query execution failed

Reason:
SQL Error [42702]: ERROR: column reference "id" is ambiguous
  Detail: It could refer to either a PL/pgSQL variable or a table column.
  Where: PL/pgSQL function test.func_001(integer) line 8 at SQL statement

This error seems weird, does it mean that Postgres is finding a conflict/clash between the test.item.id column and the id column from the returned table?! How come? This doesn't make any sense.

I cannot believe this but I see no other id usages here.

Note that if I comment out just this part.

-- WHERE
-- id = 44;

then suddenly the function works fine.

So it seems Postgres is confusing the id in the where clause with something else that's named id?!

With what?

This is totally illogical and counter-intuitive.

Could someone please explain?

Upvotes: 0

Views: 3193

Answers (2)

Pavel Stehule
Pavel Stehule

Reputation: 45835

The clause FUNCTION fx() RETURNS TABLE(x int, y int, ...) is same like FUNCTION(OUT x int, OUT y int) RETURNS SETOF record. So in your case, there is implicit variable id, although you don't use it explicitly.

PostgreSQL design allows to produce rows without SQL.

CREATE OR REPLACE FUNCTION foo(a int)
RETURNS TABLE(b int, c int) AS $$
BEGIN
  FOR i IN 1..a
  LOOP
    b := i; c := i * 10;
    RETURN NEXT;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

See following document, please.

There is another way to declare a function as returning a set, which is to use the syntax RETURNS TABLE(columns). This is equivalent to using one or more OUT parameters plus marking the function as returning SETOF record (or SETOF a single output parameter's type, as appropriate). This notation is specified in recent versions of the SQL standard, and thus may be more portable than using SETOF.

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 246858

There is a name conflict between the variable id that is defined by the RETURNS TABLE clause and the column of the same name.

Things like that cause problems in all programming languages, only PostgreSQL is nice enough to alert you rather than doing something that may not be what you intended.

Qualify the column reference like this to remove the ambiguity:

WHERE test.item.id = 44

Upvotes: 2

Related Questions