Dry-running psql scripts

Question

Psql script files may (and typically do) contain psql-specific instructions, includes and variable substitutions that are resolved client-side. I am looking for a generic way of getting the sequence of SQL commands as they would be sent to the PostgreSQL server by psql, after all client-side processing if done. Furthermore, this should be available offline. Therefore, solutions involving socket-sniffing live runs or using rolled-back transactions are excluded. Would someone know of a way that this can be achieved?

Edit: Because of psql conditional meta-commands (\if, ...) that can depend on the result of the evaluation of previous SQL statements, I understand that it is not possible to have complete resolution in all cases (which may be why psql doesn't provide a dry-run option). A valid solution to this question could still simply ignore psql's conditionals, though.

Answer 1

This Bash script expands psql's \include (\i) and \include_relative (\ir) meta-commands in-place and outputs a single resolved stream to stdout. As I'm posting it, I'm spotting a couple of areas that could be improved upon (mostly merging the two read -r line instructions), but the script does nonetheless work fine as-is (as far as I tested it):

#!/bin/bash

incl='*([[:space:]])\\@(include?(_relative)|i?(r))+([[:space:]])*'

# Recursively process single \include (\i) or \include_relative (\ir) meta-command
process() {
    # If current line is an '\include' ('\i') or '\include_relative' ('\ir') meta-command, substitute with file contents
    if [[ "$1" == $incl ]]; then
        # Read included file's name
        filename="$(echo "$1" | awk '{print $2}')"
        # Read included file's contents and process each line recursively
        while IFS= read -r line; do
            process "$line"
        done < "$filename"
    # Else, echo the line
    else
        echo "$1"
    fi
}

# Output usage information with '--help' or '-h' switch
if [[ ' --help -h ' =~ " $1 " ]]; then
    echo "USAGE: pg_psqlexpand [workdir] [-h|--help]"
    echo
    echo "Expands in place `\include' (`\i') and `\include_relative' (`\ir') meta-commands in psql scripts."
    echo
    echo "Connect pipe or use redirection for I/O."
    echo "Paths are resolved relative to `workdir' (defaults to current working directory)."
    exit 0
else
    # If working directory is specified, cd into it (defaults to '.')
    cd "${1:-.}"
    while read -r line; do
        process "$line"
    done
fi

This allows for merging complex, multi-file scripts into a single file for inspection. It also allows one to pipe in-place such scripts to psql running as the postgres superuser from locations that postgres has no access to (typically locations in the caller's home directory):

## Merge into single file
$ pg_psqlexpand < entry_point.psql > merged.psql

## Pipe to psql running as postgres
$ pg_psqlexpand < entry_point.psql | sudo -iu postgres psql

I've been using the above script for almost a week so far and it has been working fine. The only drawback that I ran into is that debug information regarding file names and line numbers is lost in the process. As a work-around, I add these meta-commands at the top of my psql entry-point, giving me enough information for debugging without much difficulty:

-- Error handling
\set VERBOSITY verbose
\set SHOW_CONTEXT errors
\set ON_ERROR_STOP on

It should be noted that this script is not syntax-aware and will therefore substitute any include-like sub-string that could appear inside strings or a double-quoted literal identifiers.