PostgreSQL pivot using temp view, PostgreSQL

PostgreSQL pivot using temp view

-- Test data
create table t(datetime timestamptz, entity char(1), result bool);
insert into t values
  ('2016-01-01 11:00:01', 'a', true),
  ('2016-01-01 17:00:01', 'a', true),  -- two events for :a on same day
  ('2016-01-01 11:01:01', 'b', false),
  ('2016-01-01 11:03:01', 'c', true),
  ('2016-01-01 13:00:01', 'd', true),  -- only one event for :d
  ('2016-01-02 11:00:01', 'a', true),
  ('2016-01-02 11:01:01', 'b', false),
  ('2016-01-02 11:03:01', 'c', true);

do $$ -- Here we will create the view to select desired data
declare
  select_clause text := 'entity';
  dates date[];
  d date;
  date_filter text;
begin
  -- Generate array with dates for our columns
  select array_agg(dt) into dates
  from generate_series((select min(datetime) from t)::date, (select max(datetime) from t)::date, '1 day') as dt;
  raise info '%', dates;

-- Generate "select part"
  foreach d in array dates loop
    date_filter := format('datetime::date = %L', d);
    raise notice '%', date_filter;
    select_clause :=
      select_clause ||
      ', array[count(*) filter(where ' || date_filter ||
      '), count(*) filter(where ' || date_filter ||
      ' and result), count(*) filter(where ' || date_filter ||
      ' and not result)] as ' ||
      quote_ident(d::text);
  end loop;
  raise info '%', select_clause;

-- Create temporary view using previousely generated "select part"
  -- "temp view" is session-wide
  execute 'create or replace temp view v as select ' || select_clause || ' from t group by entity';
end $$;

select * from v order by entity;


run \| edit \| history \| help	0

Please log in to post a comment.