0

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?

1 Answer 1

0

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

select *
from survey_gdb.display_jxl('2052-16410')
1
  • Fantastic, @a_horse_with_no_name! Thank you so much for the prompt (and accurate) response. Dec 29, 2022 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.