Queries

SELECT pg_database_size($1)

SELECT name, setting, COALESCE(unit, $1), short_desc, vartype FROM pg_settings WHERE vartype IN ($2, $3, $4) AND name != $5

SELECT 
		  pg_database.datname as datname,
		  tmp.mode as mode,
		  COALESCE(count, $1) as count 
		FROM 
		  (
		    VALUES 
		      ($2), 
		      ($3), 
		      ($4), 
		      ($5), 
		      ($6), 
		      ($7), 
		      ($8), 
		      ($9), 
		      ($10)
		  ) AS tmp(mode)
		  CROSS JOIN pg_database 
		  LEFT JOIN (
		    SELECT 
		      database, 
		      lower(mode) AS mode, 
		      count(*) AS count 
		    FROM 
		      pg_locks 
		    WHERE 
		      database IS NOT NULL 
		    GROUP BY 
		      database, 
		      lower(mode)
		  ) AS tmp2 ON tmp.mode = tmp2.mode 
		  and pg_database.oid = tmp2.database 
		ORDER BY 
		  1

SELECT
		current_database() datname,
		schemaname,
		relname,
		seq_scan,
		seq_tup_read,
		idx_scan,
		idx_tup_fetch,
		n_tup_ins,
		n_tup_upd,
		n_tup_del,
		n_tup_hot_upd,
		n_live_tup,
		n_dead_tup,
		n_mod_since_analyze,
		COALESCE(last_vacuum, $1) as last_vacuum,
		COALESCE(last_autovacuum, $2) as last_autovacuum,
		COALESCE(last_analyze, $3) as last_analyze,
		COALESCE(last_autoanalyze, $4) as last_autoanalyze,
		vacuum_count,
		autovacuum_count,
		analyze_count,
		autoanalyze_count,
		pg_total_relation_size(relid) as total_size
	FROM
		pg_stat_user_tables

SELECT
		current_database() datname,
		schemaname,
		relname,
		heap_blks_read,
		heap_blks_hit,
		idx_blks_read,
		idx_blks_hit,
		toast_blks_read,
		toast_blks_hit,
		tidx_blks_read,
		tidx_blks_hit
	FROM pg_statio_user_tables

SELECT
				pg_database.datname,
				tmp.state,
				tmp2.usename,
				tmp2.application_name,
				COALESCE(count,$1) as count,
				COALESCE(max_tx_duration,$2) as max_tx_duration
			FROM
				(
				  VALUES ($3),
				  		 ($4),
				  		 ($5),
				  		 ($6),
				  		 ($7),
				  		 ($8)
				) AS tmp(state) CROSS JOIN pg_database
			LEFT JOIN
			(
				SELECT
					datname,
					state,
					usename,
					application_name,
					count(*) AS count,
					MAX(EXTRACT($9 FROM now() - xact_start))::float AS max_tx_duration
				FROM pg_stat_activity GROUP BY datname,state,usename,application_name) AS tmp2
				ON tmp.state = tmp2.state AND pg_database.datname = tmp2.datname

WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, ((total_plan_time + total_exec_time) / $2 / $3) AS total_minutes, ((total_plan_time + total_exec_time) / calls) AS average_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $4 AND pg_database.datname = current_database() ) SELECT query, query AS explainable_query, query_hash, query_stats.user, total_minutes, average_time, calls, total_minutes * $5 / (SELECT SUM(total_minutes) FROM query_stats) AS total_percent, (SELECT SUM(total_minutes) FROM query_stats) AS all_queries_total_minutes FROM query_stats ORDER BY "total_minutes" DESC LIMIT $6 /*pghero*/

SELECT
			datid
			,datname
			,numbackends
			,xact_commit
			,xact_rollback
			,blks_read
			,blks_hit
			,tup_returned
			,tup_fetched
			,tup_inserted
			,tup_updated
			,tup_deleted
			,conflicts
			,temp_files
			,temp_bytes
			,deadlocks
			,blk_read_time
			,blk_write_time
			,stats_reset
		FROM pg_stat_database

SELECT
			COUNT(*) AS segments,
			SUM(size) AS size
		FROM pg_ls_waldir()
		WHERE name ~ $1

SELECT *,
				(case pg_is_in_recovery() when $1 then $2 else pg_current_wal_lsn() end) AS pg_current_wal_lsn,
				(case pg_is_in_recovery() when $3 then $4 else pg_wal_lsn_diff(pg_current_wal_lsn(), pg_lsn($5))::float end) AS pg_current_wal_lsn_bytes,
				(case pg_is_in_recovery() when $6 then $7 else pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)::float end) AS pg_wal_lsn_diff
			FROM pg_stat_replication

SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
  t.typname IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40)

SELECT * FROM pg_stat_database_conflicts

SELECT n.nspname AS schema, c.relname AS table, $1 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid)) AS transactions_left FROM pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind = $2 AND ($3 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid))) < $4 ORDER BY 3, 1, 2 /*pghero*/

CREATE EXTENSION IF NOT EXISTS pg_stat_statements

SELECT schemaname AS schema, t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), $1, $2) AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), $3, $4) AS using, indisunique AS unique, indisprimary AS primary, indisvalid AS valid, indexprs::text, indpred::text, pg_get_indexdef(i.indexrelid) AS definition FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid WHERE schemaname IS NOT NULL ORDER BY 1, 2 /*pghero*/

SELECT
		slot_name,
		CASE WHEN pg_is_in_recovery() THEN 
		    pg_last_wal_receive_lsn() - $1
		ELSE 
		    pg_current_wal_lsn() - $2 
		END AS current_wal_lsn,
		COALESCE(confirmed_flush_lsn, $3) - $4,
		active
	FROM pg_replication_slots

SELECT *,
				extract($1 from now() - last_archived_time) AS last_archive_age
			FROM pg_stat_archiver

SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
  t.typelem IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48)

SELECT version()

SET SESSION timezone TO 'UTC'

SELECT t.oid, t.typname
FROM pg_type as t
WHERE t.typname IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)

SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
  t.typtype IN ($1, $2, $3)

SELECT
		checkpoints_timed
		,checkpoints_req
		,checkpoint_write_time
		,checkpoint_sync_time
		,buffers_checkpoint
		,buffers_clean
		,maxwritten_clean
		,buffers_backend
		,buffers_backend_fsync
		,buffers_alloc
		,stats_reset
	FROM pg_stat_bgwriter

SELECT pid, state, application_name AS source, age(NOW(), COALESCE(query_start, xact_start)) AS duration, (wait_event IS NOT NULL) AS waiting, query, COALESCE(query_start, xact_start) AS started_at, EXTRACT($1 FROM NOW() - COALESCE(query_start, xact_start)) * $2 AS duration_ms, usename AS user, backend_type FROM pg_stat_activity WHERE state <> $3 AND pid <> pg_backend_pid() AND datname = current_database() AND NOW() - COALESCE(query_start, xact_start) > interval $4 AND query <> $5 ORDER BY COALESCE(query_start, xact_start) DESC /*pghero*/

SELECT pg_database_size(current_database()) /*pghero*/

SELECT slot_name, database, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
			FROM pg_replication_slots

SELECT pg_database.datname FROM pg_database

SELECT state, COUNT(*) AS connections FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC, 1 /*pghero*/

SELECT n.nspname AS schema, c.relname AS sequence, has_sequence_privilege(c.oid, $1) AS readable FROM pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = $2 AND n.nspname NOT IN ($3, $4) /*pghero*/

-- Register a view on the function for ease of use.
CREATE VIEW pg_stat_statements AS
  SELECT * FROM pg_stat_statements(true)

SELECT
	CASE
		WHEN NOT pg_is_in_recovery() THEN $1
                WHEN pg_last_wal_receive_lsn () = pg_last_wal_replay_lsn () THEN $2
		ELSE GREATEST ($3, EXTRACT($4 FROM (now() - pg_last_xact_replay_timestamp())))
	END AS lag,
	CASE
		WHEN pg_is_in_recovery() THEN $5
		ELSE $6
	END as is_replica

WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, ((total_plan_time + total_exec_time) / $2 / $3) AS total_minutes, ((total_plan_time + total_exec_time) / calls) AS average_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $4 AND pg_database.datname = current_database() ) SELECT query, query AS explainable_query, query_hash, query_stats.user, total_minutes, average_time, calls, total_minutes * $5 / (SELECT SUM(total_minutes) FROM query_stats) AS total_percent, (SELECT SUM(total_minutes) FROM query_stats) AS all_queries_total_minutes FROM query_stats ORDER BY "calls" DESC LIMIT $6 /*pghero*/

DO $$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'ml') THEN CREATE ROLE ml; END IF;
    IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'result') THEN CREATE ROLE result; END IF;
END
$$

SELECT c.relname FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = ANY (current_schemas($1)) AND c.relname = $2 AND c.relkind IN ($3,$4)

/* Now redefine */
CREATE FUNCTION pg_stat_statements(IN showtext boolean,
    OUT userid oid,
    OUT dbid oid,
    OUT toplevel bool,
    OUT queryid bigint,
    OUT query text,
    OUT plans int8,
    OUT total_plan_time float8,
    OUT min_plan_time float8,
    OUT max_plan_time float8,
    OUT mean_plan_time float8,
    OUT stddev_plan_time float8,
    OUT calls int8,
    OUT total_exec_time float8,
    OUT min_exec_time float8,
    OUT max_exec_time float8,
    OUT mean_exec_time float8,
    OUT stddev_exec_time float8,
    OUT rows int8,
    OUT shared_blks_hit int8,
    OUT shared_blks_read int8,
    OUT shared_blks_dirtied int8,
    OUT shared_blks_written int8,
    OUT local_blks_hit int8,
    OUT local_blks_read int8,
    OUT local_blks_dirtied int8,
    OUT local_blks_written int8,
    OUT temp_blks_read int8,
    OUT temp_blks_written int8,
    OUT blk_read_time float8,
    OUT blk_write_time float8,
    OUT wal_records int8,
    OUT wal_fpi int8,
    OUT wal_bytes numeric
)
RETURNS SETOF record
AS '$libdir/pg_stat_statements', 'pg_stat_statements_1_9'
LANGUAGE C STRICT VOLATILE PARALLEL SAFE

WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, ((total_plan_time + total_exec_time) / $2 / $3) AS total_minutes, ((total_plan_time + total_exec_time) / calls) AS average_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $4 AND pg_database.datname = current_database() ) SELECT query, query AS explainable_query, query_hash, query_stats.user, total_minutes, average_time, calls, total_minutes * $5 / (SELECT SUM(total_minutes) FROM query_stats) AS total_percent, (SELECT SUM(total_minutes) FROM query_stats) AS all_queries_total_minutes FROM query_stats ORDER BY "average_time" DESC LIMIT $6 /*pghero*/

/* contrib/pg_stat_statements/pg_stat_statements--1.4.sql */

-- complain if script is sourced in psql, rather than via CREATE EXTENSION


-- Register functions.
CREATE FUNCTION pg_stat_statements_reset()
RETURNS void
AS '$libdir/pg_stat_statements'
LANGUAGE C PARALLEL SAFE

SELECT nsp.nspname AS schema, rel.relname AS table, con.conname AS name, fnsp.nspname AS referenced_schema, frel.relname AS referenced_table FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid LEFT JOIN pg_catalog.pg_class frel ON frel.oid = con.confrelid LEFT JOIN pg_catalog.pg_namespace nsp ON nsp.oid = con.connamespace LEFT JOIN pg_catalog.pg_namespace fnsp ON fnsp.oid = frel.relnamespace WHERE con.convalidated = $1 /*pghero*/

SELECT pg_stat_activity.pid, datname AS database, usename AS user, application_name AS source, client_addr AS ip, state, ssl FROM pg_stat_activity LEFT JOIN pg_stat_ssl ON pg_stat_activity.pid = pg_stat_ssl.pid ORDER BY pg_stat_activity.pid /*pghero*/

/* Then we can drop them */
DROP VIEW pg_stat_statements

SET client_min_messages TO 'warning'

SELECT schemaname AS schema, relname AS table, indexrelname AS index, pg_relation_size(i.indexrelid) AS size_bytes, idx_scan as index_scans FROM pg_stat_user_indexes ui INNER JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan <= $1 ORDER BY pg_relation_size(i.indexrelid) DESC, relname ASC /*pghero*/

SELECT n.nspname AS schema, c.relname AS relation, CASE c.relkind WHEN $1 THEN $2 WHEN $3 then $4 ELSE $5 END AS type, pg_table_size(c.oid) AS size_bytes FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname NOT IN ($6, $7) AND n.nspname !~ $8 AND c.relkind IN ($9, $10, $11) ORDER BY pg_table_size(c.oid) DESC, 2 ASC /*pghero*/

SELECT schemaname AS schema, tablename AS table, attname AS column, null_frac, n_distinct FROM pg_stats WHERE schemaname = $1 AND tablename IN ($2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18) ORDER BY 1, 2, 3 /*pghero*/

SELECT slot_name, database, active FROM pg_replication_slots /*pghero*/

SELECT schemaname AS schema, relname AS table, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, n_dead_tup AS dead_rows, n_live_tup AS live_rows FROM pg_stat_user_tables ORDER BY 1, 2 /*pghero*/

SELECT nspname AS schema, relname AS table, reltuples::bigint AS estimated_rows, pg_total_relation_size(pg_class.oid) AS size_bytes FROM pg_class INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE relkind = $1 AND nspname = $2 AND relname IN ($3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19) ORDER BY 1, 2 /*pghero*/

SELECT * FROM pg_stat_statements LIMIT $1 /*pghero*/

SELECT pid, state, application_name AS source, age(NOW(), COALESCE(query_start, xact_start)) AS duration, (wait_event IS NOT NULL) AS waiting, query, COALESCE(query_start, xact_start) AS started_at, EXTRACT($1 FROM NOW() - COALESCE(query_start, xact_start)) * $2 AS duration_ms, usename AS user, backend_type FROM pg_stat_activity WHERE state <> $3 AND pid <> pg_backend_pid() AND datname = current_database() ORDER BY COALESCE(query_start, xact_start) DESC /*pghero*/

/* contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql */

-- complain if script is sourced in psql, rather than via ALTER EXTENSION


/* First we have to remove them from the extension */
ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements

GRANT SELECT ON pg_stat_statements TO PUBLIC

SHOW search_path

SELECT n.nspname AS table_schema, c.relname AS table, attname AS column, format_type(a.atttypid, a.atttypmod) AS column_type, pg_get_expr(d.adbin, d.adrelid) AS default_value FROM pg_catalog.pg_attribute a INNER JOIN pg_catalog.pg_class c ON c.oid = a.attrelid INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace INNER JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum) WHERE NOT a.attisdropped AND a.attnum > $1 AND pg_get_expr(d.adbin, d.adrelid) LIKE $2 AND n.nspname NOT LIKE $3 /*pghero*/

CREATE VIEW pg_stat_statements_info AS
  SELECT * FROM pg_stat_statements_info()

SELECT schemaname AS schema, tablename AS table, attname AS column, null_frac, n_distinct FROM pg_stats WHERE schemaname = $1 AND tablename IN ($2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16) ORDER BY 1, 2, 3 /*pghero*/

/* Then we can drop them */
DROP FUNCTION pg_stat_statements_reset()

/* contrib/pg_stat_statements/pg_stat_statements--1.4--1.5.sql */

-- complain if script is sourced in psql, rather than via ALTER EXTENSION


GRANT EXECUTE ON FUNCTION pg_stat_statements_reset() TO pg_read_all_stats

SELECT nspname AS schema, relname AS table, reltuples::bigint AS estimated_rows, pg_total_relation_size(pg_class.oid) AS size_bytes FROM pg_class INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE relkind = $1 AND nspname = $2 AND relname IN ($3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18) ORDER BY 1, 2 /*pghero*/

SELECT schemaname AS schema, tablename AS table, attname AS column, null_frac, n_distinct FROM pg_stats WHERE schemaname = $1 AND tablename IN ($2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17) ORDER BY 1, 2, 3 /*pghero*/

SET standard_conforming_strings = on

CREATE FUNCTION pg_stat_statements(IN showtext boolean,
    OUT userid oid,
    OUT dbid oid,
    OUT queryid bigint,
    OUT query text,
    OUT calls int8,
    OUT total_time float8,
    OUT min_time float8,
    OUT max_time float8,
    OUT mean_time float8,
    OUT stddev_time float8,
    OUT rows int8,
    OUT shared_blks_hit int8,
    OUT shared_blks_read int8,
    OUT shared_blks_dirtied int8,
    OUT shared_blks_written int8,
    OUT local_blks_hit int8,
    OUT local_blks_read int8,
    OUT local_blks_dirtied int8,
    OUT local_blks_written int8,
    OUT temp_blks_read int8,
    OUT temp_blks_written int8,
    OUT blk_read_time float8,
    OUT blk_write_time float8
)
RETURNS SETOF record
AS '$libdir/pg_stat_statements', 'pg_stat_statements_1_3'
LANGUAGE C STRICT VOLATILE PARALLEL SAFE

CREATE ROLE ml

SET intervalstyle = iso_8601

SELECT pid, phase FROM pg_stat_progress_vacuum WHERE datname = current_database() /*pghero*/

/* Now redefine */
CREATE FUNCTION pg_stat_statements_reset(IN userid Oid DEFAULT 0,
	IN dbid Oid DEFAULT 0,
	IN queryid bigint DEFAULT 0
)
RETURNS void
AS '$libdir/pg_stat_statements', 'pg_stat_statements_reset_1_7'
LANGUAGE C STRICT PARALLEL SAFE

/* contrib/pg_stat_statements/pg_stat_statements--1.8--1.9.sql */

-- complain if script is sourced in psql, rather than via ALTER EXTENSION


--- Define pg_stat_statements_info
CREATE FUNCTION pg_stat_statements_info(
    OUT dealloc bigint,
    OUT stats_reset timestamp with time zone
)
RETURNS record
AS '$libdir/pg_stat_statements'
LANGUAGE C STRICT VOLATILE PARALLEL SAFE

/* contrib/pg_stat_statements/pg_stat_statements--1.6--1.7.sql */

-- complain if script is sourced in psql, rather than via ALTER EXTENSION


/* First we have to remove them from the extension */
ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements_reset()

/* Now redefine */
CREATE FUNCTION pg_stat_statements(IN showtext boolean,
    OUT userid oid,
    OUT dbid oid,
    OUT toplevel bool,
    OUT queryid bigint,
    OUT query text,
    OUT plans int8,
    OUT total_plan_time float8,
    OUT min_plan_time float8,
    OUT max_plan_time float8,
    OUT mean_plan_time float8,
    OUT stddev_plan_time float8,
    OUT calls int8,
    OUT total_exec_time float8,
    OUT min_exec_time float8,
    OUT max_exec_time float8,
    OUT mean_exec_time float8,
    OUT stddev_exec_time float8,
    OUT rows int8,
    OUT shared_blks_hit int8,
    OUT shared_blks_read int8,
    OUT shared_blks_dirtied int8,
    OUT shared_blks_written int8,
    OUT local_blks_hit int8,
    OUT local_blks_read int8,
    OUT local_blks_dirtied int8,
    OUT local_blks_written int8,
    OUT temp_blks_read int8,
    OUT temp_blks_written int8,
    OUT blk_read_time float8,
    OUT blk_write_time float8,
    OUT temp_blk_read_time float8,
    OUT temp_blk_write_time float8,
    OUT wal_records int8,
    OUT wal_fpi int8,
    OUT wal_bytes numeric,
    OUT jit_functions int8,
    OUT jit_generation_time float8,
    OUT jit_inlining_count int8,
    OUT jit_inlining_time float8,
    OUT jit_optimization_count int8,
    OUT jit_optimization_time float8,
    OUT jit_emission_count int8,
    OUT jit_emission_time float8
)
RETURNS SETOF record
AS '$libdir/pg_stat_statements', 'pg_stat_statements_1_10'
LANGUAGE C STRICT VOLATILE PARALLEL SAFE

DROP FUNCTION pg_stat_statements(boolean)

/* Now redefine */
CREATE FUNCTION pg_stat_statements(IN showtext boolean,
    OUT userid oid,
    OUT dbid oid,
    OUT queryid bigint,
    OUT query text,
    OUT plans int8,
    OUT total_plan_time float8,
    OUT min_plan_time float8,
    OUT max_plan_time float8,
    OUT mean_plan_time float8,
    OUT stddev_plan_time float8,
    OUT calls int8,
    OUT total_exec_time float8,
    OUT min_exec_time float8,
    OUT max_exec_time float8,
    OUT mean_exec_time float8,
    OUT stddev_exec_time float8,
    OUT rows int8,
    OUT shared_blks_hit int8,
    OUT shared_blks_read int8,
    OUT shared_blks_dirtied int8,
    OUT shared_blks_written int8,
    OUT local_blks_hit int8,
    OUT local_blks_read int8,
    OUT local_blks_dirtied int8,
    OUT local_blks_written int8,
    OUT temp_blks_read int8,
    OUT temp_blks_written int8,
    OUT blk_read_time float8,
    OUT blk_write_time float8,
    OUT wal_records int8,
    OUT wal_fpi int8,
    OUT wal_bytes numeric
)
RETURNS SETOF record
AS '$libdir/pg_stat_statements', 'pg_stat_statements_1_8'
LANGUAGE C STRICT VOLATILE PARALLEL SAFE

SELECT nspname AS schema, relname AS table, reltuples::bigint AS estimated_rows, pg_total_relation_size(pg_class.oid) AS size_bytes FROM pg_class INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE relkind = $1 AND nspname = $2 AND relname IN ($3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17) ORDER BY 1, 2 /*pghero*/

ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements(boolean)

-- Don't want this to be available to non-superusers.
REVOKE ALL ON FUNCTION pg_stat_statements_reset(Oid, Oid, bigint) FROM PUBLIC

GRANT SELECT ON pg_stat_statements_info TO PUBLIC

/* contrib/pg_stat_statements/pg_stat_statements--1.5--1.6.sql */

-- complain if script is sourced in psql, rather than via ALTER EXTENSION


-- Execution is only allowed for superusers, fixing issue with 1.5.
REVOKE EXECUTE ON FUNCTION pg_stat_statements_reset() FROM pg_read_all_stats

-- Don't want this to be available to non-superusers.
REVOKE ALL ON FUNCTION pg_stat_statements_reset() FROM PUBLIC

CREATE ROLE result

NOT EXISTS (SELECT FROM pg_roles WHERE rolname = $1)

SHOW max_connections /*pghero*/

SHOW server_version_num /*pghero*/

SHOW random_page_cost /*pghero*/

SHOW maintenance_work_mem /*pghero*/

SHOW checkpoint_completion_target /*pghero*/

SHOW wal_buffers /*pghero*/

SHOW default_statistics_target /*pghero*/

SHOW huge_pages /*pghero*/

SELECT pg_is_in_recovery() /*pghero*/

SHOW min_wal_size /*pghero*/

SHOW shared_buffers /*pghero*/

SHOW effective_cache_size /*pghero*/

SHOW max_wal_size /*pghero*/

SHOW effective_io_concurrency /*pghero*/

SHOW jit /*pghero*/

SHOW work_mem /*pghero*/

SHOW wal_compression /*pghero*/

SHOW server_version /*pghero*/