-- set font to: FreeMono 20
-- run with:
-- PAGER='less --prompt "" --raw-control-chars --status-column --chop-long-lines' LESSOPEN='|-(tput setaf 3; tput bold; echo; cat %s)' blogsql test -e -f /wwwmain/writings/pgsql/multi-model.sql

\pset pager always
\pset null ''
\pset columns 999

\set head `tput setaf 2; tput bold`
\set reset `tput sgr0`

\! tput reset
\echo :head'\nCreate function to generate random text\n':reset
CREATE OR REPLACE FUNCTION random_text(integer)
RETURNS text
AS $$
    SELECT btrim(regexp_replace(array_to_string(
        ARRAY (
            SELECT substring('aaabcdeeefghiiijklmnooopqrstuuuvwxyz          '
                             FROM (random() * 46)::int FOR 1)
            FROM generate_series(1, $1)), ''),
        ' [^ ]?  *', ' ', 'g'))
$$ LANGUAGE sql;
\echo
\echo :head'Script at https://momjian.us/main/writings/pgsql/multi-model.sql':reset
\prompt '\n' dummy
\! tput reset

\echo :head'\nCreate product table\n':reset
DROP TABLE IF EXISTS product;
\echo

CREATE TABLE product (
    product_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    cost NUMERIC(8, 2) CHECK (cost >= 0)
);
\prompt '\n' dummy
\! tput reset

\echo :head'\nAdd 10k products\n':reset
INSERT INTO product (name, description, cost)
SELECT	initcap(random_text(15)),
	random_text(27),
	log(random() + 1) * 1000
FROM	generate_series(1, 10000) AS f (x);
\prompt '\n' dummy
\! tput reset

\echo :head'\nProduct table\n':reset
SELECT *
FROM product 
ORDER BY product_id
LIMIT 10;
\! tput reset

\echo :head'\nAdd three columns atomically to the product table\n':reset
BEGIN WORK;
ALTER TABLE product ADD COLUMN weight_kg NUMERIC(6,2) CHECK (weight_kg >= 0);
ALTER TABLE product ADD COLUMN country CHAR(2) DEFAULT 'US' CHECK (country ~ '^[A-Z][A-Z]$');
ALTER TABLE product ADD COLUMN producer TEXT;
COMMIT WORK;
\prompt '\n' dummy
\! tput reset

\echo :head'\nNew columns\n':reset
SELECT *
FROM product 
ORDER BY product_id
LIMIT 10;
\! tput reset

\echo :head'\nPopulate new columns\n':reset
UPDATE product SET weight_kg = log(random() + 1) * 20;
UPDATE product SET producer = initcap(random_text(20));
\prompt '\n' dummy
\! tput reset

\echo :head'\nPopulated new columns\n':reset
SELECT *
FROM product 
ORDER BY product_id
LIMIT 10;
\! tput reset

\echo :head'\nAdding rarely-used columns is cumbersome\n':reset
BEGIN WORK;
ALTER TABLE product ADD COLUMN color TEXT;
ALTER TABLE product ADD COLUMN supported_voltage INTEGER[]; -- array of integers
ALTER TABLE product ADD COLUMN certification TEXT[]; -- array of text strings
ALTER TABLE product ADD COLUMN flamable BOOLEAN;
ABORT WORK;
\prompt '\n' dummy
\! tput reset

\echo :head'\nAdd JSONB field to store arbitrary fields\n':reset
ALTER TABLE product ADD COLUMN other JSONB;

UPDATE product SET other = CAST(
	'{"' || random_text(3) || '": "' || random_text(5) || '",'
	'"' || random_text(3) || '": "' || random_text(10) || '"}'
	AS JSONB);
\prompt '\n' dummy
\! tput reset

\echo :head'\nPopulated JSONB column\n':reset
SELECT product_id, name, cost, weight_kg, other
FROM product 
ORDER BY product_id
LIMIT 10;
\! tput reset

\echo :head'\nReference JSBONB columns using relational column restriction\n':reset
SELECT name, cost, weight_kg, other
FROM product
WHERE weight_kg <= 0.02
ORDER BY product_id
LIMIT 10;
\! tput reset

\echo :head'\nReference JSBONB value using key/text restriction\n':reset
SELECT name, other->>'e' AS "e", other
FROM product
WHERE other->>'e' != ''
ORDER BY product_id
LIMIT 10;
\! tput reset

\echo :head'\nReference JSBONB value using key existance restriction\n':reset
SELECT name, other->>'e' AS "e", other
FROM product
WHERE other ? 'e'
ORDER BY product_id
LIMIT 10;
\! tput reset

\echo :head'\nJSON path query\n':reset
SELECT name, other->>'e' AS "e", other
FROM product
WHERE jsonb_path_exists(other, '$.e ? (@ starts with "i")')
ORDER BY product_id
LIMIT 10;
\! tput reset

\echo :head'\nCreate key/JSONB table\n':reset
DROP TABLE IF EXISTS product_json;
CREATE TABLE product_json (
    product_id SERIAL PRIMARY KEY,
    detail JSONB
);
\prompt '\n' dummy
\! tput reset

\echo :head'\nPopulate key/JSONB table\n':reset
INSERT INTO product_json
SELECT product_id, row_to_json(x.*) AS detail
FROM product,
     LATERAL (SELECT name, cost, weight_kg) AS x
ORDER BY product_id
LIMIT 10;
\! tput reset

\echo :head'\nKey/JSONB table\n':reset
SELECT *
FROM product_json
ORDER BY product_id
LIMIT 10;
\! tput reset

\echo :head'\nAdd another JSON key/value\n':reset
UPDATE product_json SET detail = detail || CAST(
	'{"' || random_text(3) || '": ' || text(int4(random() * 10)) || '}'
	AS JSONB);
\prompt '\n' dummy
\! tput reset

\echo :head'\nNew keys/values\n':reset
SELECT *
FROM product_json
ORDER BY product_id
LIMIT 10;
\! tput reset

\echo :head'\nRemove JSONB column\n':reset
ALTER TABLE product DROP COLUMN other;
\prompt '\n' dummy
\! tput reset

\echo :head'\nFull text search, with language-specific stemming\n':reset
SELECT description
FROM product
WHERE to_tsvector('english', description) @@ to_tsquery('e & u')
ORDER BY random() -- return random rows
LIMIT 10;
\! tput reset

\echo :head'\nCreate full text search index\n':reset
CREATE INDEX product_desc_idx_ts ON product USING GIN (to_tsvector('english', description));
\prompt '\n' dummy
\! tput reset

\echo :head'\nShow index being used\n':reset
EXPLAIN SELECT description
FROM product
WHERE to_tsvector('english', description) @@ to_tsquery('e & u')
ORDER BY random() -- return random rows
LIMIT 10;
\! tput reset

\echo :head'\nComplex index support\n':reset
SELECT amname
FROM pg_am
ORDER BY 1;
\! tput reset

\echo :head'\nAdd sub-product column\n':reset
ALTER TABLE product ADD COLUMN sub_product_id INTEGER;
\prompt '\n' dummy
\! tput reset

\echo :head'\nPopulate sub-product column\n':reset
UPDATE product SET sub_product_id = int4(random() * 10000);
\prompt '\n' dummy
\! tput reset

\echo :head'\nRetrieve sub-products\n':reset
WITH RECURSIVE sub (product_id, name, sub_product_id) AS
(
        SELECT product.product_id, product.name, product.sub_product_id
	FROM product
	WHERE product_id = 1
        UNION
        SELECT product.product_id, product.name, product.sub_product_id
        FROM sub JOIN product ON (sub.sub_product_id = product.product_id)
)
SELECT * FROM sub
WHERE product_id != sub_product_id
LIMIT 10;
\! tput reset

\echo :head'\nAdd earthdistance extension\n':reset
CREATE EXTENSION IF NOT EXISTS earthdistance CASCADE;
\prompt '\n' dummy
\! tput reset

\echo :head'\nAdd product location\n':reset
ALTER TABLE product ADD COLUMN location POINT;
\prompt '\n' dummy
\! tput reset

\echo :head'\nPopulate product location\n':reset
UPDATE product SET location = CAST('(' || random() * 180 - 90 || ', ' || random() * 360 - 180 || ')' AS POINT);
\prompt '\n' dummy
\! tput reset

\echo :head'\nproduct table with location\n':reset
SELECT product_id, name, description, location
FROM product 
ORDER BY product_id
LIMIT 10;
\! tput reset

\echo :head'\nProducts nearest to me\n':reset
SELECT product_id, name, description, location, round(location <@> '(-75.400861, 40.003179)') AS distance_miles
FROM product 
ORDER BY location <@> '(-75.400861, 40.003179)'
LIMIT 10;
\! tput reset

\echo :head'\nhttps://bmomjian.carto.com/builder/9a574e2b-e840-489c-a17f-3c516ac50717 ;  increase browser font\n':reset
