Postgres 9.6

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 FUNCTION IF EXISTS    ithelp_20190312_fun1();
DROP FUNCTION IF EXISTS    ithelp_20190312_fun2(r int);
DROP FUNCTION IF EXISTS    ithelp_20190312_fun3(p int);
DROP TABLE IF EXISTS   ithelp_20190312_t0;
CREATE TABLE           ithelp_20190312_t0(PERS_COD VARCHAR,PERS_NAME VARCHAR,ATTND_DAT DATE,ON_TIME TIME);
INSERT INTO            ithelp_20190312_t0
VALUES
('A001','王大明','2019/2/26','09:09'),
('A001','王大明','2019/2/26','15:31'),
('A001','王大明','2019/2/26','16:20'),
('A001','王大明','2019/2/26','20:32'),
('A001','張小馬','2019/2/26','09:02'),
('A001','張小馬','2019/2/26','19:37')
;
select * from ithelp_20190312_t0
6 rows affected



pers_cod
pers_name
attnd_dat
on_time


A001
王大明
2019-02-26
09:09:00

A001
王大明
2019-02-26
15:31:00

A001
王大明
2019-02-26
16:20:00

A001
王大明
2019-02-26
20:32:00

A001
張小馬
2019-02-26
09:02:00

A001
張小馬
2019-02-26
19:37:00


DROP FUNCTION IF EXISTS    ithelp_20190312_fun1();
CREATE OR REPLACE FUNCTION ithelp_20190312_fun1()
RETURNS INTEGER as
$BODY$
BEGIN
RETURN tag
from (
select MAX(tag) over (PARTITION BY 1) tag
from(
select *,
row_number() over (PARTITION BY pers_name order by on_time) tag
from ithelp_20190312_t0
) A
) B
group by tag
;
END
$BODY$
LANGUAGE 'plpgsql';
select ithelp_20190312_fun1()


ithelp_20190312_fun1


4


DROP FUNCTION IF EXISTS    ithelp_20190312_fun2(s int);
CREATE OR REPLACE FUNCTION ithelp_20190312_fun2(s int)
RETURNS void AS
$BODY$
BEGIN
FOR r IN 1..s
LOOP
EXECUTE 
'DROP TABLE IF EXISTS ithelp_20190312_t'||r||';
CREATE TABLE          ithelp_20190312_t'||r||' AS
select *,
MAX(CASE WHEN '||r||' = time_tag'||r||' THEN on_time ELSE null END) OVER (PARTITION BY pers_name) 時間'||r||'
from(
select *,
row_number() over (PARTITION BY pers_name ORDER BY on_time) time_tag'||r||'
from ithelp_20190312_t'||r-1||') a';

END LOOP;
END
$BODY$
LANGUAGE 'plpgsql';
select ithelp_20190312_fun2(ithelp_20190312_fun1())
;


ithelp_20190312_fun2





DROP FUNCTION IF EXISTS    ithelp_20190312_fun3(p int);
CREATE OR REPLACE FUNCTION ithelp_20190312_fun3(p int)
RETURNS void AS
$BODY$
BEGIN
EXECUTE 
'DROP TABLE IF EXISTS ithelp_20190312_t'||p+1||';
CREATE TABLE          ithelp_20190312_t'||p+1||' AS
SELECT 
pers_cod,
pers_name,
attnd_dat,
時間1,
時間2,
時間3,
時間4

FROM ithelp_20190312_t'||p||'

group by
pers_cod,
pers_name,
attnd_dat,
時間1,
時間2,
時間3,
時間4';
END
$BODY$
LANGUAGE 'plpgsql';
select * from ithelp_20190312_fun3(ithelp_20190312_fun1())
;


ithelp_20190312_fun3





select * from ithelp_20190312_t5


pers_cod
pers_name
attnd_dat
時間1
時間2
時間3
時間4


A001
張小馬
2019-02-26
09:02:00
19:37:00
null
null

A001
王大明
2019-02-26
09:09:00
15:31:00
16:20:00
20:32:00

pers_cod	pers_name	attnd_dat	on_time
A001	王大明	2019-02-26	09:09:00
A001	王大明	2019-02-26	15:31:00
A001	王大明	2019-02-26	16:20:00
A001	王大明	2019-02-26	20:32:00
A001	張小馬	2019-02-26	09:02:00
A001	張小馬	2019-02-26	19:37:00