Postgres 15

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 table1(line int, a  int, b  int, c  int, d  int, e  int, f  int, g  int, h  int);
insert into table1 values
(1,18,2,2,22,0,2,1,2),
(2,20,2,2,2,0,0,0,2),
(3,10,2,2,222,0,2,1,2),
(4,12,2,2,3,0,0,0,0),
(5,15,2,2,3,0,0,0,0)
  returning *;

CREATE TABLE




line
a
b
c
d
e
f
g
h


1
18
2
2
22
0
2
1
2

2
20
2
2
2
0
0
0
2

3
10
2
2
222
0
2
1
2

4
12
2
2
3
0
0
0
0

5
15
2
2
3
0
0
0
0



INSERT 0 5


create table table2(line int, criteria text);
insert into table2 values
(1,'a,b'),
(2,'a,c,f,h'),
(3,'a,b,e,g,h'),
(4,'c,e')
  returning *;

CREATE TABLE




line
criteria


1
a,b

2
a,c,f,h

3
a,b,e,g,h

4
c,e



INSERT 0 4


CREATE OR REPLACE FUNCTION f_concat_cols(_cols text, OUT result text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT concat(%s) FROM table1', _cols)
   INTO result;
END
$func$;

CREATE FUNCTION


SELECT line, f_concat_cols(criteria) AS result
FROM   table2;


line
result


1
182

2
18222

3
182012

4
20



SELECT 4