Agfdhyk

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?

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.

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