PostgreSQL syntax check without running the query

I want to verify the syntax of files containing sql queries before they can be committed in my CVS project.

In order to do that, I have a commitinfo script, but I have trouble finding out if the sql commands are valid. psql does not seem to have a dryrun mode, and constructing my own postgresql-dialact tester from the grammar (that is in the source) seems like a long stretch.

The scripts may contain multiple queries, so an EXPLAIN cannot be wrapped around them.

Any hints?

Solution 1:

I recently wrote up a utility to statically check the syntax of SQL for PostgreSQL. It leverages ecpg, the embedded SQL C preproccessor for postgres, to check the SQL syntax, so it uses the exact same parser that is built in to Postgres itself.

You can check it out on github: http://github.com/markdrago/pgsanity. You can give the README a skim to get a better idea of how it works and to get directions for how to install it. Here's a short example of how pgsanity can be used:

$ pgsanity good1.sql good2.sql bad.sql
bad.sql: line 1: ERROR: syntax error at or near "bogus_token"

$ find -name '*.sql' | xargs pgsanity
./sql/bad1.sql: line 59: ERROR: syntax error at or near ";"
./sql/bad2.sql: line 41: ERROR: syntax error at or near "insert"
./sql/bad3.sql: line 57: ERROR: syntax error at or near "update"

Solution 2:

Use this trick to validate PostgreSQL code syntax:

DO $SYNTAX_CHECK$ BEGIN RETURN;
    -- insert your SQL code here
END; $SYNTAX_CHECK$;

Function is_sql(sql text)

Solution 3:

One way would be to put it into a transaction that you roll back at the end:

BEGIN;
<query>;
<query>;
<query>;
ROLLBACK;

Be aware that there are some effects that cannot be rolled back, like dblink calls, or anything written to the file system or incremented sequences.

I would advise cloning your database for testing purposes.