1

I am getting the following error in PostgreSQL:

[42702] ERROR: column reference "topicid" is ambiguous Detail: It could refer to either a PL/pgSQL variable or a table column. Where: PL/pgSQL function topics(integer) line 3 at RETURN QUERY

I understand that to mean there is a parameter and a table column with the same name. Except that I cannot see where it is because the only parameter I have is _typeid and not topicid.

Where is the problem exactly in this function:

CREATE FUNCTION topics(_typeid integer DEFAULT NULL::integer)
  RETURNS TABLE(topicid integer, typeid integer, topic character varying)
  LANGUAGE plpgsql
AS
$$
BEGIN
RETURN QUERY SELECT
    topicid
     ,typeid
     ,topic
FROM
    topic
WHERE
    _typeid IS NULL
   OR
        typeID = _typeid
ORDER BY
    topic ASC;
END
$$;

If I refactor it to just use sql then it works fine like such:

CREATE FUNCTION topics(_typeid integer DEFAULT NULL::integer)
  RETURNS TABLE(topicid integer, typeid integer, topic character varying)
  LANGUAGE sql
AS
$$
SELECT
    topicid
     ,typeid
     ,topic
FROM
    topic
WHERE
    _typeid IS NULL
   OR
        typeID = _typeid
ORDER BY
    topic ASC;
$$;
volume one
  • 6,800
  • 13
  • 67
  • 146
  • Duplicate of [It could refer to either a PL/pgSQL variable or a table column](https://stackoverflow.com/questions/21662295/it-could-refer-to-either-a-pl-pgsql-variable-or-a-table-column). – cstork Nov 10 '21 at 13:43
  • Does this answer your question? [Postgresql column reference "id" is ambiguous](https://stackoverflow.com/questions/9821121/postgresql-column-reference-id-is-ambiguous) – TylerH Oct 20 '22 at 13:38
  • @TylerH No that is a different question. This has already been answered below by Adrian Klaver. – volume one Oct 20 '22 at 15:09
  • It is literally the same problem and solution. – TylerH Oct 20 '22 at 15:43

1 Answers1

1

The conflict is between the variables in RETURNS TABLE and the field names returned from the query. Table qualify the field names in the query e.g. topic.topicid, topic.typid, topic.topic.

Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
  • Why does it not have any problem when the language is plain `sql`? – volume one Jan 30 '21 at 23:33
  • 1
    The short answer is that the `plpgsql` parser is stricter. The longer answer can be found here [Implementation](https://www.postgresql.org/docs/12/plpgsql-implementation.html). This section also includes ways to override the behavior in various ways. I found it easier just to get used to making the columns and variables unambiguous to begin with. – Adrian Klaver Jan 30 '21 at 23:46