Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What do I do if parameter of function is the same as a column name [duplicate]

I have an SQL function which has a parameter name as id. However, I have a column name which has the same name, id. How do I tell the function how to distinguish between the parameter and the column. My function works perfectly if I change the paramter name from id to num, but that is not an option here and I cannot change the column name too.

create or replace function
    test(id integer) return text
as $$
    select address
    from customers c
    where c.id = id
$$ language sql;
like image 684
Paul Avatar asked Sep 03 '25 06:09

Paul


2 Answers

Postgres allows you to refer to arguments positionally:

create or replace function
    test(id integer) return text
as $$
    select address
    from customers c
    where c.id = $1
$$ language sql;

I view this as a bad practice and a class should not be encouraging this style. In fact, you should be encouraged to give names to parameters that are less likely to conflict with other identifiers:

create or replace function test (
        in_id integer
) return text
as $$
    select address
    from customers c
    where c.id = in_id
$$ language sql;
like image 93
Gordon Linoff Avatar answered Sep 04 '25 23:09

Gordon Linoff


Does it work if you phrase the query like this?

select address
from (select c.id as cid, c.address from customers c) t
where cid = id
like image 32
GMB Avatar answered Sep 04 '25 21:09

GMB