PostgreSQL Stan PostgreSqlStan

Install pagila in local postgres db

These directions assume postgres is installed locally with a user—preferably not a superuser—with the same name as your OS username.

From the shell:

curl -OL https://github.com/devrimgunduz/pagila/archive/refs/tags/pagila-v3.1.0.tar.gz
tar -xvf pagila-pagila-v3.1.0.tar
cd pagila-pagila-v3.1.0

	# version 0.3 (see CHANGE NOTES comment below)
	bindlist () {
	local flag_all flag_help flag_list flag_raw
	local usage=(
	"Usage:"
	" bindlist list bindings"
	" bindlist -h --help print this message"
	"Options:"
	" -a --all include all bindings"
	" -l --list single column output"

	# NEW VERSION: see gist comment for link to refactored version
	######################################################################################

	# started as an alias, see https://postgresqlstan.github.io/zsh/binds/
	# my 1st attempt to do anything semi-complicated with shell scripting
	# don't know what i'm doing, just hacking pieces together until it works

	bindlist () {
	local flag_help flag_list flag_pretty
	local usage=(

	/*
	TRYING TO UNDERSTAND THIS:

	#1 - call twice nameit directly with expected results
	#2 - call twice nameit from caller1 with unexpected results!
	#3 - call nameit twice from caller2 which works
	#4 - the issue can also be avoided by calling 2nd time in another session
	*/

	-- setup

	drop schema if exists issue_repo cascade;
	create schema issue_repo; set schema 'issue_repo';

	create or replace procedure caller1 () as $proc$
	begin call nameit('t'); end;
	$proc$ language plpgsql;

	create or replace procedure nameit (target regclass) as $proc$
	begin raise notice '👻 target: %', target; end;
	$proc$ language plpgsql;

	-- see: https://postgresqlstan.github.io/postgresql/dynamic-sql/

	create schema examples;
	set schema 'examples';

	CREATE PROCEDURE create_text_table (fields INT, tablename TEXT = '_import')
	AS $$
	/*
	DESCRIPTION create text-only staging table
	ARGUMENTS fields: number of fields (columns)

	-- add to .psqlrc before changing PROMPT1
	-- restores PROMPT1 to original setting
	-- to use enter ":reset_prompt" (w/o quotes)
	SELECT FORMAT($$\set PROMPT1 '%s'$$, :'PROMPT1') AS reset_prompt \gset

	Summary of SUMMARY OF LESS COMMANDS

	-<letter> Toggle a command line option [see OPTIONS below].
	<number> Set N by entering number before command.

	MOVING
	--------------------- -----------------------------------------------------
	e j ↓ <return> Forward one line (or N lines).
	y k ↑ Backward one line (or N lines).
	f <space> Forward one window (or N lines).