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:

enter image description here

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:

enter image description here

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')

• Rudy Stricklan: Fantastic, @a_horse_with_no_name! Thank you so much for the prompt (and accurate) response.