Postgres 14

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?.

select version();


version


PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


begin;
create temp table tbl(a bigserial, data jsonb);
insert into tbl(data) values
('{"example":"Hello world"}'),
('{"example":"Hello world"}'),
('{"example":"Hello world"}'),
('{"example":"Hello world"}'),
('{"example":"Hello world1"}');
commit ;
5 rows affected

select row_to_json(cte.*) from
(select
   tbl.data->>'example' as response,
   count(tbl.data->>'example') as total
from  tbl
group by 1
order by total desc
limit 1) cte;


row_to_json


{"response":"Hello world","total":4}


create or replace  function find_answer(_key text)
returns json as $$
    declare is_exists boolean;
        _sql text;
        _return json;
    begin
        _sql := $sql$ select row_to_json(cte.*) from
                    (select
                       tbl.data->> $1 as response,
                       count(tbl.data->>$1) as total
                    from  tbl
                    group by 1
                    order by total desc
                    limit 1) cte
                $sql$;

        select (data[_key] is null) into is_exists from tbl;
        if is_exists then raise exception '% not exists.',_key;
        else
            raise notice '% sql', _sql;
              execute _sql using _key into _return;
              return _return;
        end if;
    end
$$ language plpgsql;
select * from find_answer('example');


find_answer


{"response":"Hello world","total":4}