PostgreSQL如何处理视图

SQL是一种查询语言，通常很容易阅读。但是，如果人们不能正确查询格式，SQL也将是一场噩梦。这就是为什么开发人员经常使用SQL美化器将错误的查询转换成格式正确的字符串的原因。网络上有各种工具可以实现这一目标。

可以仅使用PostgreSQL板载工具来实现相同的目的吗？答案是肯定的。这篇文章将向您展示如何实现这一目标。

PostgreSQL如何处理视图
在PostgreSQL中，视图不存储为纯文本。相反，它以机器可读的二进制格式存储在系统表中：

test=# \d pg_rewrite
Table "pg_catalog.pg_rewrite"
   Column   |    Type      | Collation | Nullable | Default
------------+--------------+-----------+----------+---------
 oid        | oid          |           | not null |
 rulename   | name         |           | not null |
 ev_class   | oid          |           | not null |
 ev_type    | "char"       |           | not null |
 ev_enabled | "char"       |           | not null |
 is_instead | boolean      |           | not null |
 ev_qual    | pg_node_tree | C         | not null |
 ev_action  | pg_node_tree | C         | not null |
 
Indexes:
  "pg_rewrite_oid_index" UNIQUE, btree (oid)
  "pg_rewrite_rel_rulename_index" UNIQUE, btree (ev_class, rulename)

pg_node_tree数据类型包含了这里所有的魔力。这很有意义，因为在查询执行期间可以更直接地访问数据。此外，它允许PostgreSQL轻松处理更改的列名等，而不会破坏视图。在内部，PostgreSQL仅使用对象ID，因此名称等都没有关系。重命名表或列不会使视图无效。

但是，如果使用\ d +：那么PostgreSQL如何以人类可读的格式提供视图的定义？答案是：PostgreSQL再次重新组合查询。此机制可用于格式化SQL字符串并将其转换为更漂亮的内容。

请记住：该机制从未打算这样做，但是它很好地说明了可以完成的事情。

将视图变成正确的查询字符串
pg_get_viewdef函数以字符串形式返回视图的定义。我们可以利用它。让我们看一下以下函数：

CREATE OR REPLACE FUNCTION format_sql(text)
RETURNS text AS
$$
   DECLARE
      v_ugly_string       ALIAS FOR $1;
      v_beauty            text;
      v_tmp_name          text;
   BEGIN
      -- let us create a unique view name
      v_tmp_name := 'temp_' || md5(v_ugly_string);
      EXECUTE 'CREATE TEMPORARY VIEW ' ||
      v_tmp_name || ' AS ' || v_ugly_string;
 
      -- the magic happens here
      SELECT pg_get_viewdef(v_tmp_name) INTO v_beauty;
 
      -- cleanup the temporary object
      EXECUTE 'DROP VIEW ' || v_tmp_name;
      RETURN v_beauty;
   EXCEPTION WHEN OTHERS THEN
      RAISE EXCEPTION 'you have provided an invalid string: % / %',
            sqlstate, sqlerrm;
   END;
$$ LANGUAGE 'plpgsql';

它的基本作用是将字符串转换为临时视图。然后，该视图再次变成字符串并删除。这是起作用的功能：

test=# SELECT format_sql('SELECT * FROM
                  pg_tables UNION
                    ALL SELECT * FROM
          pg_tables');
 
           format_sql
-------------------------------
SELECT pg_tables.schemaname,+
       pg_tables.tablename, +
       pg_tables.tableowner, +
       pg_tables.tablespace, +
       pg_tables.hasindexes, +
       pg_tables.hasrules, +
       pg_tables.hastriggers, +
       pg_tables.rowsecurity + 
 FROM  pg_tables +
 UNION ALL +
 SELECT pg_tables.schemaname,+
       pg_tables.tablename, +
       pg_tables.tableowner, +       
       pg_tables.tablespace, +
       pg_tables.hasindexes, +
       pg_tables.hasrules, +
       pg_tables.hastriggers, +
       pg_tables.rowsecurity +
 FROM  pg_tables;
(1 row)

如您所见，该字符串以正确的格式返回。PostgreSQL甚至会为您解析“ *”并将其转换为适当的列列表。您在这里看到的是psql添加了+，以防出现换行符。我们可以轻松地解决该问题，并告诉psql更改其行为：

test=# \pset format unaligned
Output format is unaligned.
 
test=# SELECT format_sql('SELECT * FROM
                 pg_tables UNION
               ALL
                SELECT * FROM
                    pg_tables');
 format_sql
SELECT pg_tables.schemaname,
       pg_tables.tablename,
       pg_tables.tableowner,
       pg_tables.tablespace,
       pg_tables.hasindexes,
       pg_tables.hasrules,
       pg_tables.hastriggers,
       pg_tables.rowsecurity
 FROM  pg_tables
 UNION ALL
 
 SELECT pg_tables.schemaname,
       pg_tables.tablename,
       pg_tables.tableowner,
       pg_tables.tablespace,
       pg_tables.hasindexes,
       pg_tables.hasrules,
       pg_tables.hastriggers,
       pg_tables.rowsecurity
 FROM pg_tables;
(1 row)

该解决方案的局限性
当然，这种方法有两个限制。首先，该功能仅在您提供实际上有可能被执行的查询时才起作用。如果表不存在，将引发错误：

test=# SELECT format_sql('SELECT * FROM not_there');
ERROR: you have provided an invalid string: 42P01 / relation "not_there" does not exist
CONTEXT: PL/pgSQL function format_sql(text) line 19 at RAISE

这可以看作是功能还是问题–取决于您要实现的目标。除此之外，评论将被删除。无法阻止这种情况的发生。