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

create table users (id int, 	name text, 	information jsonb);
insert into users
VALUES
  (1001, 'Alice', '{"1":"Google","2":"1991-02-08"}'),
  (1002, 'Bob', '{"1":"StackOverflow","3":"www.google.com"}')
;

create table profile_fields (profilefield_id int, value text);
insert into profile_fields
VALUES
  (1, 'Company'),
  (2, 'DateOfBirth'),
  (3, 'ProfileLink')
;


CREATE TABLE



INSERT 0 2



CREATE TABLE



INSERT 0 3


create or replace procedure create_user_info_view()
as
$$
declare
  l_rec record;
  l_sql text;
  l_columns text;
begin
  
  select string_agg(concat('u.information ->> ', quote_literal(profilefield_id), ' as ', quote_ident(value)), ', ')
    into l_columns
  from profile_fields;
  
  execute 'drop view if exists users_view';
  execute 'create view users_view as select u.id, u.name, '||l_columns||' from users u';
end;
$$
language plpgsql;


CREATE PROCEDURE


call create_user_info_view();

CALL


select *
from users_view;


id
name
Company
DateOfBirth
ProfileLink


1001
Alice
Google
1991-02-08
null

1002
Bob
StackOverflow
null
www.google.com



SELECT 2


insert into profile_fields values (4, 'Hobby');

INSERT 0 1


update users 
   set information = information || jsonb_build_object('4', 'Something')
where id = 1001;

UPDATE 1


call create_user_info_view();

CALL


select *
from users_view;


id
name
Company
DateOfBirth
ProfileLink
Hobby


1002
Bob
StackOverflow
null
www.google.com
null

1001
Alice
Google
1991-02-08
null
Something



SELECT 2

id	name	Company	DateOfBirth	ProfileLink
1001	Alice	Google	1991-02-08	null
1002	Bob	StackOverflow	null	www.google.com