描述PostgreSQL中的结果集？ [英] Describe result set in PostgreSQL?

问题描述

在Microsoft Sql Server中，有一个proc sys.sp_describe_first_result_set ，它使用任意sql查询字符串并返回描述结果集中类型的数据，分别为它要执行。注意，这是描述复杂查询而不是表的结果。 MSDN参考

In Microsoft Sql Server, there is a proc sys.sp_describe_first_result_set which takes an arbitrary sql query string and returns data describing the types that would be in the result set, were it to be executed. Note, this is describing the results of a complex query, not a table. MSDN reference

Postgres有类似的东西吗？

Is there anything similar for Postgres?

推荐答案

有在协议级别-以描述消息rel = nofollow>扩展的查询协议。

There is at the protocol level - in the form of the Describe message in the extended query protocol.

我不知道任意SQL字符串的任何SQL级等效项。好主意；在pgsql-general上提高它。我怀疑这很容易实现。

I'm not aware of any SQL-level equivalent for arbitrary SQL strings. Good idea though; raise it on pgsql-general . I suspect it'd be pretty easy to implement.

实际上，快速浏览 exec_describe_statement_message c $ c> src / backend / tcop / postgres.c 显示大脑位于 src / backend中的 SendRowDescriptionMessage 中/access/common/printtup.c 。编写简单的C扩展来完成类似的工作并生成结果集，应该不难。如果您熟悉代码库，希望可以工作几个小时；想法是：

In fact, a quick look at exec_describe_statement_message in src/backend/tcop/postgres.c shows that the brains are in SendRowDescriptionMessage in src/backend/access/common/printtup.c. It shouldn't be hard to write a simple C extension to do similar work and produce a resultset. Hopefully a couple of hours work if you're familar with the codebase; the idea would be:

• 将SQL字符串输入解析器/重写器/计划器中以获得计划


• 查找顶级节点的目标列表（如果有）。这将是DML的 RETURNING 节点，或者是的顶级 Query tlist SELECT 。


• 将tlist放入 ExecTypeFromTL


• 循环在tupledesc条目上，通过在tlist中查找来跳过 resjunk 列，并为查询中的每个非重新生成的输出发出一行。

• Feed the SQL string into the parser/rewriter/planner to obtain a plan
• Find the targetlist of the top level node, if any. This will be the RETURNING node for DML, or the top-level Query tlist for a SELECT.
• Feed the tlist into ExecTypeFromTL
• Loop over the tupledesc entries, skipping resjunk columns by looking them up in the tlist, and emit a row for each non-resjunk output from the query.

这篇关于描述PostgreSQL中的结果集？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！