.psqlrc.sql

-- ---------------------
--                  /*
--                  _
--  _ __  ___  __ _| |
-- | '_ \/ __|/ _` | |
-- | |_) \__ \ (_| | |
-- | .__/|___/\__, |_|
-- |_|           |_|
-- ---------------------
\set e '\\echo '
\set s '\\set '
\set a '\\set '
\set q '\\q '
-- Info about this file:
/*
    - can be optionally loaded with \i
    - ... or can organize other files and load with \i
    - ... can also load sql statements from sql files, nicely, with \copy
    - reads variables set via [-v] flag

    Wrangling multiline text blocks, and multiline cmd-sequences, and info-prints, and echos:
        * let :e == \set e '\\echo '
        * preface each of these with [ \set printer ]
    - :e :content -- will print everything SQUEEZED
    - ':e' :content -- will print everything SQUEEZED
    - :e ':content' -- will print everything CORRECT
    - ':e' ':content' -- will print everything CORRECT
    - '\\echo ' content -- will print it LITERAL
    - '\\echo ' :content -- will print everything SQUEEZED (bc content renders first)
    - '\\echo ' ':content' -- will print everything CORRECT
    - \echo :content -- will print everything CORRECT
    - \echo ':content' -- will print it LITERAL
    - macro-which-prints
*/


------------
-- BANNER --
------------

-- For some reason, cant use select construct here.
/* SELECT $banner$ */
\! tput clear
\! figlet 'psql'
\! echo ---------------------
\! tmux select-pane -t $TMUX_PANE -P "bg=#001305";
/* $banner$ as print_banner \gset */
/* :print_banner */

\echo '\nCurrent Host Server Date Time : '`date` '\n'


--------------------------
-- ETC. / MISCELLANEOUS --
--------------------------

-- Pager stuff
\setenv PAGER 'less '
-- \set test_pager `cat path/to/sql-file-which-produces-large-output.sql`

-- Table appearance stuff
\pset border 2
\pset linestyle unicode
\pset footer 0
--^ prettier table lines
--\pset null '¤'
--\pset null '[NULL]'
\pset null '∅'


\set menu '\\i ~/.psqlrc'
\set reload '\\i ~/.psqlrc'
    \set rl ':reload'

\set help '\\?'

-- dont print those two lines at the bottom (version info, help for help)
\set QUIET ON
-- same as ON?
\set QUIET YES


-------------
-- EXPLORE --
-------------
-- META:
-- https://github.com/search?q=psqlrc&type=commits
-- https://wiki.postgresql.org/wiki/Psqlrc
-- pg + psqlrc -- https://github.com/dbcli/pgcli/issues/20

-- This guys sets up a GUI-based cnxn that reports to the prompt
-- https://github.com/funbringer/smart_psqlrc
-- psqlplusplus
-- https://github.com/jackgo73/psqlplus
-- pipe and smoke it
-- https://github.com/kreldjarn/psqlrc
-- welcome banner
-- https://github.com/pawsuch/psqlrc/blob/main/psqlrc_example
-- hollobon
-- https://github.com/hollobon/psqlrc/blob/master/psqlrc.conf
-- nurrik
-- https://github.com/nurikk/psqlrc/blob/master/psqlrc
-- e7e6
-- https://github.com/e7e6/psqlrc/blob/master/psqlrc
-- babuurduck
-- https://github.com/baburdick/psqlrc-files/blob/master/.psqlrc-admin
-- this dude's website
    -- https://www.depesz.com/2008/05/18/silencing-commands-in-psqlrc/
    -- https://www.depesz.com/2021/06/23/a-tale-of-making-company-wide-standard-psqlrc/
-- privileges
    -- https://linuxhint.com/check-postgres-user-privileges/
    -- https://stackoverflow.com/questions/22483555/postgresql-give-all-permissions-to-a-user-on-a-postgresql-database
    -- https://www.cyberciti.biz/faq/howto-add-postgresql-user-account/
-- https://www.postgresql.org/docs/10/app-psql.html
-- https://www.educba.com/postgresql-commands/
-- etc.
    -- https://www.citusdata.com/blog/2017/07/16/customizing-my-postgres-shell-using-psqlrc/
    -- https://www.if-not-true-then-false.com/2009/postgresql-psql-psqlrc-tips-and-tricks/
    -- https://opensourcedbms.com/dbms/psqlrc-psql-startup-file-for-postgres/
    -- https://thoughtbot.com/blog/an-explained-psqlrc
-- regex-based-coloring of fields
    -- https://merlinmoncure.blogspot.com/2012/09/psql-now-with-splash-of-color.html
-- https://www.craigkerstiens.com/2013/02/21/more-out-of-psql/
-- http://pavdmyt.com/better-postgresql-cli-experience-with-psqlrc-tweaks/
-- full explain
    -- https://dba.stackexchange.com/questions/65744/is-there-a-way-to-define-macros-alias-in-psql
-- watch-wrapper
-- sizes
    -- https://dbtut.com/index.php/2018/10/07/postgresql-list-of-table-sizes/
    --- https://stackoverflow.com/questions/2596624/how-do-you-find-the-disk-size-of-a-postgres-postgresql-table-and-its-indexes
    -- https://stackoverflow.com/questions/2596670/how-do-you-find-the-row-count-for-all-your-tables-in-postgres
    -- https://www.digitalocean.com/community/tutorials/how-to-customize-the-postgresql-prompt-with-psqlrc-on-ubuntu-14-04


-- \set HISTFILE ~/.psql_history- :HOST - :DBNAME

-------------------------
-- PROMPT MODIFICATION --
-------------------------
/*
    Cant do a select-quote-literal-gset construct since PROMPT1 needs a value
    without newlines. Need to be careful that color codes are getting escaped
    w.r.t. their character count for readline operations. Can use multiple
    variable layers for the sake of readability.

    ...actually, probably can do the select-quote-literal-gset construct, just
    need to play around with quoting of the echo statement... to get it to
    concat.

-- dynamic variable in prompt example
>>> \set PROMPT1 '(%:dynovar:)>>> '
()>>> \set dynovar 12345
(12345)>>>

-- reference for changing colors of text via true color:
-- printf "\x1b[38;2;40;177;249mTRUECOLOR\x1b[0m\n"
--  - 38 is foreground
--  - 48 is background
--  - then, 3 numbers of 2/40/177 for r/g/b
--  - ;249m as the closing sequence


*/

\set withbug '\x1b[38;5;7m%M:%> %n@%/\n \x1b[38;5;3m%R%# \x1b[0m%x'
-------------------
-- PROMPT PIECES --
-------------------
\set s '\\set'
-------------------

-- ignore character counts, wrap the color codes
:s c_li '\x1'
:s c_ri '\x2'

-- color7: \x1b[38;5;7m
:s c_START '\x1b[38;5;'
:s start_ :c_START
:s c_END 'm'
:s _end :c_END

/* :s c_grey '\x1b[38;5;7m' */
:s c_grey :start_ '7' :_end
:s c_grey :c_li :c_grey :c_ri

:s c_none '\x1b[0m'
:s c_none :c_li :c_none :c_ri
:s p_domain '%M'
:s p_port '%>'
:s p_username '%n'
:s p_database '%/'
:s c_yellow '\x1b[38;5;3m'
:s c_yellow :c_li :c_yellow :c_ri
:s p_xthing '%x'
:s p_hash '%#'
:s p_r '%R'

----------------------
-- **** PROMPT **** --
----------------------
:s line1 :c_grey :p_domain ':' :p_port ' ' :p_username '@' :p_database
:s line2 ' ' :c_yellow :p_r :p_hash ' ' :c_none :p_xthing
\set PROMPT1 :withbug
\set PROMPT1 :line1 '\n' :line2
----------------------

:s c_red :start_ '1' :_end
:s c_red :c_li :c_red :c_ri

\set PROMPT2 :c_red '... ' :c_none

----------------------------
-- multi-line prompt1 set --
----------------------------
-- * cant get it to work
SELECT $$
:line1
\n
:line2
$$ as prompt1_content \gset
/* :s prompt1 :e :prompt1_content */
/* \set PROMPT1 :prompt1 */

----------------------------------
-- CONTEXT ACQUISITION SECTION --
----------------------------------
-- 1. query sizes of database, get largest table, get most active table
-- 2. print it

---------------------------
-- COMMON USAGE REMINDER --
---------------------------
SELECT $usage$
        use "\e" for opening a command to edit in your text editor
	use "\d [table]" naked to show list of tables. With table to show more information about the table
        use "\?" for the internal cmds
	use "\h" for syntax help on sql and psql commands
	use "\x" for expanded output on many-columned tables
        use "\l" for the schemas
        use "\conninfo" to make sure you are in the right schema
        use "\dS" for tables
$usage$ as _usage \gset
:s print_usage ':e :_usage'
:print_usage
-- :s print_usage :e :_usage # will get double-expanded

-------------
-- ALIASES --
-------------
\set examplealias '\\echo alias test'
-- and then an example abbreviation
\set ea ':examplealias'
-- and if you want to multiline it, you can use gset instead

-- Notes:
-- on variables: https://stackoverflow.com/q/36959/7601668
-- Multi-line meta-commands are not supported indeed:
-- - https://stackoverflow.com/q/42307906/7601668
-- These variables can be specified via variables:
-- - https://stackoverflow.com/a/38536729/7601668
-- ... you could build a bash-alias, var-aware-rc system that configures which
-- mode you're jumping into.
-- Quoting \echo, not quoting echo quoting the variable statement, all have
-- effects on:
--      - \-quoting
--      - squashing lines into a single line

SELECT $aliasoutput$
Available aliases:
        - :rcount, rc -- record count for all tables
        - :tsizes, ts -- NYI: table sizes
        - :quicklook, ql -- a report of a bunch of useful anchor points for
        - :killcnxns
        - :s, :f, :w, :o for quick-sql-bits
        - :bearings -- quickref for de-rusting e.g. \?, \h
        - :sumake -- make a super user
        - :privs -- goto link with privs
	- :pwd -- print present working directory
$aliasoutput$ as alias_banner_content \gset
\set print_alias_banner \echo  :alias_banner_content
:print_alias_banner

:s privs '\\echo `open https://linuxhint.com/check-postgres-user-privileges/`'

:s pwd ':e `pwd`'


------------------
-- RECORD COUNT --
------------------
\set rc ':rcount'
SELECT $rcount$
SELECT
  nspname AS schemaname,relname,reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r'
ORDER BY reltuples DESC;
$rcount$ as rcount \gset
-- ### Ideal output: ###
-- RECORD COUNTS
--- -----------------------------+-----------
 --  materials_production_groups |        11
 --  production_groups           |         8
 --  materials                   |         5

------------------
-- CNXN ACTIVITY --
------------------
\set ac ':activity'
SELECT $activity$
SELECT
  datname AS datname_todo, pid, usename AS username, application_name AS app_name, client_addr, client_hostname, client_port, query, state
FROM pg_stat_activity
WHERE usename IS NOT NULL
;
$activity$ as activity \gset

-------------
-- SCRATCH --
-------------
\set settings 'select name, setting,unit,context from pg_settings;'

\set locks  'SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, kl.pid AS blocking_pid, ka.usename AS blocking_user, a.query AS blocked_statement FROM pg_catalog.pg_locks bl JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.pid JOIN pg_catalog.pg_locks kl JOIN pg_catalog.pg_stat_activity ka ON kl.pid = ka.pid ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid WHERE NOT bl.granted;'

\set conninfo 'select usename, count(*) from pg_stat_activity group by usename;'

\set activity 'select datname, pid, usename, application_name,client_addr, client_hostname, client_port, query, state from pg_stat_activity;'

\set waits 'SELECT pg_stat_activity.pid, pg_stat_activity.query, pg_stat_activity.waiting, now() - pg_stat_activity.query_start AS \"totaltime\", pg_stat_activity.backend_start FROM pg_stat_activity WHERE pg_stat_activity.query !~ \'%IDLE%\'::text AND pg_stat_activity.waiting = true;'

\set dbsize 'SELECT datname, pg_size_pretty(pg_database_size(datname)) db_size FROM pg_database ORDER BY db_size;'

\set tablesize 'SELECT nspname || \'.\' || relname AS \"relation\", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN (\'pg_catalog\', \'information_schema\') ORDER BY pg_relation_size(C.oid) DESC LIMIT 40;'

\set uselesscol 'SELECT nspname, relname, attname, typname, (stanullfrac*100)::int AS null_percent, case when stadistinct >= 0 then stadistinct else abs(stadistinct)*reltuples end AS \"distinct\", case 1 when stakind1 then stavalues1 when stakind2 then stavalues2 end AS \"values\" FROM pg_class c JOIN pg_namespace ns ON (ns.oid=relnamespace) JOIN pg_attribute ON (c.oid=attrelid) JOIN pg_type t ON (t.oid=atttypid) JOIN pg_statistic ON (c.oid=starelid AND staattnum=attnum) WHERE nspname NOT LIKE E\'pg\\\\_%\' AND nspname != \'information_schema\' AND relkind=\'r\' AND NOT attisdropped AND attstattarget != 0 AND reltuples >= 100 AND stadistinct BETWEEN 0 AND 1 ORDER BY nspname, relname, attname;'

\set uptime 'select now() - pg_postmaster_start_time() AS uptime;'

SELECT $cmd$
Administrative queries:

                        :settings       -- Server Settings
                        :conninfo       -- Server connections
                        :activity       -- Server activity
                        :locks          -- Lock info
                        :waits          -- Waiting queires
                        :dbsize         -- Database Size
                        :tablesize      -- Tables Size
                        :uselesscol     -- Useless columns
                        :uptime         -- Server uptime
                        :menu           -- Help Menu
                        \h              -- Help w/ SQL commands
                        \?              -- Help w/ psql commands

$cmd$ as adminpro_queries_content \gset
:s print_adminpro_queries :e ':adminpro_queries_content'
-- :print_adminpro_queries

SELECT $cmd$
Development queries:

                        :sp             -- Current Search Path NYI
                        :clear          -- Clear screen        NYI
                        :ll             -- List                NYI
$cmd$ as dev_queries_content \gset
:s print_dev_queries :e ':dev_queries_content'
-- :print_dev_queries



\set clients_by_state 'select count(*),state from pg_stat_activity group by 2;'
\set clients_idle 'select count(*),state from pg_stat_activity where state <> \'active\' group by 2;'
\set dat_age 'select datname,age(datfrozenxid) from pg_database ;'
\set table_age 'SELECT c.oid::regclass AS table_name, GREATEST(age(c.relfrozenxid), age(t.relfrozenxid)) AS age, pg_table_size(c.oid::regclass) AS table_size FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind = \'r\'::"char"ORDER BY GREATEST(age(c.relfrozenxid), age(t.relfrozenxid)) DESC;'
\set replica_slots 'select * from pg_replication_slots';
\set replica_stats 'select * from pg_stat_replication';
\set slow_queries 'SELECT (total_time / 1000 / 60) as total_minutes, (total_time/calls) as average_time, query FROM pg_stat_statements ORDER BY 1 DESC LIMIT 100;'


--------------------------------------------------------
-- END: STUFF THAT MUST GO AFTER ALL OTHER STATEMENTS --
--------------------------------------------------------
-- shows query timing at bottom
-- \timing on
-- NO UNDERSTAND \echo `[[ -t 0 ]] || echo ':c_red WARNING: no tty, run psql with --no-psqlrc :c_none'`
-- NO UNDERSTAND \set pp '\\i /Users/dougpa/psqlplus/psqlplus.psql'

/*
Desires:
- sexy prompt
    - fix the color code
- themeing -- the idea is a theme-bank
- queries/aliases/introspectives/pulse-taking
    Questions we'd ask of a unknown database:
    - table interrogation
        - record count
        - how big is it?
        - how complex?: number of fields
        - deadspace identification?:
            - sort each column by the last time it was updated
            - change-frequency of a column or table
            * transaction log
            * timestamp of record changes
            - activity level of the database/table/column
                - parse the transaction log: "there were 1523 changes in the past hour"
            - sparse-ness ratio

- kill all
- watch-last SQL cmd
- \x-last SQL cmd

- IF conditional if it's an RDS box
>>> rdsadmin schema (datname)

Special stuff you should know about this box:
- it's aws's rds
- it's highly active (# of connections)
- it's a multiuser environment
- it's a single-user environment
- it doesn't have any defaults modified
- the last login was 5 months ago


- INFO BANNER:
    - whoami
    - which db
    - what other databases there are (that are NOT the default)
    - my auth info (am i admin?)
        - other users?
    - available tables -- if i connected direct
    - common usages
    - general data survey (size, recency)

    # make the builtins mnemonically-sound, +filtering of info
    :tables -- \dS

# of tables:

table name | # of records | # of fields | size | # of indices

- handy aliases -- bake in postgres tips in general into an alias-bank
    - fullexplain
    - crud aliases -- composable aliases of commonly used small components
        ;s
        ;f
        ;w
        ;o
    - "filtering by an id"

- banner information
    - auth info -- am i root? wtf is root?
- data entry
    - update
    - insert

Questions:
- what is search path
- what is toast
- watch is cool
- implement edit psqlrc inside of psql
- implement a version printer
Server: PostgreSQL 13.3
Version: 3.1.0
- implement the table-introspection, use f for field count by concatenating the string and r for row count
- how to get auth info, if i am a super user, etc.
- implement quicklink-aliases -- e.g. docs location
- implement copy-to-clipboard things like the cnxn string
- how to write a tables records to a local file

*/

-- ALIASES TO COMMON DBS
\set mats 'select * from materials'
\set macs 'select * from production_groups'
\set mmgs 'select * from materials_production_groups'

-- extended display
\x