CODEWITHSUNDEEP
postgresql
nimgwfc
nimgwfc

Reputation: 1509

Column reference ambiguous but it is actually a function parameter

I'm getting the following error and can't seem to find a resolution beyond changing the argument names which I would rather not do:

ERROR:  column reference "pcode" is ambiguous
LINE 2:         where lower(n.pcode) = lower(pCode)

pCode is an argument for the plpgsql function and n.pcode is a column from the table with the alias of n.

It looks something like this:

create or replace function func_name(
    pCode text,
    other_params....
)
returns json as
$$
declare
q text;
x bigint;
begin

    if pCode is not null and param_x is not null then
        select count(*) from n_table n
        where lower(n.pcode) = lower(pCode)
        and lower(n.param_x) = lower(param_x)
        into x;
        RAISE NOTICE 'returned amount: %', x;
    end if;

etc, etc

The above will work if I change pCode to say pCode123 but this isn't an ideal solution for me. Is there any way that I can explicitly tell it that lower(pCode) references that function parameter and not the column?

I will have the same issue with param_x which is spelt exactly the same as the column name.

Upvotes: 1

Views: 385

Answers (1)

user330315
user330315

Reputation:

You have a column named pcode and a parameter named pcode a reference to just pcode is thus ambiguous.

Most people just choose some prefix for their arguments to avoid this situation (e.g. arg_pcode or p_pcode). If you don't want to change your parameter pcode to something different, you can fully qualify it with the function's name:

lower(func_name.pcode)

Upvotes: 4

Related Questions