We need a view to use from metabase

[timlinux]

create view vw_indicators_by_geometry as select 
    rir_data_indicator.id, 
    rir_data_indicator.name, 
    rir_data_indicator.description, 
    rir_data_indicatorvalue.value,
    rir_data_geometry.name as geography_name,
    rir_data_geometrylevelname.name as level_name
from rir_data_indicator, rir_data_indicatorvalue, rir_data_geometry, rir_data_geometrylevelname
where 
    rir_data_indicatorvalue.indicator_id = rir_data_indicator.id
    and
    rir_data_indicatorvalue.geometry_id = rir_data_geometry.id
    and
    rir_data_geometry.geometry_level_id = rir_data_geometrylevelname.id;

[timlinux]

Please create it during the orchestration of the site so it is automatically available.

[timlinux]

@meomancer I did some more playing around and we can do like this:

-- Copyright © 2015, Hannes Landeholm <[email protected]>
-- This Source Code Form is subject to the terms of the Mozilla Public
-- License, v. 2.0. If a copy of the MPL was not distributed with this
-- file, You can obtain one at http://mozilla.org/MPL/2.0/.

-- See the README.md file distributed with this project for documentation.
create or replace function colpivot(
    out_table varchar, in_query varchar,
    key_cols varchar[], class_cols varchar[],
    value_e varchar, col_order varchar
) returns void as $$
    declare
        in_table varchar;
        col varchar;
        ali varchar;
        on_e varchar;
        i integer;
        rec record;
        query varchar;
        -- This is actually an array of arrays but postgres does not support an array of arrays type so we flatten it.
        -- We could theoretically use the matrix feature but it's extremly cancerogenous and we would have to involve
        -- custom aggrigates. For most intents and purposes postgres does not have a multi-dimensional array type.
        clsc_cols text[] := array[]::text[];
        n_clsc_cols integer;
        n_class_cols integer;
    begin
        in_table := quote_ident('__' || out_table || '_in');
        execute ('create temp table ' || in_table || ' on commit drop as ' || in_query);
        -- get ordered unique columns (column combinations)
        query := 'select array[';
        i := 0;
        foreach col in array class_cols loop
            if i > 0 then
                query := query || ', ';
            end if;
            query := query || 'quote_literal(' || quote_ident(col) || ')';
            i := i + 1;
        end loop;
        query := query || '] x from ' || in_table;
        for j in 1..2 loop
            if j = 1 then
                query := query || ' group by ';
            else
                query := query || ' order by ';
                if col_order is not null then
                    query := query || col_order || ' ';
                    exit;
                end if;
            end if;
            i := 0;
            foreach col in array class_cols loop
                if i > 0 then
                    query := query || ', ';
                end if;
                query := query || quote_ident(col);
                i := i + 1;
            end loop;
        end loop;
        -- raise notice '%', query;
        for rec in
            execute query
        loop
            clsc_cols := array_cat(clsc_cols, rec.x);
        end loop;
        n_class_cols := array_length(class_cols, 1);
        n_clsc_cols := array_length(clsc_cols, 1) / n_class_cols;
        -- build target query
        query := 'select ';
        i := 0;
        foreach col in array key_cols loop
            if i > 0 then
                query := query || ', ';
            end if;
            query := query || '_key.' || quote_ident(col) || ' ';
            i := i + 1;
        end loop;
        for j in 1..n_clsc_cols loop
            query := query || ', ';
            col := '';
            for k in 1..n_class_cols loop
                if k > 1 then
                    col := col || ', ';
                end if;
                col := col || clsc_cols[(j - 1) * n_class_cols + k];
            end loop;
            ali := '_clsc_' || j::text;
            query := query || '(' || replace(value_e, '#', ali) || ')' || ' as ' || quote_ident(col) || ' ';
        end loop;
        query := query || ' from (select distinct ';
        i := 0;
        foreach col in array key_cols loop
            if i > 0 then
                query := query || ', ';
            end if;
            query := query || quote_ident(col) || ' ';
            i := i + 1;
        end loop;
        query := query || ' from ' || in_table || ') _key ';
        for j in 1..n_clsc_cols loop
            ali := '_clsc_' || j::text;
            on_e := '';
            i := 0;
            foreach col in array key_cols loop
                if i > 0 then
                    on_e := on_e || ' and ';
                end if;
                on_e := on_e || ali || '.' || quote_ident(col) || ' = _key.' || quote_ident(col) || ' ';
                i := i + 1;
            end loop;
            for k in 1..n_class_cols loop
                on_e := on_e || ' and ';
                on_e := on_e || ali || '.' || quote_ident(class_cols[k]) || ' = ' || clsc_cols[(j - 1) * n_class_cols + k];
            end loop;
            query := query || 'left join ' || in_table || ' as ' || ali || ' on ' || on_e || ' ';
        end loop;
        -- raise notice '%', query;
        execute ('create temp table ' || quote_ident(out_table) || ' on commit drop as ' || query);
        -- cleanup temporary in_table before we return
        execute ('drop table ' || in_table)
        return;
    end;
$$ language plpgsql volatile;



drop view vw_indicators_by_geometry;
create view vw_indicators_by_geometry as select 
    rir_data_indicator.id, 
    rir_data_indicator.name, 
    rir_data_indicator.description, 
    rir_data_indicatorvalue.value,
	rir_data_indicatorvalue.date,
    rir_data_geometry.name as geography_name,
    rir_data_geometrylevelname.name as level_name
from rir_data_indicator, rir_data_indicatorvalue, rir_data_geometry, rir_data_geometrylevelname
where 
    rir_data_indicatorvalue.indicator_id = rir_data_indicator.id
    and
    rir_data_indicatorvalue.geometry_id = rir_data_geometry.id
    and
    rir_data_geometry.geometry_level_id = rir_data_geometrylevelname.id;
	

-- see documentation at https://github.com/hnsl/colpivot#parameter-reference
select colpivot('_indicators_pivot', 'select * from vw_indicators_by_geometry',
    array['id', 'geography_name', 'level_name', 'date'], array['name'], '#.value', null);
select * from _indicators_pivot;

Only problem is the pivot creates a temporary table so it works if you execute the pivot and the select in a single transaction e.g.

But not if you later try to select from the temporary table (since it is deleted when the transaction ends:

[timlinux]

Another approach is to do it like this

But then we need to manage deleting and recreating the table.