/*

  Create functions and views in order to make monitoring and administration of PostgreSQL a bit easier.
  Right below this comment the destination schema is set. Change this line appropriately if you want
  a different schema name.

  In order to see a nice list of views and functions, execute SELECT * FROM tools.infolist;
  or \dv+ and \df+ while the destination schema is in the search_path.

  This file must be executed by psql in every database where the views are going to be used:
  If you see COMMIT as last output, everything is fine. If you see ROLLBACK as last output,
  something went wrong. Examine the complete output of psql to find the problem.

  © 2018-2020 Holger@Jakobs.com

 */

\set destination_schema tools


BEGIN;

-- Get rid of existing schema if it exists
DROP SCHEMA IF EXISTS :destination_schema CASCADE;

-- GRANT SELECT some_rights TO admins
-- If you want members of the role "admins" to be able to see settings info,
-- although they aren't necessarily superusers, execute these GRANT statements.
-- The "admins" group isn't standard, you have to create it and add members
-- to it. We create it here for your convenience, please add members as needed.
-- If the role "admins" already exists, nothing is being done. Since there is
-- no IF NOT EXISTS in the CREATE ROLE command, we use an anonymous plpgsql
-- block with exception handling.
DO $$
BEGIN
  CREATE ROLE admins;
  EXCEPTION WHEN DUPLICATE_OBJECT THEN
  -- DO NOTHING
END;
$$;
GRANT SELECT ON pg_file_settings TO admins;
GRANT SELECT ON pg_settings TO admins;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_show_all_file_settings() to admins;


-- CREATE DESTINATION SCHEMA IF IT DOESN'T ALREADY EXIST.
CREATE SCHEMA IF NOT EXISTS :destination_schema;
-- SET THE search_path TO THE NEW SCHEMA SO THAT ALL OBJECTS END UP THERE.


-- VIEW SHOWING ALL ACTIVE CONFIGURATION FILES
DROP VIEW IF EXISTS config_files;
CREATE VIEW config_files AS
  WITH cte AS (
    SELECT sourcefile, min(seqno) AS low
    FROM pg_file_settings
    GROUP BY sourcefile
  )
  SELECT sourcefile AS "Configuration File" 
  FROM cte 
  WHERE sourcefile IS NOT NULL
  ORDER BY low;
GRANT SELECT ON config_files TO PUBLIC;
COMMENT ON VIEW config_files IS 'Show all active configuration files.';


-- VIEW SHOWING CURRENT NON-DEFAULT SETTINGS
DROP VIEW IF EXISTS config_non_default_settings;
CREATE VIEW config_non_default_settings AS
SELECT category, name, setting, unit, source, sourcefile, sourceline
FROM pg_settings
WHERE source <> 'default'
ORDER BY 1, 2;
GRANT SELECT ON config_non_default_settings TO PUBLIC;
COMMENT ON VIEW config_non_default_settings IS 'Show current non-default settings.';


-- FUNCTION FOR RELOADING CONFIGURATION FILES IF FREE OF ERRORS
DROP VIEW IF EXISTS config_reload;
DROP FUNCTION IF EXISTS config_reload();
CREATE FUNCTION config_reload() RETURNS SETOF text LANGUAGE plpgsql AS $body$
DECLARE
  r text;
  noOfUnappliedSettings integer;
  noOfErrors integer;
BEGIN
  SELECT COUNT(*) INTO NoOfErrors
  FROM pg_file_settings
  WHERE error IS NOT NULL;

  IF NoOfErrors > 0 THEN
    FOR r IN SELECT
      format ('%L in line %s of file %L, name: %s, setting %s',
      error, sourceline, sourcefile, name, setting)
    FROM pg_file_settings
    WHERE error IS NOT NULL
    LOOP
      RETURN NEXT r;
    END LOOP;
    RETURN NEXT 'Number of errors: ' || NoOfErrors;
  END IF;

  IF NoOfErrors = 0 THEN
    SELECT pg_reload_conf() INTO r;
    SELECT COUNT(*) INTO NoOfUnappliedSettings
    FROM pg_file_settings f
    WHERE name IN (
      SELECT name FROM pg_settings
      WHERE pending_restart
    )
    AND setting <> (
      SELECT setting FROM pg_settings s
      WHERE s.name = f.name
    )
    OR NOT applied;
    FOR r IN SELECT
      format ('Unapplied setting in line %s of file %L, name: %s, setting %s',
      sourceline, sourcefile, name, setting)
    FROM pg_file_settings f
    WHERE name IN (
      SELECT name FROM pg_settings
      WHERE pending_restart
    )
    AND setting <> (
      SELECT setting FROM pg_settings s
      WHERE s.name = f.name
    )
    OR NOT applied
    LOOP
      RETURN NEXT r;
    END LOOP;
    IF NoOfUnappliedSettings = 0 THEN
      RETURN NEXT 'Configuration files have been successfully reloaded.';
    ELSE
      RETURN NEXT 'Number of unapplied settings: ' || NoOfUnappliedSettings;
      RETURN NEXT 'Please restart the server to apply these settings.';
    END IF;
  END IF;
  RETURN;
  END;
$body$;
COMMENT ON FUNCTION config_reload() IS 'Reload configuration without restart, if config files are error-free.';
CREATE VIEW config_reload AS SELECT * FROM config_reload();
COMMENT ON VIEW config_reload IS 'Reload configuration without restart, if config files are error-free.';


-- VIEW TO SHOW OVERRIDDEN SETTINGS IN CONFIG FILES
DROP VIEW IF EXISTS config_overridden;
CREATE VIEW config_overridden AS
  SELECT name, sourcefile ||':'|| sourceline || ', ' || setting  AS "config line",
  (SELECT COALESCE (sourcefile || ':'|| sourceline, source) || ', ' || setting || COALESCE(' (unit: '||unit||')','')
    FROM pg_settings s WHERE lower(s.name)=lower(f.name)
  ) AS "overridden by"
  FROM pg_file_settings f
  WHERE NOT applied;
COMMENT ON VIEW config_overridden IS 'Show configuration settings in file which are currently overridden by later entries.';


-- VIEW TO TEST CONFIGURATION FILES FOR ERRORS
DROP VIEW IF EXISTS config_test;
CREATE VIEW config_test AS
SELECT * FROM pg_file_settings WHERE error IS NOT NULL;
COMMENT ON VIEW config_test IS 'Test configuration files for syntax errors.';


-- FUNCTION TO KICK OUT ALL OTHER SESSIONS
DROP FUNCTION IF EXISTS stop_sessions() CASCADE;
CREATE FUNCTION stop_sessions() RETURNS BOOLEAN LANGUAGE SQL AS $body$
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid();
$body$;
COMMENT ON FUNCTION stop_sessions() IS 'Stop all sessions except this one.';
CREATE VIEW stop_sessions AS SELECT * FROM stop_sessions();
COMMENT ON VIEW stop_sessions IS 'Stop all sessions except this one.';

-- VIEW SHOWING CURRENT ACTIVITY ON THE SYSTEM
DROP VIEW IF EXISTS current_activity;
CREATE VIEW current_activity AS
  SELECT
  datname AS "database",
  usename AS "user",
  application_name AS "application",
  COALESCE(client_hostname || '(' || client_addr || ')', client_addr::TEXT, '[local]') AS "client hostname",
  backend_start::TIMESTAMP(0) AS "session start",
  state,
  wait_event_type AS "wait event type",
  CASE
    WHEN state = 'active'
    THEN (current_timestamp::TIMESTAMP(0) - query_start::TIMESTAMP(0))::TEXT
    ELSE ''
    END AS "query duration",
  CASE
    WHEN state = 'active'
    THEN query
    ELSE ''
  END AS "active query"
  FROM pg_stat_activity
  WHERE datname IS NOT NULL;
GRANT SELECT ON current_activity TO PUBLIC;
COMMENT ON VIEW current_activity IS 'Show current activity of the database system.';


-- VIEW SHOWING CURRENT SESSIONS PER USER
DROP VIEW IF EXISTS user_sessions;
CREATE VIEW user_sessions AS
  SELECT usename, COUNT(*), string_agg(pid::text, ', ') AS pid, string_agg(datname,', ') AS database
  FROM pg_stat_activity
  GROUP BY usename;
COMMENT ON VIEW user_sessions IS 'Show current sessions per user.';


-- VIEW SHOWING CURRENT QUERIES PER USER
DROP VIEW IF EXISTS user_queries;
CREATE VIEW user_queries AS
  SELECT usename, datname, query
  FROM pg_stat_activity
  ORDER BY usename;
GRANT SELECT ON user_queries TO PUBLIC;
COMMENT ON VIEW user_queries IS 'Show current queries per user.';


-- VIEW TO SHOW ROW COUNTS OF ALL NON-EMPTY TABLES IN CURRENT SCHEMAS
DROP VIEW IF EXISTS table_row_counts;
CREATE VIEW table_row_counts AS
  SELECT nspname AS schemaname,relname,reltuples::numeric
  FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE
  nspname IN (SELECT unnest(current_schemas(false))) AND
  relkind='r' AND reltuples <> 0
  ORDER BY reltuples DESC;
GRANT SELECT ON table_row_counts TO PUBLIC;
COMMENT ON VIEW table_row_counts IS 'Show estimated row counts of all non-empty tables in current search_schema (don''t forget to ANALYZE).';


-- VIEW TO SHOW EMPTY TABLES IN CURRENT SCHEMA
DROP VIEW  IF EXISTS tables_empty;
CREATE VIEW tables_empty AS
  SELECT nspname AS schemaname,relname,reltuples
  FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE
  nspname IN (SELECT unnest(current_schemas(false))) AND
  relkind='r' AND reltuples = 0;
GRANT SELECT ON tables_empty TO PUBLIC;
COMMENT ON VIEW tables_empty IS 'Show empty tables in current schemas.';


-- VIEW REPORTING TABLE SIZES IN SCHEMA
DROP VIEW IF EXISTS table_sizes;
CREATE VIEW table_sizes AS
  SELECT oid, table_schema as schema, table_name, row_estimate::numeric,
  pg_size_pretty(total_bytes) AS total,
  pg_size_pretty(index_bytes) AS INDEX,
  pg_size_pretty(toast_bytes) AS toast,
  pg_size_pretty(table_bytes) AS TABLE
  FROM (
    SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
    SELECT c.oid,nspname AS table_schema, relname AS table_name,
          c.reltuples AS row_estimate,
          pg_total_relation_size(c.oid) AS total_bytes,
          pg_indexes_size(c.oid) AS index_bytes,
          pg_total_relation_size(reltoastrelid) AS toast_bytes
    FROM pg_class c
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE relkind = 'r'
    ) a
  ) a
  WHERE table_schema IN (SELECT unnest(current_schemas(false)))
  ORDER BY total_bytes DESC;
GRANT SELECT ON table_sizes TO PUBLIC;
COMMENT ON VIEW table_sizes IS 'Show sizes of tables in current schemas.';


-- VIEW TO SHOW UNUSED INDEXES WHICH AREN'T PRIMARY KEYS OR UNIQUE CONSTRAINTS
DROP VIEW IF EXISTS indexes_unused;
CREATE VIEW indexes_unused AS
 SELECT relname, indexrelname
  FROM pg_stat_user_indexes
  WHERE (idx_scan = 0 OR idx_scan IS NULL)
    AND indexrelid NOT IN (
      SELECT conindid
      FROM pg_constraint
      WHERE contype IN ( 'p', 'u')
    );
GRANT SELECT ON indexes_unused TO PUBLIC;
COMMENT ON VIEW indexes_unused IS 'Show unused non-primary-key/non-unique indexes.';


-- VIEW TO SHOW USAGE OF INDEXES, SHOW USAGE COUNT AS TIMES_USED
DROP VIEW IF EXISTS index_usage;
CREATE VIEW index_usage AS
  SELECT idx_scan AS TIMES_USED, indexrelid::regclass AS INDEX, relid::regclass AS TABLE
  FROM pg_stat_user_indexes NATURAL JOIN pg_index
  WHERE idx_scan > 0 AND NOT indisunique
  ORDER BY 2;
GRANT SELECT ON index_usage TO PUBLIC;
COMMENT ON VIEW index_usage IS 'Show usage count of indexes.';


-- VIEW TO SHOW TABLES WITH MORE THAN 1000 ROWS WHICH HAVE ONLY BEEN
-- SEQUENTIALLY SCANNED, BUT NOT READ BY INDEX
DROP VIEW IF EXISTS tables_seq_scan;
CREATE VIEW tables_seq_scan AS
  SELECT relname, seq_scan, n_live_tup
  FROM pg_stat_user_tables
  WHERE (idx_scan = 0 OR idx_scan IS NULL) AND n_live_tup > 1000;
GRANT SELECT ON tables_seq_scan TO PUBLIC;
COMMENT ON VIEW tables_seq_scan IS 'Show tables with more than 1000 rows which only have been sequentially scanned.';


-- VIEW TO SHOW SIZE OF DATABASES
DROP VIEW IF EXISTS db_sizes;
CREATE VIEW db_sizes AS
  SELECT datname AS "database name", pg_size_pretty(pg_database_size(datname)) AS size
  FROM pg_database
  WHERE NOT datistemplate;
GRANT SELECT ON db_sizes TO PUBLIC;
COMMENT ON VIEW db_sizes IS 'Show size of databases (except template dbs)';


-- VIEW TO SHOW ROLE NAMES AND ATTRIBUTES
DROP VIEW IF EXISTS role_attributes;
CREATE VIEW role_attributes AS
  SELECT r.rolname, COALESCE(pg_catalog.shobj_description(r.oid, 'pg_authid'), '') AS description,
    CASE WHEN rolcanlogin    THEN '  LOGIN '       ELSE 'NOLOGIN ' END ||
    CASE WHEN rolsuper       THEN '  SUPERUSER '   ELSE 'NOSUPERUSER ' END ||
    CASE WHEN rolcreatedb    THEN '  CREATEDB '    ELSE 'NOCREATEDB ' END ||
    CASE WHEN rolcreaterole  THEN '  CREATEROLE '  ELSE 'NOCREATEROLE ' END ||
    CASE WHEN rolreplication THEN '  REPLICATION ' ELSE 'NOREPLICATION ' END ||
    CASE WHEN rolbypassrls   THEN '  BYPASSRLS '   ELSE 'NOBYPASSRLS ' END
    AS attributes,
    CASE WHEN r.rolconnlimit = -1 THEN 'no connection limit '
      ELSE r.rolconnlimit::text
    END AS connection_limit
  FROM pg_catalog.pg_roles r
  WHERE r.rolname !~ '^pg_'
  ORDER BY 1;
COMMENT ON VIEW role_attributes IS 'Show role names, real names and attributes.';


-- VIEW TO SHOW ROLE NAMES AND MEMBERSHIPS
DROP VIEW IF EXISTS role_memberships;
CREATE VIEW role_memberships AS
  SELECT r.rolname, COALESCE(pg_catalog.shobj_description(r.oid, 'pg_authid'), '') AS description,
    NULLIF(ARRAY_TO_STRING (ARRAY(SELECT b.rolname
      FROM pg_catalog.pg_auth_members m
      JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
      WHERE m.member = r.oid), ', '), '') AS memberof
  FROM pg_catalog.pg_roles r
  WHERE r.rolname !~ '^pg_'
  ORDER BY 1;
COMMENT ON VIEW role_memberships IS 'Show role names, real names and role memberships.';


-- VIEW TO SHOW ALL GROUP ROLES AND MEMBERSHIP
DROP VIEW IF EXISTS role_groups_memberships;
CREATE VIEW role_groups_memberships AS
  SELECT r.rolname, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description,
    ARRAY_TO_STRING (ARRAY(SELECT b.rolname
      FROM pg_catalog.pg_auth_members m
      JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
      WHERE m.member = r.oid), ', ') as memberof,
  CASE WHEN rolsuper THEN 'is Superuser' ELSE '' END AS "special"
  FROM pg_catalog.pg_roles r
  WHERE r.rolname !~ '^pg_' AND NOT r.rolcanlogin
  ORDER BY 1;
COMMENT ON VIEW role_groups_memberships IS 'Show group names, description and memberships.';


-- VIEW TO GET ROLE HIERARCHY TOP TO BOTTOM
DROP VIEW IF EXISTS roles_from_top;
CREATE VIEW roles_from_top AS
  WITH RECURSIVE rollen (oid, role, canlogin) AS (
    -- Startpunkte der Hierarchien
    SELECT oid, rolname::text, rolcanlogin
    FROM pg_catalog.pg_roles
    WHERE rolname NOT IN ('postgres') AND rolname !~ '^pg_'
    AND oid NOT IN (SELECT member FROM pg_catalog.pg_auth_members)
    UNION
    -- rekursive Unterabfrage
    SELECT b.oid, r.role || '->' || b.rolname, b.rolcanlogin
    FROM rollen r
    JOIN pg_catalog.pg_auth_members m ON (r.oid = m.roleid)
    JOIN pg_catalog.pg_roles b ON (b.oid = m.member)
    WHERE r.role NOT IN ('postgres')
    AND r.role !~ '^pg_' AND r.oid <> m.member
  )
  SELECT *, pg_catalog.shobj_description(oid, 'pg_authid') AS description
  FROM rollen
  ORDER BY 3, 2;
COMMENT ON VIEW roles_from_top IS 'Show role hierarchy starting from the top.';


-- VIEW TO GET ROLE HIERARCHY BOTTOM TO TOP
DROP VIEW IF EXISTS roles_from_bottom;
CREATE VIEW roles_from_bottom AS
  WITH RECURSIVE rollen (oid, role, description, membership, canlogin, ebene) AS (
  -- Startpunkte der Hierarchien
  SELECT oid, rolname,
    pg_catalog.shobj_description(oid, 'pg_authid') AS description,
    ''::text COLLATE "C", rolcanlogin, 0
  FROM pg_catalog.pg_roles
  WHERE rolname NOT IN ('postgres') AND rolname !~ '^pg_'
  AND rolcanlogin
  UNION
  -- rekursive Unterabfrage
  SELECT b.oid, r.role, r.description, b.rolname, r.canlogin, ebene+1
  FROM rollen r
  JOIN pg_catalog.pg_auth_members m ON (r.oid = m.member)
  JOIN pg_catalog.pg_roles b ON (b.oid = m.roleid)
  WHERE r.role NOT IN ('postgres')
    AND r.role !~ '^pg_'
    AND r.oid <> b.oid
  ), mitgliedschaften AS (
  SELECT role, description, canlogin, membership || ' (' || MAX(ebene) || ')' AS membership, MAX(ebene) AS ebene
  FROM rollen
  WHERE membership <> ''
  GROUP BY role, description, membership, canlogin
  )
  SELECT role, description, canlogin, string_agg (membership, ', ' ORDER BY ebene DESC, membership ASC) AS membership
  FROM mitgliedschaften
  GROUP BY role, description, canlogin
  ORDER BY 1;
COMMENT ON VIEW roles_from_bottom IS 'Show role hierarchy starting from the bottom.';


-- VIEW TO GET DEFAULT PRIVILEGES ON NEW OBJECTS
DROP VIEW IF EXISTS default_privs;
CREATE VIEW default_privs AS
  SELECT da.oid, rolname AS role_name,
    nspname AS schema,
    CASE defaclobjtype
      WHEN 'r' THEN 'relations'
      WHEN 'f' THEN 'routines'
      WHEN 'S' THEN 'sequences'
      WHEN 'T' THEN 'types'
      WHEN 'n' THEN 'schemas'
      ELSE '<other>'
    END AS object_type,
    defaclacl AS default_acl
  FROM pg_default_acl da
    JOIN pg_roles r ON r.oid = da.defaclrole
    LEFT JOIN pg_namespace n ON n.oid = defaclnamespace;
COMMENT ON VIEW default_privs IS 'Show default privileges for new objects.';


-- VIEW LIST OF OBJECTS OWNED BY A ROLE.
-- These include tables, indexes, sequences, views, materialized views,
-- composite types, toast tables, foreign tables, procedures, functions,
-- window functions, aggregate functions, data types, and schemas.
-- Rarely used objects like tablespaces, access methods,
-- operator classes, conversions, foreign objects (servers,
-- tables, user mappings), text search configurations,
-- languages, event triggers and the like are not included.
DROP FUNCTION IF EXISTS list_objects (name);
DROP TYPE IF EXISTS obj_list;
CREATE TYPE obj_list AS (
  schema_name name,
  name name,
  kind text,
  owner name
);
CREATE FUNCTION list_objects (owner_param name) RETURNS SETOF obj_list AS $body$
  WITH cte AS (
    -- Data objects
    SELECT
        n.nspname AS schema_name,
        c.relname AS name,
        CASE c.relkind
          WHEN 'r' THEN 'table'
          WHEN 'i' THEN 'index'
          WHEN 'S' THEN 'sequence'
          WHEN 'v' THEN 'view'
          WHEN 'm' THEN 'materialized view'
          WHEN 'c' THEN 'composite type'
          WHEN 't' THEN 'toast table'
          WHEN 'f' THEN 'foreign table'
          ELSE '<unknown> ' || c.relkind::text
        END AS kind,
        pg_get_userbyid(c.relowner) AS owner
      FROM pg_class c
      JOIN pg_namespace n ON n.oid = c.relnamespace

    UNION ALL

    -- Functions and procedures
    SELECT
        n.nspname,
        p.proname,
        CASE p.prokind
          WHEN 'p' THEN 'procedure'
          WHEN 'f' THEN 'function'
          WHEN 'w' THEN 'window function'
          WHEN 'a' THEN 'aggregate function'
          ELSE '<unknown> ' || p.prokind::text
        END AS prokind,
        pg_get_userbyid(p.proowner)
      FROM pg_proc p
      JOIN pg_namespace n ON n.oid = p.pronamespace

    UNION ALL

    -- Data types
    SELECT n.nspname,
      t.typname,
      'domain',
      pg_get_userbyid(t.typowner)
      FROM pg_catalog.pg_type t
      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
      WHERE t.typrelid = 0 AND typcategory = 'N'

    UNION ALL

    -- Schemas
    SELECT NULL, n.nspname, 'schema',
      pg_catalog.pg_get_userbyid(n.nspowner)
    FROM pg_catalog.pg_namespace n
    WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
  )
  SELECT * FROM cte
  WHERE owner = owner_param
  ORDER BY 1 NULLS LAST, 2;
$body$ LANGUAGE sql;
GRANT EXECUTE ON FUNCTION list_objects (name) TO PUBLIC;
COMMENT ON FUNCTION list_objects (name)  IS 'List objects owned by a role.';


-- VIEW TO GET PRIMARY KEY AND UNIQUE CONSTRAINTS FROM INFORMATION SCHEMA
DROP VIEW IF EXISTS primary_unique_constraints;
CREATE VIEW primary_unique_constraints AS
  SELECT kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME,
     tc.CONSTRAINT_TYPE, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
  JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
    ON kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
   AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
   AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
   AND kcu.TABLE_NAME = tc.TABLE_NAME
  WHERE tc.CONSTRAINT_TYPE in ('PRIMARY KEY', 'UNIQUE')
  ORDER BY kcu.TABLE_SCHEMA, kcu.TABLE_NAME, tc.CONSTRAINT_TYPE, kcu.CONSTRAINT_NAME, kcu.ORDINAL_POSITION;
GRANT SELECT ON primary_unique_constraints TO PUBLIC;
COMMENT ON VIEW primary_unique_constraints IS 'Show primary key and unique constraints.';


-- VIEW SHOWING INFORMATION ABOUT LOGGING
DROP FUNCTION IF EXISTS log_info() CASCADE;
CREATE FUNCTION log_info() RETURNS SETOF text AS $$
DECLARE
  s text;
BEGIN
  RETURN QUERY
    SELECT 'Logging destination is: ' || setting FROM pg_settings WHERE name='log_destination';
  IF ((SELECT setting FROM pg_settings WHERE name='log_destination') = 'stderr') AND
     ((SELECT setting FROM pg_settings WHERE name='logging_collector') = 'off')
  THEN
    RETURN QUERY SELECT 'Logging goes to stderr without logging collector. Examine OS-level startup script to find out where it ends up.';
  ELSE
    RETURN QUERY SELECT 'logging_collector is: '
        || (SELECT setting FROM pg_settings WHERE name='logging_collector');
    SELECT ((SELECT setting FROM pg_settings WHERE name='data_directory') || '/'
         || (SELECT setting FROM pg_settings WHERE name='log_directory')) INTO s;
    IF s IS NOT NULL THEN
      RETURN NEXT ('log directory is: ' || s);
    END IF;
    SELECT (SELECT 'log_file pattern is: ' || (SELECT setting FROM pg_settings WHERE name='log_filename')) INTO s;
    IF s IS NOT NULL THEN
      RETURN NEXT (s);
    END IF;
    RETURN QUERY SELECT 'current logfile is: '
        || (SELECT setting FROM pg_settings WHERE name='data_directory')
        || '/' || pg_current_logfile();
  END IF;
  RETURN QUERY SELECT 'log_line_prefix is: '   || (SELECT setting FROM pg_settings WHERE name='log_line_prefix');
  RETURN;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION log_info() IS 'Show information about logging.';
DROP VIEW IF EXISTS log_info;
CREATE VIEW log_info AS
  SELECT log_info from log_info();
COMMENT ON VIEW log_info IS 'Show information about logging.';



-- FUNCTION TO ACQUIRE SUPERUSER RIGHTS IF MEMBER OF ROLE postgres
DROP FUNCTION IF EXISTS admin();
CREATE FUNCTION admin() RETURNS VOID LANGUAGE SQL AS $body$
  SET role TO "postgres";
$body$;
COMMENT ON FUNCTION admin() IS 'Acquire SUPERUSER rights if permitted (member of role ''postgres'').';


-- VIEW SHOWING UPTIME OF SERVER
DROP VIEW IF EXISTS uptime;
CREATE VIEW uptime AS
  SELECT 'started ' || pg_postmaster_start_time()::timestamp(0) ||
      ', running for ' || date_trunc('second', current_timestamp - pg_postmaster_start_time())
  AS "postgresql uptime";
GRANT SELECT ON uptime TO PUBLIC;
COMMENT ON VIEW uptime IS 'Show since when and for how long the server has been running.';


-- VIEW SHOWING TABLE BLOAT
DROP VIEW IF EXISTS table_bloat;
CREATE VIEW table_bloat AS
WITH constants AS (
  SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 4 AS ma
), bloat_info AS (
  SELECT
    ma,bs,schemaname,tablename,
    (datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
    (maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
  FROM (
    SELECT
      schemaname, tablename, hdr, ma, bs,
      SUM((1-null_frac)*avg_width) AS datawidth,
      MAX(null_frac) AS maxfracsum,
      hdr+(
        SELECT 1+count(*)/8
        FROM pg_stats s2
        WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
      ) AS nullhdr
    FROM pg_stats s, constants
    GROUP BY 1,2,3,4,5
  ) AS foo
), bloat AS (
  SELECT
    schemaname, tablename, cc.relpages, bs,
    CEIL((cc.reltuples*((datahdr+ma-
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta
  FROM bloat_info
  JOIN pg_class cc ON cc.relname = bloat_info.tablename
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema'
), index_bloat AS (
  SELECT
    schemaname, tablename, bs,
    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
  FROM bloat_info
  JOIN pg_class cc ON cc.relname = bloat_info.tablename
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema'
  JOIN pg_index i ON indrelid = cc.oid
  JOIN pg_class c2 ON c2.oid = i.indexrelid
)
SELECT
  type, schemaname, object_name, bloat, pg_size_pretty(raw_waste) AS waste
FROM (
    SELECT 'table' AS type, schemaname, tablename AS object_name,
      ROUND(CASE WHEN otta=0 THEN 0.0 ELSE bloat.relpages/otta::numeric END,1) AS bloat,
      CASE WHEN relpages < otta THEN '0' ELSE (bs*(bloat.relpages-otta)::bigint)::bigint END AS raw_waste
    FROM bloat
  UNION
    SELECT 'index' AS type, schemaname, tablename || '::' || iname AS object_name,
      ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS bloat,
      CASE WHEN ipages < iotta THEN '0' ELSE (bs*(ipages-iotta))::bigint END AS raw_waste
    FROM index_bloat
) bloat_summary
ORDER BY raw_waste DESC, bloat DESC;
COMMENT ON VIEW table_bloat IS 'Show table bloat';


-- VIEW SHOWING INFORMATION ABOUT CURRENT CONNECTIONS
DROP VIEW IF EXISTS db_connections;
CREATE VIEW db_connections AS
SELECT state AS "connections in state", count(*)
FROM pg_stat_activity GROUP BY state;
COMMENT ON VIEW db_connections IS 'Show current connections';


-- VIEW SHOWING CURRENT TIMELINE
DROP VIEW IF EXISTS timeline;
CREATE VIEW timeline AS
SELECT trim (leading substr (pg_walfile_name (pg_current_wal_lsn()), 0, 9), '0')::integer AS timeline;
COMMENT ON VIEW timeline IS 'Show current timeline';


-- VIEW SHOWING INFORMATION ABOUT VIEWS IN (DOESN'T SHOW FUNCTIONS CREATED BY THIS SCRIPT)
DROP VIEW IF EXISTS viewlist;
CREATE VIEW viewlist AS
  SELECT relname AS "view name", pg_catalog.obj_description(oid, 'pg_class') AS "description"
  FROM pg_class
  WHERE relkind = 'v'
  AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = :'destination_schema')
  ORDER BY 1;
GRANT SELECT ON viewlist TO PUBLIC;
\set comment 'Show information about views in schema ':"destination_schema"
COMMENT ON VIEW viewlist IS :'comment';


-- VIEW SHOWING INFORMATION ABOUT FUNCTIONS (DOESN'T SHOW VIEWS CREATED BY THIS SCRIPT)
DROP VIEW IF EXISTS funclist;
CREATE VIEW funclist AS
  SELECT proname as "function name",
    pg_catalog.pg_get_function_result(oid) as "result data type",
    pg_catalog.pg_get_function_arguments(oid) as "argument data types",
    pg_catalog.obj_description(oid, 'pg_proc') AS "description"
  FROM pg_catalog.pg_proc
  WHERE prokind = 'f' AND pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = :'destination_schema')
  ORDER BY 1;
GRANT SELECT ON funclist TO PUBLIC;
\set comment 'Show information about functions in schema ':"destination_schema"
COMMENT ON VIEW funclist IS :'comment';


-- FUNCTION APPENDING A SCHEMA TO THE search_path
DROP FUNCTION IF EXISTS path_append(text);
CREATE FUNCTION path_append (text) RETURNS text AS $$
  SELECT set_config ('search_path', current_setting ('search_path') || ',' || $1, false)
  WHERE current_setting ('search_path') !~ ('(^|,\s?)' || $1 || '(,\s?|$)');
  SELECT current_setting ('search_path');
$$ LANGUAGE sql;
COMMENT ON FUNCTION path_append(text) IS 'Append a schema to the search_path';


-- FUNCTION PREPENDING A SCHEMA TO THE search_path
DROP FUNCTION IF EXISTS path_prepend(text);
CREATE FUNCTION path_prepend (text) RETURNS text AS $$
  SELECT set_config ('search_path', $1 || ',' || current_setting ('search_path'), false)
  WHERE current_setting ('search_path') !~ ('(^|,\s?)' || $1 || '(,\s?|$)');
  SELECT current_setting ('search_path');
$$ LANGUAGE sql;
COMMENT ON FUNCTION path_prepend(text) IS 'Prepend a schema to the search_path';


-- FUNCTION REMOVING A SCHEMA FROM THE search_path
DROP FUNCTION IF EXISTS path_remove(text);
CREATE FUNCTION path_remove (text) RETURNS text AS $$
  DECLARE
    retval text;
  BEGIN
    BEGIN
      SELECT set_config ('search_path',
          regexp_replace (current_setting ('search_path'), ('(^|,\s?)' || $1), ''), false) INTO retval;
      EXCEPTION WHEN OTHERS THEN
      -- NOTHING
    END;
    BEGIN
      SELECT set_config ('search_path',
          regexp_replace (current_setting ('search_path'), ($1 || '(,\s?|$)'), ''), false) INTO retval;
      EXCEPTION WHEN OTHERS THEN
      -- NOTHING
    END;
    SELECT current_setting ('search_path') INTO retval;
    RETURN retval;
  END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION path_remove(text) IS 'Remove a schema from the search_path';


-- PROCEDURE searchall() TO SET THE search_path TO ALL SCHEMAS
-- OF CURRENT DATABASE IN ALPHABETICAL ORDER
-- USAGE:    CALL searchall();

DROP PROCEDURE IF EXISTS searchall();
CREATE PROCEDURE searchall() LANGUAGE plpgsql AS $body$
DECLARE
  cmd text = 'set search_path to ';
  schema_name text;
BEGIN
  FOR schema_name IN 
    SELECT nspname
    FROM pg_catalog.pg_namespace
    WHERE nspname !~ '^pg_' AND nspname <> 'information_schema'
    ORDER BY 1
  LOOP
    cmd = cmd || '"' || schema_name ||  '",';
  END LOOP;
  cmd = trim (TRAILING FROM cmd, ',');
  EXECUTE cmd;
END;
$body$;
COMMENT ON PROCEDURE searchall() IS 'Set search_path to all schemas of current database in alphabetical order';


-- LOAD EXTENSION tablefunc (for crosstab and other functions)
-- UNCOMMENT THE FOLLOWING LINE IF YOU WANT THIS EXTENSION
-- CREATE EXTENSION IF NOT EXISTS tablefunc;

COMMIT;

-- END OF FILE


