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;
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;
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
