Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_with_data (data1 int);

INSERT INTO table_with_data VALUES (1), (2), (3);

CREATE TABLE



INSERT 0 3


-- repaired function
CREATE OR REPLACE FUNCTION public.test1(var int)
  RETURNS TABLE(res int)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT data1 AS res  -- this column alias is just noise (or documentation)
   FROM   table_with_data
   WHERE  data1 < var;      -- !
END
$func$

CREATE FUNCTION


SELECT * FROM public.test1(3);


res


1

2



SELECT 2


-- truly ambiguous reference ...
CREATE OR REPLACE FUNCTION public.test2(var int)
  RETURNS TABLE(data1 int)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT data1 AS res  -- this column alias is just noise (or documentation)
   FROM   table_with_data
   WHERE  data1 < var;      -- !
END
$func$

CREATE FUNCTION


-- leads to error msg!
SELECT * FROM public.test2(3);

ERROR:  column reference "data1" is ambiguous
LINE 1: SELECT data1 AS res  -- this column alias is just noise (or ...
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT data1 AS res  -- this column alias is just noise (or documentation)
   FROM   table_with_data
   WHERE  data1 < var
CONTEXT:  PL/pgSQL function test2(integer) line 3 at RETURN QUERY


-- brute force fix
CREATE OR REPLACE FUNCTION public.test3(var int)
  RETURNS TABLE(data1 int)
  LANGUAGE plpgsql AS
$func$
#variable_conflict use_column             -- to resolve conflicts
BEGIN
   RETURN QUERY
   SELECT data1
   FROM   table_with_data
   WHERE  data1 < var;      -- !
END
$func$

CREATE FUNCTION


SELECT * FROM public.test3(3);


data1


1

2



SELECT 2


-- proper fix
CREATE OR REPLACE FUNCTION public.test4(_var int)
  RETURNS TABLE(res int)
  LANGUAGE plpgsql STABLE AS
$func$
BEGIN
   RETURN QUERY
   SELECT t.data1  -- table-qualify column name
   FROM   table_with_data t
   WHERE  t.data1 < _var;      -- !
END
$func$

CREATE FUNCTION


SELECT * FROM public.test4(3);


res


1

2



SELECT 2