Запрос PostgreSQL с динамическим количеством столбцов

Используются различные методы, некоторые из которых уже упоминались здесь, например кросс-таблица. Кроме того, вы можете создать собственную функцию, которая динамически строит запрос и возвращает результат TABLE и еще несколько методов.

Но все требуют, чтобы вы заранее определили точное количество выходов и их типы данных.

Если я понимаю ваш случай, это то, чего вы не хотели бы, как вы упомянули:

Если нам теперь нужно каждый день пинать устройство, нужно обновить запрос.

Это почти тот же недостаток, что и при использовании кросс-таблицы и других способов.

Таким образом, есть способ использовать Курсоры. Вероятно, это не лучший способ, и если вы можете использовать crosstab, то это, вероятно, лучше.
Но, по крайней мере, я добавлю эту опцию с комментариями в код.

Решение:

-- Function for opening cursor
CREATE OR REPLACE
FUNCTION    test_stats(
                c REFCURSOR,    -- cursor name
                sdate date,     -- start date of period wanted (included)
                edate date,     -- end date of period wanted (included)
                gtype text      -- you had in your 'tests' table some group type which I included just in case
            )
RETURNS     REFCURSOR
LANGUAGE    PLPGSQL
AS
$main$
BEGIN
    OPEN    c
    FOR
    -- Following dynamic query building can be
    -- used also if want to go with function that RETURNS TABLE
    EXECUTE format(
            '   SELECT  r.date,
                        %s
                FROM    test_results r
                WHERE   r.date BETWEEN %L AND %L
                GROUP BY 1
            ',
                -- Here we build for each 'name' own statement and 
                -- aggregate together with comma separator to feed
                -- into main query.
                -- P.S. We need to double check result unfortunately
                --      against test_results table once to get pre-filter
                --      for names in specified date range.
                --      With this we eliminate tests that for sure will
                --      not be presented in the range. In given test data
                --      this means eliminating 'hit'.
            (
                SELECT  string_agg(
                            DISTINCT format(
                                '(  SELECT  success
                                    FROM    test_results i
                                    WHERE   i.name = %1$L
                                    AND     i.date = r.date ) AS "%1$s"',
                                t.name
                            ),
                            ','
                        )
                FROM    tests t,
                LATERAL (   SELECT  array_agg( DISTINCT r.name )
                            FROM    test_results r
                            WHERE   r.date BETWEEN sdate AND edate
                        ) a( lst )
                WHERE   t.group = gtype     -- the group type is used here
                AND     t.name = ANY ( a.lst::text[] )
            ),
            sdate,      -- start date for between statement
            edate       -- end date for between statement
        );
    RETURN c;
END;
$main$;

-- Usage example:
BEGIN;
SELECT test_stats( 'teststats1', '2017-06-21'::date, '2017-06-23'::date, 'basic' );
FETCH ALL IN teststats1;
COMMIT;

-- Result (from your given test data set):
    date    | drop | poke | prod
------------+------+------+------
 2017-06-22 |      | t    | f
 2017-06-21 |      | t    | t
 2017-06-23 | t    | t    | t
(3 rows)

Как я уже говорил, это не идеальный способ, но он работает :)

Включите расширение tablefunc, а затем используйте функцию crosstab; см. документы PG: https://www.postgresql.org/docs/current/static/tablefunc.html. Аргументом функции кросс-таблицы должен быть текст запроса, который создает три столбца: дату, имя теста и успех теста в указанном порядке.

Я бы представил такой запрос:

SELECT tr.name, tr.date, tr.success
FROM tests t JOIN
     test_results tr
     ON t.testid = tr.testid
WHERE t.group = 'basic' AND tr.date > now() - '1 week'::interval;

Вам, вероятно, лучше сводить данные на уровне приложения.