SQL 不运行查询的 PostgreSQL 语法检查
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8271606/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL syntax check without running the query
提问by RobAu
I want to verify the syntax of files containing sql queries before they can be committed in my CVS project.
我想验证包含 sql 查询的文件的语法，然后才能在我的 CVS 项目中提交它们。
In order to do that, I have a commitinfo script, but I have trouble finding out if the sql commands are valid. psqldoes 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.
为了做到这一点，我有一个 commitinfo 脚本，但我无法确定 sql 命令是否有效。psql似乎没有dryrun模式，并且从语法（即在源代码中）构建我自己的postgresql-dialact测试器似乎很长一段时间。
The scripts may contain multiple queries, so an EXPLAINcannot be wrapped around them.
脚本可能包含多个查询，因此EXPLAIN不能将它们包裹起来。
Any hints?
任何提示？
回答by Mark Drago
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.
我最近编写了一个实用程序来静态检查 PostgreSQL 的 SQL 语法。它利用 ecpg（用于 postgres 的嵌入式 SQL C 预处理器）来检查 SQL 语法，因此它使用内置于 Postgres 本身的完全相同的解析器。
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:
您可以在 github 上查看：http: //github.com/markdrago/pgsanity。您可以浏览一下 README，以更好地了解它的工作原理并获取安装说明。这是一个如何使用 pgsanity 的简短示例：
$ 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"
回答by Erwin Brandstetter
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 dblinkcalls, or anything written to the file system or incremented sequences.
请注意，有些效果无法回滚，例如dblink调用，或写入文件系统或递增序列的任何内容。
I would advise cloning your database for testing purposes.
我建议克隆您的数据库以进行测试。
回答by karlgold
EXPLAIN (without ANALYZE) will parse the query and prepare an execution plan, without actually executing it.
EXPLAIN（不带 ANALYZE）将解析查询并准备执行计划，而不实际执行它。
https://www.postgresql.org/docs/current/static/sql-explain.html
https://www.postgresql.org/docs/current/static/sql-explain.html
回答by aleroot
I'm usually use Mimer online SQL validator, the only thing is that it check SQL syntax for standard SQL :
我通常使用Mimer 在线 SQL 验证器，唯一的事情是它检查标准 SQL 的 SQL 语法：
- SQL-92
- SQL-99
- SQL-03
- 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 ...
而不是特定于 PostgreSQL ......但是，如果您按照标准编写代码，您可以使用它并且它运行良好......
回答by Anshul Tiwari
A wonderful utility to verify SQL syntax: SQL Fiddle
验证 SQL 语法的绝妙实用程序：SQL Fiddle
Supports MySQL, Oracle, PostgreSQL, SQLite, MS SQL.
支持 MySQL、Oracle、PostgreSQL、SQLite、MS SQL。
回答by Rinat
Use this trick to validate PostgreSQL code syntax:
使用这个技巧来验证 PostgreSQL 代码语法：
DO $SYNTAX_CHECK$ BEGIN RETURN;
-- insert your SQL code here
END; $SYNTAX_CHECK$;
回答by shcherbak
You can run queries iside postgresql function and raise exception in the end. All changes will be rolled back. For example:
您可以在 postgresql 函数旁边运行查询并最终引发异常。所有更改都将回滚。例如：
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_checkextension (on github), the next incarnation of pgpsql_lint
另一个解决方案 -plpgsql_check扩展（在 github 上），pgpsql_lint 的下一个化身
回答by Jeff Wu
You could just wrap it in
SELECT 1 ( <your query> ) AS a WHERE 1 = 0;
你可以把它包起来
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
