Skip to content

Instantly share code, notes, and snippets.

Created August 16, 2014 01:36
Show Gist options
  • Save joevandyk/846222a5b76287662786 to your computer and use it in GitHub Desktop.
Save joevandyk/846222a5b76287662786 to your computer and use it in GitHub Desktop.
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 $$
v_query database_reports.queries;
v_count int;
v_start timestamptz := clock_timestamp();
v_result_id bigint;
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;
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;
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment