Postgres function update column ambiguous

Question

I create a postgres function like this:

CREATE OR REPLACE FUNCTION delete_annotation_f(
    IN cmtid uuid)
    RETURNS bool AS $$
DECLARE
     pid uuid;
     cmt_cnt int4;
BEGIN
    SELECT get_comment_cnt_f(cmtid) INTO cmt_cnt;
    UPDATE detail_t SET ann_cmt_cnt=ann_cmt_cnt - cmt_cnt;
    RETURN TRUE;
END
$$
LANGUAGE plpgsql;

But when I run this function I get this error:

ERROR:  column reference "cmt_cnt" is ambiguous
LINE 1: ...detail_t SET ann_cmt_cnt=ann_cmt_cnt-cmt_cnt WH...

I find this link On Inset: column reference "score" is ambiguous but it could not help me solve the problem. Anyone have solutions?

table detail_t must have column cmt_cnt ?..

Vao Tsun
– Vao Tsun

2018-01-24 13:34:47 +00:00
Commented Jan 24, 2018 at 13:34 — Vao Tsun
– Vao Tsun, Commented Jan 24, 2018 at 13:34

jlandercy · Accepted Answer · 2018-01-24 13:36:26Z

1

You are using a variable that have the same name of a column. The query parser could not decide which it must chose, change your variable name then the ambiguity will vanish.

answered Jan 24, 2018 at 13:36

jlandercy

11.6k3 gold badges48 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vao Tsun · Accepted Answer · 2018-01-24 13:44:23Z

https://www.postgresql.org/docs/current/static/plpgsql-implementation.html

By default, PL/pgSQL will report an error if a name in a SQL statement could refer to either a variable or a table column. You can fix such a problem by renaming the variable or column, or by qualifying the ambiguous reference, or by telling PL/pgSQL which interpretation to prefer. The simplest solution is to rename the variable or column. A common coding rule is to use a different naming convention for PL/pgSQL variables than you use for column names. For example, if you consistently name function variables v_something while none of your column names start with v_, no conflicts will occur.

and further:

You can also set the behavior on a function-by-function basis, by inserting one of these special commands at the start of the function text:
#variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column

Collectives™ on Stack Overflow

Postgres function update column ambiguous

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest