Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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?

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

Thanks beforehand!

>Solution :

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.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading