How to return columns with the same name as original table when creating function?

So I have this function:

create function report (varchar)
returns table (
        id bigint,
        src_data_system_id int8
language plpgsql
as $$
    return query
        select id, src_data_system_id from process_log_objects plo 
        where src_data_system_name = 'somecondition'||$1;
end $$

If in returns block I am using the same column names as in select query in a function body, then when I do select * from report('dict_day'); I am getting an error

 ERROR: column reference "id" is ambiguous
  Detail: It could refer to either a PL/pgSQL variable or a table column.

How can I return original column names from this query?

Thanks beforehand!

>Solution :

You’d have to qualify the columns with the table name:

    SELECT, plo.src_data_system_id FROM process_log_objects plo

To avoid that kind of problem, it is good style to use different parameter names for the function.

Leave a Reply