PostgreSqlStan

# 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"

PostgreSqlStan

# 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=(

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

PostgreSqlStan

/*
   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

PostgreSqlStan

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;

PostgreSqlStan

-- 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)

PostgreSqlStan

CREATE PROCEDURE create_text_table (fields INT, tablename TEXT = '_import')
AS $$
/*
DESCRIPTION       create text-only staging table
  ARGUMENTS       fields: number of fields (columns)
                  tablename: [optional, default='_import'] name of table
*/
DECLARE
  fieldlist TEXT := '';
BEGIN

PostgreSqlStan

-- 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

PostgreSqlStan

           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).

PostgreSqlStan

-- view: ls (tested on postgres 14.1)
CREATE OR REPLACE VIEW ls AS
     SELECT
            N.nspname AS "schema",
            relname AS "relation",
            CASE c.relkind
                WHEN 'f' THEN 'fdw table'
                WHEN 'r' THEN 'table'
                WHEN 'v' THEN 'view'
                WHEN 'm' THEN 'm view' END AS "type",