CREATE SCHEMA vividcortex;

CREATE OR REPLACE FUNCTION vividcortex.get_func_version() RETURNS text AS
$$
  SELECT 'PG84-20211012'::text;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION vividcortex.get_processlist() RETURNS
TABLE(c1 text, c2 inet, c3 integer, c4 timestamp with time zone, c5 text,
  c6 timestamp with time zone, c7 text, c8 timestamp with time zone, c9 name,
  c10 boolean, c11 timestamp with time zone, c12 integer, c13 name) AS
$$
  SELECT NULL::text, client_addr, client_port, now() AS now, current_query,
    query_start, NULL::text, NULL::timestamp with time zone, usename, waiting, xact_start, procpid, datname
  FROM pg_stat_activity;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION vividcortex.get_lockmetrics_v2() RETURNS
TABLE(c1 text, c2 boolean, c3 bigint, c4 double precision) AS
$$
  SELECT locktype, granted, COUNT(*) AS cnt,
    SUM(LEAST(EXTRACT(epoch FROM (now()-a.query_start))*1000000, 1000000)) tw
  FROM pg_locks AS l
  JOIN pg_stat_activity AS a ON l.pid = a.procpid
  WHERE now() > a.query_start
  GROUP BY locktype, granted;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION vividcortex.get_blockers(in integer[]) RETURNS
TABLE(c1 integer, c2 bigint) AS
$$
  SELECT l2.pid, COUNT(DISTINCT l.pid)
    FROM pg_locks AS l
    JOIN pg_stat_activity AS a ON a.procpid<>l.pid AND NOT a.waiting AND a.xact_start IS NOT NULL
    JOIN pg_locks AS l2 ON l2.pid=a.procpid AND l2.locktype=l.locktype AND l2.granted AND
      l2.relation IS NOT DISTINCT FROM l.relation AND
      l2.database IS NOT DISTINCT FROM l.database AND
      l2.transactionid IS NOT DISTINCT FROM l.transactionid AND
      l2.virtualxid IS NOT DISTINCT FROM l.virtualxid AND
      l2.page IS NOT DISTINCT FROM l.page AND
      l2.classid IS NOT DISTINCT FROM l.classid AND
      l2.objid IS NOT DISTINCT FROM l.objid AND
      l2.objsubid IS NOT DISTINCT FROM l.objsubid
    WHERE NOT l.granted AND l.locktype IN ('advisory','object','relation','transactionid') AND l.pid=ANY($1)
    GROUP BY l2.pid;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

-- Functions for fetching db/schema/table/index size metrics.
CREATE OR REPLACE FUNCTION vividcortex.get_dbsizes() RETURNS
TABLE(c1 name, c2 bigint) AS
$$
  SELECT datname, pg_database_size(datname) FROM pg_database;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION vividcortex.get_schemasizes() RETURNS
TABLE(c1 name, c2 numeric, c3 numeric, c4 numeric) AS
$$
  SELECT ''::name, 0::numeric,
    0::numeric, 0::numeric
  FROM pg_statio_user_tables
  WHERE FALSE;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION vividcortex.get_tablesizes() RETURNS
TABLE(c1 name, c2 bigint, c3 bigint, c4 bigint) AS
$$
  SELECT relname, 0::bigint, 0::bigint,
    pg_total_relation_size(relid)
  FROM pg_statio_user_tables;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION vividcortex.get_indexsizes() RETURNS
TABLE(c1 name, c2 bigint) AS
$$
  SELECT indexrelname, pg_relation_size(indexrelid) AS index_size
  FROM pg_stat_user_indexes;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

-- RDS, ignore errors (E.g. unknown relation pg_stat_statements) if you do not need off-host monitoring

CREATE OR REPLACE FUNCTION vividcortex.get_stat_query(in text, out c1 text) AS
$$
  SELECT query FROM pg_stat_statements WHERE MD5(query) = $1 LIMIT 1;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION vividcortex.get_stat_statements() RETURNS
TABLE(c1 text, c2 bigint, c3 double precision, c4 bigint, c5 bigint, c6 bigint, c7 bigint,
  c8 bigint, c9 bigint, c10 bigint, c11 bigint, c12 bigint, c13 bigint, c14 bigint,
  c15 double precision, c16 double precision, c17 oid, c18 oid,
  c19 bigint, c20 double precision, c21 numeric) AS
$$
  SELECT MD5(query), calls, 1000 * total_time, rows,
      0::bigint, 0::bigint, 0::bigint, 0::bigint, 0::bigint, 0::bigint, 0::bigint,
      0::bigint, 0::bigint, 0::bigint, 0::double precision, 0::double precision, dbid,
      userid, 0::bigint, 0::double precision, 0::numeric
      FROM pg_stat_statements
      WHERE query IS NOT NULL;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION vividcortex.get_stat_statements_grouped() RETURNS
TABLE(c1 text, c2 numeric, c3 double precision, c4 numeric, c5 numeric, c6 numeric,
  c7 numeric, c8 numeric, c9 numeric, c10 numeric, c11 numeric, c12 numeric, c13 numeric,
  c14 numeric, c15 double precision, c16 double precision, c17 oid, c18 oid,
  c19 bigint, c20 double precision, c21 numeric) AS
$$
  SELECT MD5(query), SUM(calls), 1000 * SUM(total_time), SUM(rows),
      0::numeric, 0::numeric, 0::numeric, 0::numeric, 0::numeric, 0::numeric, 0::numeric,
      0::numeric, 0::numeric, 0::numeric, 0::double precision, 0::double precision, dbid,
      userid, 0::bigint, 0::double precision, 0::numeric
      FROM pg_stat_statements
      WHERE query IS NOT NULL
      GROUP BY MD5(query), dbid, userid;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION vividcortex.get_stat_activity() RETURNS
TABLE(c1 name, c2 name, c3 double precision, c4 double precision, c5 text, c6 text, c7 text,
  c8 integer, c9 inet, c10 integer,
  c11 double precision,
  c12 text, c13 text, c14 text) AS
$$
  SELECT ''::name, NULL::name, 0::double precision, 0::double precision, ''::text,
      ''::text, ''::text, 0::integer, NULL::inet, NULL::integer,
      0::double precision,
      NULL::text, NULL::text, NULL::text;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION vividcortex.qm_warmup() RETURNS
TABLE(c1 text, c2 text) AS
$$
  SELECT MD5(query), query FROM pg_stat_statements WHERE query IS NOT NULL;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;
