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?
I have a function in pgsql
CREATE OR REPLACE FUNCTION core.date_bs_from_ad(date_in_ad date)
RETURNS character varying AS
$$
BEGIN
RETURN(
SELECT date_in_bs FROM core.date_conversion
WHERE date_in_ad = $1
);
END
$$
LANGUAGE plpgsql;
It is created with no errors, but when i use this function it through following error:
ERROR: column reference "date_in_ad" is ambiguous
LINE 3: WHERE date_in_ad = $1
^
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: SELECT (
SELECT MAX(date_in_bs) FROM core.date_conversion
WHERE date_in_ad = $1
)
CONTEXT: PL/pgSQL function core.date_bs_from_ad(date) line 3 at RETURN
********** Error **********
ERROR: column reference "date_in_ad" is ambiguous
SQL state: 42702
Detail: It could refer to either a PL/pgSQL variable or a table column.
Context: PL/pgSQL function core.date_bs_from_ad(date) line 3 at RETURN
asked Feb 9, 2014 at 17:18
3
In cases like these, where the code is simple straightforward enough, sometimes it is useful to rely on one of these special plpgsql commands at the start of the function text:
#variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column
In this case, it would be used as follows:
CREATE OR REPLACE FUNCTION core.date_bs_from_ad(date_in_ad date)
RETURNS character varying AS
$$
#variable_conflict use_column
BEGIN
RETURN(
SELECT date_in_bs FROM core.date_conversion
WHERE date_in_ad = $1
);
END
$$
This is especially useful for cases when the clash is not with the parameters, but rather with the output column names, such as this:
CREATE OR REPLACE FUNCTION core.date_bs_from_ad(p_date_in_ad date)
RETURNS TABLE (date_in_bs character varying) AS
$$
BEGIN
RETURN QUERY
SELECT date_in_bs FROM core.date_conversion
WHERE date_in_ad = p_date_in_ad;
END;
$$
The function above will fail because it the compiler cannot decide if date_in_bs is the output variable name or one of core.date_conversion‘s columns. For problems like these, the command #variable_conflict use_column can really help.
answered Feb 8, 2017 at 23:33
Ezequiel TolnayEzequiel Tolnay
4,3221 gold badge19 silver badges28 bronze badges
There is a collision between SQL identifier and PlpgSQL variable. There are no clean, what do you want. You wrote a predicate, that is TRUE always.
Good to use:
- prefix (usually «_») for local variables
- qualified names in embedded SQL — like table_name.column_name
so both techniques (only one is necessary)
CREATE OR REPLACE FUNCTION core.date_bs_from_ad(_date_in_ad date)
RETURNS character varying AS $$
BEGIN
RETURN SELECT dc.date_in_bs
FROM core.date_conversion dc
WHERE dc.date_in_ad = _date_in_ad;
END
$$ LANGUAGE plpgsql;
For these one line functions is SQL language better:
CREATE OR REPLACE FUNCTION core.date_bs_from_ad(_date_in_ad date)
RETURNS character varying AS $$
SELECT dc.date_in_bs
FROM core.date_conversion dc
WHERE dc.date_in_ad = $1;
$$ LANGUAGE sql;
answered Feb 9, 2014 at 17:29
Pavel StehulePavel Stehule
42.4k5 gold badges91 silver badges94 bronze badges
3
This works:
CREATE OR REPLACE FUNCTION sql_fun()
RETURNS TABLE (id UUID) AS $$
INSERT INTO foo DEFAULT VALUES
RETURNING id
$$ LANGUAGE SQL;
SELECT *
FROM sql_fun();
This doesn’t:
CREATE OR REPLACE FUNCTION plpgsql_fun()
RETURNS TABLE (id UUID) AS $$
BEGIN
RETURN QUERY
INSERT INTO foo DEFAULT VALUES
RETURNING id;
END
$$ LANGUAGE PLpgSQL;
SELECT *
FROM plpgsql_fun();
Is this a bug in PL/pgSQL? How can I fix this while keeping the return type as is?
asked Jun 9, 2020 at 16:13
0
The solution is to qualify all columns from RETURNING which have the same name as the columns from RETURNS TABLE with the name of the table which was INSERTED INTO:
CREATE OR REPLACE FUNCTION plpgsql_fun()
RETURNS TABLE (id UUID) AS $$
BEGIN
RETURN QUERY
INSERT INTO foo DEFAULT VALUES
RETURNING foo.id;
END
$$ LANGUAGE PLpgSQL;
If the name of the table is long and there are multiple columns, the name can be aliased:
CREATE OR REPLACE FUNCTION plpgsql_fun()
RETURNS TABLE (id UUID, foo INT, bar INT) AS $$
BEGIN
RETURN QUERY
INSERT INTO table_with_a_very_long_name AS x DEFAULT VALUES
RETURNING x.id, x.foo, x.bar;
END
$$ LANGUAGE PLpgSQL;
answered Jun 9, 2020 at 17:08
xehpukxehpuk
2674 silver badges11 bronze badges
1
хочу заполнить пустое поле таблицы 1 значениями, которые нахожу по коду из таблицы 1, в таблице 2
CREATE OR REPLACE FUNCTION public.add_soate(
)
RETURNS SETOF zagsmarriagelist
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
DECLARE
r zagsmarriagelist%rowtype;
DECLARE code varchar;
BEGIN
FOR r IN
SELECT id FROM zagsmarriagelist
LOOP
code := (select nullif(regexp_replace(r::varchar, '\D','','g'), ''));
UPDATE zagsmarriagelist
SET bridesoate = (select substring(a.code from 1 for 14) from ate_history a where a.ate::varchar=(select bridebirthaddress from zagsmarriagelist where id::varchar=code))
WHERE id::varchar=code;
RETURN NEXT r;
END LOOP;
RETURN;
END
$BODY$;
ALTER FUNCTION public.add_soate()
OWNER TO postgres;
select * from add_soate();
ошибка: ERROR: ОШИБКА: неоднозначная ссылка на столбец «code»
LINE 2: …ess from zagsmarriagelist z where z.id::varchar = code)) as …
^
DETAIL: Подразумевается ссылка на переменную PL/pgSQL или столбец таблицы.
QUERY: UPDATE zagsmarriagelist
SET bridesoate = (case when (select z.bridebirthaddress from zagsmarriagelist z where z.id::varchar = code) != » then cast((select substring(a.code from 1 for 14) from ate_history a where a.ate::varchar=(select z.bridebirthaddress from zagsmarriagelist z where z.id::varchar = code)) as integer) else NULL END),
groomsoate = (case when (select z.groombirthaddress from zagsmarriagelist z where z.id::varchar = code) != » then cast((select substring(a.code from 1 for 14) from ate_history a where a.ate::varchar=(select z.bridebirthaddress from zagsmarriagelist z where z.id::varchar = code)) as integer) else NULL END)
WHERE zagsmarriagelist.id::varchar=code
CONTEXT: функция PL/pgSQL add_soate(), строка 13, оператор SQL-оператор
SQL state: 42702
почему он не опознает переменную ‘code’ в подзапросе?
ERROR: column reference is ambiguous
You may encounter an error message like following.
ERROR: column reference "z" is ambiguous at character 8
STATEMENT: SELECT z FROM x, y
This error occurs when the referenced column can’t be resolved unambiguously. This may occur when you have two tables that have columns with the same name.
In the example above, you would resolve this by adding an explicit table name like this:
Solution:
Fix your query to include the specific table or view that the column belongs to.