使用了不同的方法，这里已经提到了一些方法，例如交叉表。另外，您可以构建自己的函数，该函数可以动态构建查询并以TABLE和其他几种方法返回。
但是，所有这些都要求您预定义输出及其数据类型的确切数量。
如果我了解您的情况，那么您就不会像您提到的那样：
如果现在我们需要每天开始踢设备，则需要更新查询。
使用交叉表和其他方式的缺点几乎相同。
因此，有一种使用Cursors的方法。这可能不是最好的方法，如果可以使用
crosstab，那可能更好。
但是至少这是我将在代码中添加注释的选项。
解决方案：
-- Function for opening cursorCREATE OR REPLACeFUNCTION 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 REFCURSORLANGUAGE PLPGSQLAS$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)
正如我所提到的，这不是完美的方法，但是它确实可以完成工作：)
