gistfile1.sql

BEGIN;

create table test_table (id serial primary key, name text);
insert into test_table (name) values ('joe'), ('bob');

create schema database_reports;

create table database_reports.queries (
  query_id bigserial primary key,
  parent_id bigint references database_reports.queries,
  name text not null,
  query text not null,
  created_at timestamptz default now(),
  type text not null
);

create index on database_reports.queries(parent_id);

create table database_reports.results (
  result_id bigserial primary key,
  query_id bigint references database_reports.queries not null,
  created_at timestamptz not null default now(),
  duration interval not null,
  data json not null,
  success boolean not null
);

create index on database_reports.results(query_id);

create function run_query(p_query_id bigint) returns bigint as $$
declare
  v_query database_reports.queries;
  v_count int;
  v_start timestamptz := clock_timestamp();
  v_result_id bigint;
begin
  select * into v_query from database_reports.queries where query_id = p_query_id;
  drop table if exists temp_query_results;
  execute 'create temporary table temp_query_results as (' || v_query.query ||')';
  select count(*) into v_count from temp_query_results;
  if v_count < 100 then -- want to keep a reasonable limit on result sizes, change to whatever
    insert into database_reports.results (query_id, duration, data, success)
      select v_query.query_id, clock_timestamp() - v_start, json_agg(temp_query_results), true from temp_query_results
      returning result_id into v_result_id;
    return v_result_id;
  else
    raise exception 'too many results: %', v_count;
  end if;
end $$ language plpgsql;

create function download_result(p_result_id bigint) returns void as $$
declare r text;
 begin
  drop table if exists temp_download_result;
  create temporary table temp_download_result();
  for r in select json_object_keys(json_extract_path(data, '0')) from database_reports.results where result_id = p_result_id loop
    execute 'alter table temp_download_result add column ' || r || ' text';
  end loop;
  insert into temp_download_result select (json_populate_recordset(null::temp_download_result, data)).*
    from database_reports.results where result_id = p_result_id;
end $$ language plpgsql;


insert into database_reports.queries (name, query, type) values ('test', 'select *, true as foo from test_table', 'test');

select run_query(1);
select download_result(1);
select * from database_reports.results where result_id = 1;
copy (select * from temp_download_result) to stdout with csv header;