Script for "substitute a variable" blog post

gen tables on oracle.sql

CREATE TABLE orders AS
SELECT level AS order_id,
       trunc(dbms_random.value(1, 100)) AS product_id,
       date '2024-01-01' + dbms_random.value * (date '2024-12-31' - date '2024-01-01') AS order_date,
       round(dbms_random.value(1, 1000), 2) AS amount
  FROM dual CONNECT BY level <= 1000;
 
CREATE TABLE products AS
SELECT distinct product_id FROM orders;

gen tables on postgresql.sql

CREATE TABLE orders AS
SELECT i AS order_id,
       i % 100 + 1 AS product_id,
       date '2024-01-01' + (random() * (date '2024-12-31' - date '2024-01-01')) * interval '1 day' AS order_date,
       round(random()::numeric * 1000, 2) AS amount
  FROM generate_series(1,1000) i;

CREATE TABLE products AS
SELECT distinct product_id FROM orders;

psql-report-01.sql

-- psql-report-01.sql
\pset footer off

SELECT COALESCE(SUM(amount), 0) AS total_amount
  FROM orders
  JOIN products USING (product_id)
 WHERE product_id = :product_id
   AND order_date BETWEEN :'start_date'::date
                      AND :'end_date'::date;

psql-report-02-wrong.sql

-- psql-report-02-wrong.sql
DO $$
DECLARE
  p_id    products.product_id%TYPE;
  p_sum   numeric;
  p_start date := :'start_date'::date;
  p_end   date := :'end_date'::date;
  
BEGIN
  SELECT product_id INTO STRICT p_id
    FROM products
   WHERE product_id = :product_id;
   
  SELECT COALESCE(SUM(amount), 0) INTO p_sum
    FROM orders
   WHERE product_id = p_id
     AND order_date BETWEEN p_start AND p_end;

  RAISE NOTICE 'Total amount: %', p_sum;
  
EXCEPTION
  WHEN no_data_found THEN
    RAISE NOTICE 'Product % does not exist', :product_id;
END;
$$;

psql-report-02.sql

-- psql-report-02.sql
\pset footer off

SELECT NOT EXISTS(
  SELECT product_id
    FROM products
   WHERE product_id = :product_id
) AS unknown_product \gset

\if :unknown_product
  \echo 'Product' :product_id 'does not exist'
  \quit
\endif

SELECT COALESCE(SUM(amount), 0) AS total_amount
  FROM orders
  JOIN products USING (product_id)
 WHERE product_id = :product_id
   AND order_date BETWEEN :'start_date'::date
                      AND :'end_date'::date;

psql-report-03.sql

-- psql-report-03.sql
\set QUIET on

SET my.product_id = :product_id;
SET my.start_date = :'start_date';
SET my.end_date   = :'end_date';

DO $$
DECLARE
  my_id        int := current_setting('my.product_id')::int;
  p_id         products.product_id%TYPE;
  p_sum        numeric;
  p_prev_sum   numeric;
  p_start      date := current_setting('my.start_date')::date;
  p_end        date := current_setting('my.end_date')::date;
  p_prev_start date := p_start - interval '1 day' * (p_end - p_start);
  p_prev_end   date := p_start - interval '1 day';
  
  c_orders CURSOR (v_start date, v_end date) IS
    SELECT COALESCE(SUM(amount), 0)
      FROM orders
     WHERE product_id = p_id
       AND order_date BETWEEN v_start AND v_end;
  
BEGIN
  CREATE FUNCTION pg_temp.score(p_sum numeric, p_prev_sum numeric) 
  RETURNS numeric LANGUAGE plpgsql AS $func$
    BEGIN
      RETURN ROUND((p_sum - p_prev_sum) / p_prev_sum * 100, 2);
    EXCEPTION
      WHEN division_by_zero THEN
        RETURN NULL;
    END;
  $func$;
  
  SELECT product_id INTO STRICT p_id
    FROM products
   WHERE product_id = my_id;
   
  OPEN c_orders(p_start, p_end);
    FETCH c_orders INTO p_sum;
  CLOSE c_orders;
  
  OPEN c_orders(p_prev_start, p_prev_end);
    FETCH c_orders INTO p_prev_sum;
  CLOSE c_orders;
  
  RAISE NOTICE 'Total amount: %', p_sum;
  RAISE NOTICE 'Performance score: %', pg_temp.score(p_sum, p_prev_sum);
   
EXCEPTION
  WHEN no_data_found THEN
    RAISE NOTICE 'Product % does not exist', my_id;
END;
$$;

psql-report-04.sql

-- psql-report-04.sql
\pset footer off

SELECT NOT EXISTS(
  SELECT product_id
    FROM products
   WHERE product_id = :product_id
) AS unknown_product \gset

\if :unknown_product
  \echo 'Product' :product_id 'does not exist'
  \quit
\endif

SELECT :'end_date'::date - :'start_date'::date AS days \gset
SELECT :'start_date'::date - :days AS prev_start \gset

WITH periods AS (
  SELECT d AS start_date, d + :days * '1 day'::interval AS end_date
    FROM generate_series(:'prev_start'::date, 
                         :'start_date'::date, 
                         :days * '1 day'::interval) AS s(d)
), amounts AS (
  SELECT start_date, COALESCE(SUM(amount), 0) AS total_amount, 
         LAG(SUM(amount), 1) OVER (ORDER BY start_date) AS prev_total_amount
    FROM orders
    JOIN periods ON order_date BETWEEN start_date AND end_date
   WHERE product_id = :product_id
   GROUP BY start_date
)
SELECT total_amount, 
       ROUND((total_amount - prev_total_amount) / 
              prev_total_amount * 100, 2) AS performance_score
  FROM amounts 
 WHERE prev_total_amount IS NOT NULL;

sqlplus-report-01.sql

-- sqlplus-report-01.sql
CONNECT user/password@database

DEF product_id = '&1'
DEF start_date = '&2'
DEF end_date   = '&3'

SELECT NVL(SUM(amount), 0) AS total_amount
  FROM orders
  JOIN products USING (product_id)
 WHERE product_id = &product_id
   AND order_date BETWEEN TO_DATE('&start_date', 'YYYY-MM-DD')
                      AND TO_DATE('&end_date', 'YYYY-MM-DD');

QUIT

sqlplus-report-02.sql

-- sqlplus-report-02.sql
CONNECT user/password@database

DEF product_id = '&1'
DEF start_date = '&2'
DEF end_date   = '&3'

SET serveroutput ON
SET feedback OFF
SET verify OFF

DECLARE
  p_id    products.product_id%TYPE;
  p_sum   NUMBER;
  p_start DATE := TO_DATE('&start_date', 'YYYY-MM-DD');
  p_end   DATE := TO_DATE('&end_date', 'YYYY-MM-DD');
  
BEGIN
  SELECT product_id INTO p_id
    FROM products
   WHERE product_id = &product_id;
   
  SELECT NVL(SUM(amount), 0) INTO p_sum
    FROM orders
   WHERE product_id = p_id
     AND order_date BETWEEN p_start AND p_end;
      
  DBMS_OUTPUT.PUT_LINE('Total amount: ' || p_sum);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Product ' || &product_id || ' does not exist');
END;
/

QUIT

sqlplus-report-03.sql

-- sqlplus-report-03.sql
CONNECT user/password@database

DEF product_id = '&1'
DEF start_date = '&2'
DEF end_date   = '&3'

SET serveroutput ON
SET feedback OFF
SET verify OFF

DECLARE
  p_id         products.product_id%TYPE;
  p_sum        NUMBER;
  p_prev_sum   NUMBER;
  p_start      DATE := TO_DATE('&start_date', 'YYYY-MM-DD');
  p_end        DATE := TO_DATE('&end_date', 'YYYY-MM-DD');
  p_prev_start DATE := p_start - (p_end - p_start);
  p_prev_end   DATE := p_start - 1;

  CURSOR c_orders (v_start DATE, v_end DATE) IS
    SELECT SUM(amount) AS total_amount
      FROM orders
     WHERE product_id = p_id
       AND order_date BETWEEN v_start AND v_end;

  FUNCTION score(p_sum NUMBER, p_prev_sum NUMBER)
  RETURN NUMBER IS
    v_score NUMBER;
  BEGIN
    RETURN ROUND((p_sum - p_prev_sum) / p_prev_sum * 100, 2);
  EXCEPTION
    WHEN ZERO_DIVIDE THEN
      RETURN NULL;
  END;

BEGIN
  SELECT product_id INTO p_id
    FROM products
   WHERE product_id = &product_id;
   
  OPEN c_orders(p_start, p_end);
    FETCH c_orders INTO p_sum;
  CLOSE c_orders;
      
  OPEN c_orders(p_prev_start, p_prev_end);
    FETCH c_orders INTO p_prev_sum;
  CLOSE c_orders;
      
  DBMS_OUTPUT.PUT_LINE('Total amount: ' || p_sum);
  DBMS_OUTPUT.PUT_LINE('Performance score: ' || score(p_sum, p_prev_sum));

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Product ' || &product_id || ' does not exist');
END;
/

QUIT