查詢以輸出查詢、表或視圖的列名和數據類型

是否有返回查詢、表或視圖的欄位名稱和欄位類型的 PostgreSQL 查詢或命令？

例如，如果應用於簡單的 SELECT 查詢的解決方案SELECT * from person應該返回一個列表，如：

Column Name   | Column Type
===========================
First Name    | character
Last Name     | character
Age           | integer
Date of Birth | date

我查看了information_schema下面答案中描述的視圖，它似乎很好地涵蓋了表格，我懷疑它也涵蓋了視圖，但我還沒有檢查過。

最後一個是任意但有效的 SELECT 查詢，例如在數據庫中涉及、JOINS等 UNIONS。是否有內置過程或其他儲存過程或腳本可以為任何有效的 QUERY 返回相同的內容？

我正在開發一個創建數據和查詢表單的程序，數據驗證和對返回的數據執行函式需要這些資訊。

資訊模式與系統目錄

我們已經多次討論過這個問題。資訊模式服務於某些目的。系統目錄是所有資訊的實際來源。

資訊模式提供了有助於可移植性的標準化視圖，主要是跨主要 Postgres 版本，因為一旦您的查詢足夠複雜以至於需要查找系統目錄，跨不同 RDBMS 平台的可移植性通常是一種錯覺。而且，值得注意的是，Oracle 仍然不支持資訊模式。

資訊模式中的視圖必須經過許多環節才能達到符合標準的格式。這使它們變得緩慢，有時甚至非常緩慢。比較這些基本對象的計劃和性能：

EXPLAIN ANALYZE SELECT * from information_schema.columns;
EXPLAIN ANALYZE SELECT * from pg_catalog.pg_attribute;

差異是顯著的。這真的取決於你在找什麼。

你的例子

對於您的範例SELECT * from tbl，請比較以下針對此簡單表的兩個查詢：

CREATE TEMP TABLE foo(
  a numeric(12,3)
, b timestamp(0)
);

使用**pg_attribute**：

SELECT attname, format_type(atttypid, atttypmod) AS type
FROM   pg_attribute
WHERE  attrelid = 'foo'::regclass
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum;

format_type()返回帶有所有修飾符的完整類型：

attname | type
--------+-------------------------------
a       | numeric(12,3)
b       | timestamp(0) without time zone

另請注意，轉換regclass為根據目前解析表名search_path。如果名稱無效，它會引發異常。看：

• 錯誤：找不到數據類型 information_schema.sql_identifier 的數組類型

使用**information_schema.columns**：

SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'foo'
ORDER  BY ordinal_position;

資訊是標準化的，但不完整：

column_name | data_type
------------+----------------------------
a           | numeric
b           | timestamp without time zone

要獲取數據類型的完整資訊，您需要另外考慮所有這些列：

character_maximum_length
character_octet_length
numeric_precision
numeric_precision_radix
numeric_scale
datetime_precision
interval_type
interval_precision

相關答案：

• 如何檢查給定模式中是否存在表（在 SO 上）
• 列出指定表的所有列

利弊清單

最大的專業人士（IMO）以粗體顯示。

資訊模式視圖

• 通常更簡單（取決於）
• 慢
• 預處理，可能適合也可能不適合您的需求
• 選擇性（使用者只能看到他們有權限的對象）
• 符合 SQL 標準（由一些主要的 RDBMS 實現）
• 主要可跨主要 Postgres 版本移植
• 不需要太多關於 Postgres 的具體知識
• 標識符是描述性的，冗長的，有時很尷尬

系統目錄

• 通常更複雜（取決於），更接近源
• 快速地
• 完整（包括系統列oid）
• 不符合 SQL 標準
• 跨主要 Postgres 版本的可移植性較差（但基礎不會改變）
• 需要更具體的 Postgres 知識
• 標識符簡潔，描述性較差，但方便簡短

任意查詢

要從查詢中獲取相同的列名和類型列表，您可以使用一個簡單的技巧：查詢輸出中CREATE的臨時表，然後使用與上述相同的技術。

您可以附加LIMIT 0，因為您不需要實際數據：

CREATE TEMP TABLE tmp123 AS
SELECT 1::numeric, now()
LIMIT  0;

要獲取各個列的數據類型，您還可以使用函式pg_typeof()：

SELECT pg_typeof(1);

引用自：https://dba.stackexchange.com/questions/75015