'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 $$
begin
    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!

postgresql


Solution 1:[1]

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

RETURN QUERY
    SELECT plo.id, 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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Laurenz Albe

Related Questions