Postgres 10

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 times AS
SELECT * FROM (
   VALUES
   ('Fred', 8, 2, 3)
 , ('Jane', 8, 1, 0)
 , ('Samm', 8, 0, 6)
 , ('Alex', 8, 0, 0)
) t(name, st, ot, dt);
4 rows affected

SELECT t.name, u.t, u.val
FROM   times t
LEFT   JOIN LATERAL (
   VALUES
    ('st', t.st)
  , ('ot', t.ot)
  , ('dt', t.dt)
   ) u(t, val) ON u.val <> 0
ORDER  BY 1, 2;  -- optional


name
t
val


Alex
st
8

Fred
dt
3

Fred
ot
2

Fred
st
8

Jane
ot
1

Jane
st
8

Samm
dt
6

Samm
st
8


CREATE OR REPLACE FUNCTION f_unpivot_times_columns(VARIADIC _cols text[])
  RETURNS TABLE(name text, tc text, val int) AS
$func$
BEGIN
   RETURN QUERY EXECUTE (
   SELECT
     'SELECT t.name, u.t, u.val
      FROM   times t
      LEFT   JOIN LATERAL (VALUES '
          || string_agg(format('(%L, t.%I)', c, c), ', ')
          || ') u(t, val) ON u.val <> 0
      ORDER  BY 1, 2'  -- optional
   FROM   unnest(_cols) c
   );
END
$func$  LANGUAGE plpgsql;
SELECT * FROM f_unpivot_times_columns(VARIADIC '{st, ot, dt}');


name
tc
val


Alex
st
8

Fred
dt
3

Fred
ot
2

Fred
st
8

Jane
ot
1

Jane
st
8

Samm
dt
6

Samm
st
8


SELECT * FROM f_unpivot_times_columns('ot', 'dt');  -- without "st" Alex has an empty row


name
tc
val


Alex
null
null

Fred
dt
3

Fred
ot
2

Jane
ot
1

Samm
dt
6