PostgreSQL syntax check without running the query
SqlParsingPostgresqlSyntaxCvsSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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 - Sql
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 - Sql
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.
Solution 4 - Sql
EXPLAIN (without ANALYZE) will parse the query and prepare an execution plan, without actually executing it.
https://www.postgresql.org/docs/current/static/sql-explain.html
Solution 5 - Sql
I'm usually use Mimer online SQL validator, the only thing is that it check SQL syntax for standard SQL :
- SQL-92
- SQL-99
- SQL-03
and not specific for the PostgreSQL ... However if you write code following the standard you can use it and it work well ...
Solution 6 - Sql
A wonderful utility to verify SQL syntax: SQL Fiddle
Supports MySQL, Oracle, PostgreSQL, SQLite, MS SQL.
Solution 7 - Sql
You could just wrap it in
SELECT 1 ( <your query> ) AS a WHERE 1 = 0;
It'll fail on validation but it won't actually execute. Here's an example query plan:
Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false
Solution 8 - Sql
You can run queries iside postgresql function and raise exception in the end. All changes will be rolled back. For example:
CREATE OR REPLACE FUNCTION run_test(_sp character varying)
RETURNS character varying AS
$BODY$
BEGIN
EXECUTE 'SELECT ' || _sp;
RAISE EXCEPTION '#OK';
EXCEPTION
WHEN others THEN
RETURN SQLERRM;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Another sollution - plpgsql_check
extension (on github), the next incarnation of pgpsql_lint