PostgreSqlStan

# tested on macOS 11.5.2 (Big Sur)

curl -OL https://www.openssl.org/source/openssl-3.0.0.tar.gz
tar -xvf openssl-3.0.0.tar.gz
cd openssl-3.0.0
./Configure # note the capital “C” - not an accident
make
make test
sudo make install

PostgreSqlStan

# tested on macOS 11.5.2 (Big Sur)

curl -OL https://tukaani.org/xz/xz-5.2.5.tar.gz
tar -xvf xz-5.2.5.tar.gz
cd xz-5.2.5/
./configure
make
make test  # optional
sudo make install

PostgreSqlStan

# tested on macOS 11.5.2 (Big Sur) after installing openssl and xz

curl -OL https://www.python.org/ftp/python/3.9.7/Python-3.9.7.tgz
tar -xvf Python-3.9.7.tgz
cd Python-3.9.7/
./configure
make
make test  # optional
sudo make install

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

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

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

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

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

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

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