Postgres 9.4

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

-- /** 
-- begin test data 
-- begin test data 
-- begin test data 
--  */
DROP TABLE IF EXISTS tmpSales ;
CREATE TEMP TABLE tmpSales AS
SELECT
  sale_id
  ,TRUNC(RANDOM()*12)+1 AS book_id
  ,TRUNC(RANDOM()*100)+1 AS customer_id
  ,(date '2010-01-01' + random() * (timestamp '2016-12-31' - timestamp '2010-01-01'))::DATE AS sale_date
FROM generate_series(1,1000) AS sale_id;
 

DROP TABLE IF EXISTS tmp_month_total ; 
CREATE TEMP TABLE tmp_month_total AS 
  SELECT
     date_part( 'year' , sale_date ) AS year
    ,date_part( 'month', sale_date ) AS mn 
    ,to_char(sale_date, 'mon') AS month
    ,COUNT(*) AS total
  FROM tmpSales
  GROUP BY date_part('year', sale_date), to_char(sale_date, 'mon') ,date_part( 'month', sale_date ) 
;

SELECT * FROM tmpSales LIMIT 15 ;
-- /** 
-- END test data 
-- END test data 
-- END test data 
--  */


sale_id
book_id
customer_id
sale_date


1
3
47
2011-11-06

2
5
79
2012-05-16

3
11
78
2010-01-31

4
5
100
2011-07-16

5
4
23
2014-11-01

6
10
54
2012-09-25

7
7
76
2015-11-23

8
12
9
2013-07-25

9
11
18
2011-03-17

10
2
98
2011-07-04

11
5
20
2014-10-12

12
8
56
2013-04-12

13
12
46
2011-09-13

14
12
86
2011-09-13

15
3
12
2013-04-08



SELECT 15


-- /** 
-- dyn. build a temporary row-type (as temp table) based on existing data, not hard-coded
-- dyn. build a temporary row-type (as temp table) based on existing data, not hard-coded
-- dyn. build a temporary row-type (as temp table) based on existing data, not hard-coded
--  **/
DROP TABLE IF EXISTS tmpTblTyp CASCADE ; 
DO LANGUAGE plpgsql $$ DECLARE v_sqlstring VARCHAR  = ''; BEGIN 
v_sqlstring := CONCAT( 'CREATE TEMP TABLE tmpTblTyp AS SELECT '   
                       ,(SELECT  STRING_AGG( CONCAT('NULL::int AS ' , month )::TEXT , ' ,' 
                            ORDER BY mn                        
                           )::TEXT
                           FROM
                           (SELECT DISTINCT month, mn FROM tmp_month_total )a )
                       ,' LIMIT 0 '    
                       ) ; -- RAISE NOTICE '%', v_sqlstring ;  
EXECUTE( v_sqlstring ) ; END $$; 
 

DO


-- /**
-- build the JSON record-set using the dyn. row-type
-- build the JSON record-set using the dyn. row-type
-- build the JSON record-set using the dyn. row-type
--**/
DROP TABLE IF EXISTS tmpMoToJson ;
CREATE TEMP TABLE tmpMoToJson AS
	  SELECT 
	     year AS year
	     ,(json_build_array( months )) AS js_months_arr /** optional **/
	     ,json_populate_recordset ( NULL::tmpTblTyp /** use temp table as a record type!! **/
	                              , json_build_array( months ) 
	                             ) jprs /** builds row-type column that can be expanded with (jprs).*   
                                         **/
	  FROM ( SELECT year
             -- accum data into JSON array
	         ,json_object_agg(month,total) AS months 
			 FROM tmp_month_total
			 GROUP BY year
			 ORDER BY year
			) a
; 



SELECT 7


SELECT  
  year
,(ROW((jprs).*)::tmpTblTyp).* /** explode the composite type row **/
--, js_months_arr /** optional **/
FROM tmpMoToJson ;


year
jan
feb
mar
apr
may
jun
jul
aug
sep
oct
nov
dec


2010
17
9
12
12
9
7
10
13
4
8
9
14

2011
7
11
14
11
10
12
9
14
8
13
14
15

2012
16
13
12
9
14
17
8
11
19
9
15
11

2013
16
10
8
14
13
15
15
4
15
13
11
13

2014
10
15
10
12
11
11
6
8
15
12
15
13

2015
13
7
16
9
12
11
11
15
5
8
23
16

2016
14
12
13
5
16
12
17
14
9
14
17
10



SELECT 7

sale_id	book_id	customer_id	sale_date
1	3	47	2011-11-06
2	5	79	2012-05-16
3	11	78	2010-01-31
4	5	100	2011-07-16
5	4	23	2014-11-01
6	10	54	2012-09-25
7	7	76	2015-11-23
8	12	9	2013-07-25
9	11	18	2011-03-17
10	2	98	2011-07-04
11	5	20	2014-10-12
12	8	56	2013-04-12
13	12	46	2011-09-13
14	12	86	2011-09-13
15	3	12	2013-04-08

year	jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
2010	17	9	12	12	9	7	10	13	4	8	9	14
2011	7	11	14	11	10	12	9	14	8	13	14	15
2012	16	13	12	9	14	17	8	11	19	9	15	11
2013	16	10	8	14	13	15	15	4	15	13	11	13
2014	10	15	10	12	11	11	6	8	15	12	15	13
2015	13	7	16	9	12	11	11	15	5	8	23	16
2016	14	12	13	5	16	12	17	14	9	14	17	10