如何从PostgreSQL查询中获取列名和类型（不运行它）？ [英] How to get column names and types from a PostgreSQL query (without running it)?

问题描述

对于任何给定的（Postgres）SQL查询，我都需要能够提取列名和列类型。我不需要查询结果。而且我需要它快速（例如，我不想等待查询本身完成-尤其是花费几分钟或更长时间）。

For any given (Postgres) SQL query, I need to be able to extract the column names and column types. I do not need the results of the query. And I need it to be quick (i.e. I don't want to have to wait for the query itself to finish - especially if it takes minutes or longer).

我认为我有一个解决方案：

为以下查询提取列和类型：

To extract the columns and types for the following query:

with X as (
  select nationality, count(*)
  from customers group by nationality
)
select *
from X

我将其包装如下：

select *
from (
    with X as (
        select nationality, count(*)
        from customers group by nationality
    )
    select *
    from X
) q
where 1 = 0
limit 0

和其中1 = 0 和限制0 表示它将不会运行内容。

and the where 1 = 0 and limit 0 would mean that it wouldn't run the content.

但这不起作用

如果 customers 表很大，那么上面的代码需要一分钟才能运行。然后返回0个结果（按预期）。

If the customers table is huge, then the above takes over a minute to run. And then returns 0 results (as expected).

有什么想法吗？

有什么方法（无需编写我自己的PSQL解析器）从任何任意PSQL查询（无需运行至完成）中获取列名和类型？

Is there any way (without writing my own PSQL parser) to get the column names and types from any arbitrary PSQL query (without running it to completion)?

注意：我们的目标是可以与任何任意（用户输入的）SQL SELECT查询一起使用。

Note: the goal is for this to work with any arbitrary (user-entered) SQL SELECT query.

推荐答案

借助Postgres（及其JDBC驱动程序） ），您可以执行以下操作：

With Postgres (and its JDBC driver) you can do the following:

PreparedStatement pstmt = con.prepareStatement("select ... ");
ResultSetMetaData meta = pstmt.getMetaData();
for (int i=1; i <= meta.getColumnCount(); i++)
{
  System.out.println("Column name: " + meta.getColumnName(i) + ", data type: " + meta.getColumnTypeName(i));
}

请注意，您无需在以下位置添加错误的或限制0 。调用 prepareStatement（）并不会真正执行查询。

Note that you do not need to add a where false or limit 0 to the statement. The call to prepareStatement() does not actually execute the query.

这篇关于如何从PostgreSQL查询中获取列名和类型（不运行它）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！