如何防止PostgreSQL更改格式正确的SQL定义 [英] How to prevent PostgreSQL from altering my nicely formatted SQL definitions

问题描述

我注意到，在为PostgreSQL编写SQL之后，稍后查看SQL定义时，似乎数据库改变了格式化SQL的方式。例如，我的前导逗号被移到了后面，我的制表符被更改了，等等。

I've noticed that after writing SQL for PostgreSQL, when I view the SQL definition later it seems the database changed the way I formatted the SQL. For example, my leading commas are moved to the back, my tabbing is altered, etc.

有没有办法防止这种情况发生？

Is there a way to prevent this?

推荐答案

这是一种误解。 Postgres不会更改格式。原始SQL字符串只是根本不存储 。

That's a misunderstanding. Postgres does not "alter the format". The original SQL string is just not stored at all.

查询已解析并重写，具体取决于一种查询，将采取措施。 例如，当您创建视图时，结果将存储在系统目录中，大部分为 pg_class 和 pg_rewrite 。

The query is parsed and rewritten and depending on the kind of query, action is taken. For instance, when you create view, the results are stored in system catalogs, mostly pg_class and pg_rewrite.

您还必须注意，所有标识符都是在数据库对象创建时解析的，采用当前的 search_path 和可见性（早期绑定）。如果环境以任何方式更改，则相同的查询字符串可能在以后表示不同的含义。

You also must be aware that all identifiers are resolved at create time of a database object, taking the current search_path and visibility into account (early binding). The same query string can mean something different later, if the environment changes in any way.

稍后您将看到的是重新设计的版本从这些条目构建。
有一些内置函数对此有帮助，但在很大程度上取决于客户端如何格式化反向工程的SQL代码。

What you see later is a re-engineered version built from these entries. There are some built-in functions to help with that, but it's largely up to the client how to format reverse-engineered SQL code.

函数 是部分例外。函数主体以字符串形式传递，并按原样保存 ，并且（目前）仅使用表面语法测试和对象验证。如果您想要一种快速的方法来保存 Postgres中的查询，包括其格式，例如，可以使用PL / pgSQL函数。

Functions are an exception (partly). The function body is passed as string and saved as is, exactly as passed, with (currently) only superficial syntax testing and validation of objects. If you want a quick way to "save" a query including its format inside Postgres, you can use a PL/pgSQL function, for instance.

通常的方法是拥有一个独立的代码存储库，例如 @Mike已经建议-或不要在意格式太多。

The general approach is to have an independent code repository like @Mike already suggested - or not to care about the format too much.

这篇关于如何防止PostgreSQL更改格式正确的SQL定义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！