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 split_clm
(
id integer PRIMARY KEY,
name text,
hobby text
);
INSERT INTO split_clm (id, name, hobby)
VALUES
(1, 'Rene', 'Python, Monkey Bars'),
(2, 'CJ', 'Trading, Python'),
(3, 'Herlinda', 'Fashion'),
(4, 'DJ', 'Consulting, Sales'),
(5, 'Martha', 'Social Media, Teaching'),
(6, 'Doug', 'Leadership, Management'),
(7, 'Mathew', 'Finance, Emp Engagement'),
(8, 'Meyers', 'Sleeping, Coding, CrossFit'),
(9, 'Mike', 'YouTube, Athletics'),
(10, 'Peter', 'Eat, Sleep, Python'),
(11, 'Thomas', 'Read, Trading, Sales');
INSERT 0 11
create function create_clm_view()
returns void
as
$$
declare
l_sql text;
l_columns text;
l_hobby_name text;
begin
l_sql := 'select name, ';
select string_agg(format('%L = any(hobbies) as %I', x.hobby_name, x.hobby_name), ', ' order by hobby_name)
into l_columns
from (
select distinct t.hobby_name
from split_clm s
cross join unnest(regexp_split_to_array(s.hobby, '\s*,\s*')) as t(hobby_name)
) x;
l_sql := l_sql || l_columns ||
$sql$ from ( select name, regexp_split_to_array(hobby, '\s*,\s*') as hobbies from split_clm ) t $sql$ ;
execute 'drop view if exists clm_view cascade';
execute 'create view clm_view as '||l_sql;
end;
$$
language plpgsql;
CREATE FUNCTION
select create_clm_view()
| create_clm_view |
|---|
SELECT 1
select *
from clm_view;
| name | Athletics | Coding | Consulting | CrossFit | Eat | Emp Engagement | Fashion | Finance | Leadership | Management | Monkey Bars | Python | Read | Sales | Sleep | Sleeping | Social Media | Teaching | Trading | YouTube |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Rene | f | f | f | f | f | f | f | f | f | f | t | t | f | f | f | f | f | f | f | f |
| CJ | f | f | f | f | f | f | f | f | f | f | f | t | f | f | f | f | f | f | t | f |
| Herlinda | f | f | f | f | f | f | t | f | f | f | f | f | f | f | f | f | f | f | f | f |
| DJ | f | f | t | f | f | f | f | f | f | f | f | f | f | t | f | f | f | f | f | f |
| Martha | f | f | f | f | f | f | f | f | f | f | f | f | f | f | f | f | t | t | f | f |
| Doug | f | f | f | f | f | f | f | f | t | t | f | f | f | f | f | f | f | f | f | f |
| Mathew | f | f | f | f | f | t | f | t | f | f | f | f | f | f | f | f | f | f | f | f |
| Meyers | f | t | f | t | f | f | f | f | f | f | f | f | f | f | f | t | f | f | f | f |
| Mike | t | f | f | f | f | f | f | f | f | f | f | f | f | f | f | f | f | f | f | t |
| Peter | f | f | f | f | t | f | f | f | f | f | f | t | f | f | t | f | f | f | f | f |
| Thomas | f | f | f | f | f | f | f | f | f | f | f | f | t | t | f | f | f | f | t | f |
SELECT 11
update split_clm
set hobby = hobby||', A new hobby'
where id = 1;
UPDATE 1
select create_clm_view();
| create_clm_view |
|---|
SELECT 1
select *
from clm_view;
| name | A new hobby | Athletics | Coding | Consulting | CrossFit | Eat | Emp Engagement | Fashion | Finance | Leadership | Management | Monkey Bars | Python | Read | Sales | Sleep | Sleeping | Social Media | Teaching | Trading | YouTube |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| CJ | f | f | f | f | f | f | f | f | f | f | f | f | t | f | f | f | f | f | f | t | f |
| Herlinda | f | f | f | f | f | f | f | t | f | f | f | f | f | f | f | f | f | f | f | f | f |
| DJ | f | f | f | t | f | f | f | f | f | f | f | f | f | f | t | f | f | f | f | f | f |
| Martha | f | f | f | f | f | f | f | f | f | f | f | f | f | f | f | f | f | t | t | f | f |
| Doug | f | f | f | f | f | f | f | f | f | t | t | f | f | f | f | f | f | f | f | f | f |
| Mathew | f | f | f | f | f | f | t | f | t | f | f | f | f | f | f | f | f | f | f | f | f |
| Meyers | f | f | t | f | t | f | f | f | f | f | f | f | f | f | f | f | t | f | f | f | f |
| Mike | f | t | f | f | f | f | f | f | f | f | f | f | f | f | f | f | f | f | f | f | t |
| Peter | f | f | f | f | f | t | f | f | f | f | f | f | t | f | f | t | f | f | f | f | f |
| Thomas | f | f | f | f | f | f | f | f | f | f | f | f | f | t | t | f | f | f | f | t | f |
| Rene | t | f | f | f | f | f | f | f | f | f | f | t | t | f | f | f | f | f | f | f | f |
SELECT 11