Postgres 13

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

drop table if exists example_table;
create table example_table(
    lbl int, 
    name varchar(32), 
    value varchar(55)
);
    
insert into example_table (lbl, name, value)
values
    (1, 'num', 1),
    (1, 'colour', 'Red'),
    (1, 'percentage', 25.0),
    (2, 'num', 2),
    (2, 'colour', 'Green'),
    (2, 'percentage', 50.0),
    (3, 'num', 3),
    (3, 'colour', 'Blue'),
    (3, 'percentage', 75.0);


DROP TABLE



CREATE TABLE



INSERT 0 9


create or replace function create_pivot_view()
returns void language plpgsql as $$
declare
    list text;
begin
    select string_agg(format('jdata->>%1$L "%1$s"', name), ', ')
    from (
        select distinct name
        from example_table
        ) sub
    into list;

    execute format($f$
        drop view if exists example_pivot_view;
        create view example_pivot_view as
        select lbl, %s
        from (
            select lbl, json_object_agg(name, value) jdata
            from example_table
            group by 1
            order by 1
            ) sub
        $f$, list);
end $$;


CREATE FUNCTION


select create_pivot_view();

select *
from example_pivot_view;



create_pivot_view






SELECT 1




lbl
colour
percentage
num


1
Red
25.0
1

2
Green
50.0
2

3
Blue
75.0
3



SELECT 3