Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create function get_my_records(a1 text, a2 text) returns setof jsonb language sql as $f$
select * from (values('{ "a": 1, "b": 3.14 }'::jsonb)
                    ,('{ "a": 2, "b": 2.71 }'::jsonb)) as a(b) $f$;

CREATE FUNCTION

select (j->>'a')::int as a, 
       (j->>'b')::double precision as b 
from get_my_records('some','arg') as jdata(j);

a
b

1
3.14

2
2.71

SELECT 2

--You can't have multiple `from` lists
SELECT * 
FROM jsonb_to_record(val) as x(a int,b double precision)
FROM get_my_records( 'some', 'arg' ) AS val;

ERROR:  syntax error at or near "FROM"
LINE 4: FROM get_my_records( 'some', 'arg' ) AS val;
        ^

--You can extend your `from` list by joining
SELECT x.* 
FROM get_my_records('some','arg') AS val 
LEFT JOIN LATERAL jsonb_to_record(val) as x(a int,b double precision) ON TRUE;

a
b

1
3.14

2
2.71

SELECT 2

--You can join implicitly, since `lateral` is implied by dependence between sources
--and on true just lets them join freely
SELECT x.* 
FROM get_my_records('some','arg') AS val,
     jsonb_to_record(val) as x(a int,b double precision);

a
b

1
3.14

2
2.71

SELECT 2