Include, Variables, and Debugging

Goal: Leverage \include and \set to write more modular queries and be able to debug them.

It debugs the query, Dee Dee

Setup

See the SQL Testing Framework 101 tutorial for database setup instructions to follow along on your computer.

It's Saturday evening, and you're enjoying the aurora borealis from your living room when you see a Slack message from Sandy Holcombs, VP of Sales:

> Sandy Holcombs is typing...
> đź‘‹ HI HI HIđź‘‹ On Friday morning the CEO asked me what the percent of rentals for his favorite films were handled by each sales associate.  I need this from you now, and it better be right. OK? Thank you, you're the best!!

Oh well, 🤦. I'm sure the aurora will still be there when you finish this request for Holcombs. Like everything you've been asked for (except that recursive Kevin Bloom thing), this should be simple enough.

Get to work

What a moment, Holcombs didn't say which films were the CEOs favorites, and she's đź”´ in Slack now. That's OK we can handle that with a variable.

\include '/queries/utils/percent.sql'
\set film_name 'Agent Truman'
WITH film_by_staff AS (
  SELECT
     COUNT(film.title) AS staff_rental
    ,staff.first_name
    ,staff.last_name
  FROM rental
  JOIN inventory USING(inventory_id)
  JOIN film USING(film_id)
  JOIN staff USING(staff_id)
  WHERE film.title = :'film_name'
  GROUP BY staff_id
), film_total AS (
  SELECT
    COUNT(*) AS total_rental
  FROM rental
  JOIN inventory USING(inventory_id)
  JOIN film USING(film_id)
  WHERE film.title = :'film_name'
), summary AS (
  SELECT
    *
  FROM film_by_staff
  FULL JOIN film_total ON TRUE
)

SELECT
   first_name
  ,last_name
  ,staff_rental
  ,total_rental
  ,PERCENT(staff_rental, total_rental) AS pct_of_rental
FROM summary
;

This query makes use of two great PSQL features, \include and \set. \include let's us include other files as if they'd been written in-line. SQL Testing Framework includes some utilities, like a PERCENTAGE function in queries/utils that are meant to be used by \includeing them.

\set defines a variable, that's used later by with a :. In this case :'film_name', which will let us put in a placeholder until Holcombs can tell us what the CEO's favorite films are.

This looks good, let's test it:

\set query '/queries/rentals_of_ceo_favs.sql'
:setup_test
WITH text AS (
  SELECT 'the percentages add up to 100 pct' AS value
), expect AS (
  SELECT 100 AS value
), actual AS (
  SELECT
    SUM(pct_of_rental) AS value
  FROM "/queries/rentals_of_ceo_favs.sql"
)
:evaluate_test
:cleanup_test

Let's run the test:

$ ./runner.sh rentals_of_ceo_favs.sql
 â—Źpsql:rentals_of_ceo_favs.sql:4: ERROR:  syntax error at or near "CREATE"
LINE 4:         CREATE TEMP TABLE "prechecks" (value BOOLEAN)
                ^

Hmm, an error. How can we figure out what the problem is? SQL Testing Framework exposes and environment variable that can help debug errors. We can define STF_ECHO='queries' to print the output of our test queries, which will help figure out what's wrong.

$ export STF_ECHO='queries'; ./runner.sh rentals_of_ceo_favs.sql
 â—ŹCREATE OR REPLACE FUNCTION PERCENT(numerator anycompatible, denominator anycompatible)
RETURNS numeric LANGUAGE plpgsql AS $$
BEGIN
    RETURN ROUND(numerator::numeric / denominator::numeric * 100, 2);
END $$;
BEGIN;
CREATE OR REPLACE FUNCTION PERCENT(numerator anycompatible, denominator anycompatible)
RETURNS numeric LANGUAGE plpgsql AS $$
BEGIN
    RETURN ROUND(numerator::numeric / denominator::numeric * 100, 2);
END $$;
CREATE TEMP TABLE "/queries/rentals_of_ceo_favs.sql"
            ON COMMIT DROP
            AS
        CREATE TEMP TABLE "prechecks" (value BOOLEAN)
          ON COMMIT DROP
        ;
psql:rentals_of_ceo_favs.sql:4: ERROR:  syntax error at or near "CREATE"
LINE 4:         CREATE TEMP TABLE "prechecks" (value BOOLEAN)

Well that's not going to work. It doesn't look like our query was loaded into the temp table (and the FUNCTION was defined twice).

The problem here is that the \include and \set don't really belong in the query file. If we were using the query interactively, the best way would be to \include and \set the in interactive session, that would be more flexible.

postgres=# \include '/queries/utils/percent.sql'
CREATE FUNCTION
postgres=# \set film_name 'Agent Truman'
postgres=# \include rentals_of_ceo_favs.sql
 first_name | last_name | staff_rental | total_rental | pct_of_rental
------------+-----------+--------------+--------------+---------------
 Mike       | Hillyer   |           11 |           21 |         52.38
 Jon        | Stephens  |           10 |           21 |         47.62
(2 rows)

Let's remove those from the query file and put them in the test file instead:

-- \include '/queries/utils/percent.sql'
-- \set film_name 'Agent Truman'
WITH film_by_staff AS (
  SELECT
     COUNT(film.title) AS staff_rental
    ,staff.first_name
    ,staff.last_name
  FROM rental
  JOIN inventory USING(inventory_id)
  JOIN film USING(film_id)
  JOIN staff USING(staff_id)
  WHERE film.title = :'film_name'
  GROUP BY staff_id
), film_total AS (
  SELECT
    COUNT(*) AS total_rental
  FROM rental
  JOIN inventory USING(inventory_id)
  JOIN film USING(film_id)
  WHERE film.title = :'film_name'
), summary AS (
  SELECT
    *
  FROM film_by_staff
  FULL JOIN film_total ON TRUE
)

SELECT
   first_name
  ,last_name
  ,staff_rental
  ,total_rental
  ,PERCENT(staff_rental, total_rental) AS pct_of_rental
FROM summary
;
\set film_name 'Agent Truman'
\include '/queries/utils/percent.sql'
\set query '/queries/rentals_of_ceo_favs.sql'
:setup_test
WITH text AS (
  SELECT 'the percentages add up to 100 pct' AS value
), expect AS (
  SELECT 100 AS value
), actual AS (
  SELECT
    SUM(pct_of_rental) AS value
  FROM "/queries/rentals_of_ceo_favs.sql"
)
:evaluate_test
:cleanup_test

And run the test file again with queries echo on:

$ export STF_ECHO='queries'; ./runner.sh rentals_of_ceo_favs.sql
 â—ŹCREATE OR REPLACE FUNCTION PERCENT(numerator anycompatible, denominator anycompatible)
RETURNS numeric LANGUAGE plpgsql AS $$
BEGIN
    RETURN ROUND(numerator::numeric / denominator::numeric * 100, 2);
END $$;
BEGIN;
CREATE TEMP TABLE "/queries/rentals_of_ceo_favs.sql"
            ON COMMIT DROP
            AS

WITH film_by_staff AS (
  SELECT
     COUNT(film.title) AS staff_rental
    ,staff.first_name
    ,staff.last_name
  FROM rental
  JOIN inventory USING(inventory_id)
  JOIN film USING(film_id)
  JOIN staff USING(staff_id)
  WHERE film.title = 'Agent Truman'
  GROUP BY staff_id
), film_total AS (
  SELECT
    COUNT(*) AS total_rental
  FROM rental
  JOIN inventory USING(inventory_id)
  JOIN film USING(film_id)
  WHERE film.title = 'Agent Truman'
), summary AS (
  SELECT
    *
  FROM film_by_staff
  FULL JOIN film_total ON TRUE
)

SELECT
   first_name
  ,last_name
  ,staff_rental
  ,total_rental
  ,PERCENT(staff_rental, total_rental) AS pct_of_rental
FROM summary
          ;
CREATE TEMP TABLE "prechecks" (value BOOLEAN)
          ON COMMIT DROP
        ;
WITH text AS (
  SELECT 'the percentages add up to 100 pct' AS value
), expect AS (
  SELECT 100 AS value
), actual AS (
  SELECT
    SUM(pct_of_rental) AS value
  FROM "/queries/rentals_of_ceo_favs.sql"
)
        INSERT INTO stf.test_results (
           run_id
          ,filename
          ,actual
          ,expect
          ,did_pass
          ,text
        )
        SELECT
           1661659229 AS run_id
          ,'rentals_of_ceo_favs.sql' AS filename
          ,actual.value AS actual
          ,expect.value AS expect
          ,actual.value IS NOT DISTINCT FROM expect.value AS did_pass
          ,text.value AS text
        FROM actual
        FULL JOIN expect    ON TRUE
        FULL JOIN text      ON TRUE
        ;
CREATE TEMP TABLE IF NOT EXISTS "prechecks" (value BOOLEAN)
        ;
WITH latest_test_run AS (
          SELECT * FROM stf.test_results
          ORDER BY id DESC
          LIMIT 1
        ), aggregated_prechecks AS (
          SELECT
             latest_test_run.id AS id
             ,ARRAY_REMOVE(ARRAY_AGG(prechecks.value), NULL) AS value
          FROM latest_test_run
          FULL JOIN "prechecks" ON TRUE
          GROUP BY latest_test_run.id
        )
        UPDATE stf.test_results
        SET precheck = aggregated_prechecks.value
        FROM aggregated_prechecks
        WHERE aggregated_prechecks.id = stf.test_results.id
        ;
COMMIT;

 actual | expect | did_pass |               text
--------+--------+----------+-----------------------------------
 100.00 | 100    | true     | the percentages add up to 100 pct
(1 row)

Much better. When things aren't working as expected STF_ECHO='queries' is a very helpful way to figure out what's going wrong.

Bonus

A test like this also a great example of when to use a precheck. What if there were no rentals for Agent Truman? With a simple precheck our final test looks likes this:

\set film_name 'Agent Truman'
\include '/queries/utils/percent.sql'
\set query '/queries/rentals_of_ceo_favs.sql'
:setup_test
WITH text AS (
  SELECT 'the percentages add up to 100 pct' AS value
), precheck_there_are_rentals AS (
  INSERT INTO :"prechecks" (value)
  SELECT COALESCE(SUM(staff_rental) > 0, FALSE)
  FROM "/queries/rentals_of_ceo_favs.sql"
), expect AS (
  SELECT 100 AS value
), actual AS (
  SELECT
    SUM(pct_of_rental) AS value
  FROM "/queries/rentals_of_ceo_favs.sql"
)
:evaluate_test
:cleanup_test

$ ./runner.sh rentals_of_ceo_favs.sql
 â—Ź
 actual | expect | did_pass |               text
--------+--------+----------+-----------------------------------
 100.00 | 100    | true     | the percentages add up to 100 pct
(1 row)

Now we can get back to the aurora and when Holcombs back online and let's us know what films the CEO likes we can plug those in and also, as we were warned "better be right." Just another Saturday at StreamBuster!

The code for this example is available on Github