The amount of cursed SQL that I am writing just to keep it in pure SQL. It would be way faster to just make the query in Python. Anyway… Corap rewrite is coming along nicely.

DO $$
DECLARE
    cols text;
    query text;
BEGIN
    SELECT string_agg(quote_ident(name) || ' text', ', ')
    INTO cols
    FROM (
        SELECT name
        FROM (SELECT DISTINCT name, priority FROM device_analyses) AS o
        ORDER BY priority DESC
    ) AS o;
    
    BEGIN
        EXECUTE 'DROP MATERIALIZED VIEW IF EXISTS device_analysis_summary';
        query := format('
            CREATE MATERIALIZED VIEW device_analysis_summary AS
            SELECT *
            FROM crosstab(
                ''SELECT d.deveui, da.name, da.value
                FROM devices d
                LEFT JOIN device_analyses da ON d.deveui = da.device_id
                ORDER BY d.deveui, da.name'',
                ''SELECT name
                FROM (SELECT DISTINCT name, priority FROM device_analyses) AS o
                ORDER BY priority DESC''
            ) AS ct(deveui text, %s);
        ', cols);
        EXECUTE query;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Error creating materialized view: %', SQLERRM;
            ROLLBACK;
            RETURN;
    END;
END $$;