行构造函数有什么用? [英] What is a row constructor used for?

问题描述

在PostgreSQL中，ROW()函数是干什么用的?

In PostgreSQL, what is the ROW() function used for?

具体有什么区别

SELECT ROW(t.f1, t.f2, 42) FROM t;

其中 f1 的类型为 int，f2 的类型为 text

where f1 is of type int, f2 is of type text

和

CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);

推荐答案

您混淆了抽象级别.正如其他答案已经指出的那样，CREATE TYPE 仅在系统中注册(复合/行)类型.而 ROW 构造函数实际上返回一行.

You are confusing levels of abstraction. As other answers already point out, CREATE TYPE only registers a (composite / row) type in the system. While a ROW constructor actually returns a row.

使用 ROW 构造函数创建的行类型不保留列名，这在您尝试将行转换为 JSON 时变得很明显.

A row type created with the ROW constructor does not preserve column names, which becomes evident when you try to convert the row to JSON.

尽管如此，ROW 在大多数情况下只是一个干扰词.文档:

While being at it, ROW is just a noise word most of the time. The documentation:

当列表中有多个表达式时，关键字ROW是可选的.

The key word ROW is optional when there is more than one expression in the list.

演示:

SELECT t                              AS r1, row_to_json(t)                           AS j1
     , ROW(1, 'x', NUMERIC '42.1')    AS r2, row_to_json(ROW(1, 'x', NUMERIC '42.1')) AS j2
     ,    (1, 'x', NUMERIC '42.1')    AS r3, row_to_json(   (1, 'x', NUMERIC '42.1')) AS j3
     ,    (1, 'x', '42.1')::myrowtype AS r4, row_to_json((1, 'x', '42.1')::myrowtype) AS j4
FROM (SELECT 1, 'x', NUMERIC '42.1') t;

db<>fiddle 这里
_sqlfiddle

r1 和 j1 保留原始列名.
r2 和 j2 没有.
r3 和 j3 是一样的；来证明 ROW 只是噪音.
r4 和 j4 携带注册类型的列名.

r1 and j1 preserve original column names.
r2 and j2 do not.
r3 and j3 are the same; to demonstrate how ROW is just noise.
r4 and j4 carry the column names of the registered type.

如果元素的编号和数据类型与行类型-名称匹配，您可以将行(记录)转换为已注册的行类型> 输入字段被忽略.

You can cast the row (record) to a registered row type if number and data types of the elements match the row type - names of input fields are ignored.

• 作为 JSON 对象数组返回SQL (Postgres)

这篇关于行构造函数有什么用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！