Displaying a Postgres function query results in columns

Question

I have a Postgres 12 table with a JSONB column named ‘jxl’. I can query the jxl data and get values displayed in separate columns:

SELECT jxl -> 'JOBFile' -> 'Environment' -> 'AssociatedFiles' -> 'ExportedFiles' ->> 'File' as "CSV File",
jxl -> 'JOBFile' -> 'Environment' -> 'CoordinateSystem' ->> 'DatumName' as "Datum"
FROM survey_gdb.jxl_files WHERE jxl -> 'JOBFile' ->> 'jobName' = '2052-16410';

Result:

What I want to do is input the ‘jobName’ in the where clause to a Postgres function. Here’s what I have tried:

create or replace function survey_gdb.display_jxl (project text) 
    returns table (
        csv_file text,
        datum text
    ) 
as $func$
begin
    return query 
        select
            jxl -> 'JOBFile' -> 'Environment' -> 'AssociatedFiles' -> 'ExportedFiles' ->> 'File' as "CSV File",
            jxl -> 'JOBFile' -> 'Environment' -> 'CoordinateSystem' ->> 'DatumName' as "Datum"
        from survey_gdb.jxl_files
        where jxl -> 'JOBFile' ->> 'jobName' = project;
end
$func$ 
language plpgsql;

However, when I issue a “select survey_gdb.display_jxl('2052-16410')” query, what I get is the following:

How can I see the results from the function identical to the results from the raw query with the hard-coded WHERE condition?

Answer 1

Set returning functions need to be uses like a table: in the FROM clause:

select *
from survey_gdb.display_jxl('2052-16410')