PostgreSQL-返回键值对数组的最佳方法

Question

我正在尝试选择多个字段，其中一个字段需要是一个数组，该数组的每个元素都包含 两个
值。每个数组项都需要包含一个名称（字符不同）和一个ID（数字）。我知道如何返回单个值的数组（使用ARRAY关键字），但是我不确定如何返回本身包含两个值的对象的数组。

查询就像

SELECT
    t.field1,
    t.field2,
    ARRAY(--with each element containing two values i.e. {'TheName', 1 })
FROM MyTable t

我读到一种实现此目的的方法是，将值选择为一个类型，然后创建该类型的数组。问题是，该函数的其余部分已经返回了一个类型（这意味着我将拥有嵌套的类型-
可以吗？如果是这样，您将如何以应用程序代码的形式读取此数据-即使用诸如NPGSQL的.Net数据提供程序？）

任何帮助深表感谢。

Answer 1

我怀疑如果没有对您的应用程序的更多了解，我将无法一路使您达到所需的结果。但是我们可以走得很远。对于初学者，有以下ROW功能：

# SELECT 'foo', ROW(3, 'Bob');
 ?column? |   row   
----------+---------
 foo      | (3,Bob)
(1 row)

这样一来，您就可以将整行捆绑到一个单元格中。您还可以通过为其创建类型来使事情更明确：

# CREATE TYPE person(id INTEGER, name VARCHAR);
CREATE TYPE
# SELECT now(), row(3, 'Bob')::person;
              now              |   row   
-------------------------------+---------
 2012-02-03 10:46:13.279512-07 | (3,Bob)
(1 row)

顺便说一句，每当创建一个表时，PostgreSQL都会创建一个具有相同名称的类型，因此，如果您已经有一个这样的表，那么您也将具有一个类型。例如：

# DROP TYPE person;
DROP TYPE

# CREATE TABLE people (id SERIAL, name VARCHAR);
NOTICE:  CREATE TABLE will create implicit sequence "people_id_seq" for serial column "people.id"
CREATE TABLE

# SELECT 'foo', row(3, 'Bob')::people;
 ?column? |   row   
----------+---------
 foo      | (3,Bob)
(1 row)

在第三个查询中，我看到的people就像一个类型一样。

现在，由于两个原因，这可能不会像您想的那样有多大帮助：

1. 我找不到用于从嵌套行中提取数据的任何方便的语法。

我可能会遗漏一些东西，但只是看不到有很多人在使用这种语法。我在文档中看到的唯一示例是一个将行值作为参数并对其进行处理的函数。我没有看到将行从单元格中拉出并查询其中一部分的示例。看来您可以通过这种方式打包数据，但是之后很难对其进行解构。您将不得不做很多存储过程。

1. 您语言的PostgreSQL驱动程序可能无法处理嵌套在行中的行值数据。

我不能说NPGSQL，但是由于这是一个非常特定于PostgreSQL的功能，因此您不会在支持其他数据库的库中找到对它的支持。例如，Hibernate将无法处理获取作为单元格值存储在一行中的对象。我什至不确定JDBC是否能够为Hibernate提供有用的信息，因此问题可能会更加严重。

因此，您可以在这里做些可行的事情，前提是您可以生活在没有很多细微差别的地方。不过我还是建议不要追求它，因为除非我真的被误导，否则这将是一场艰苦的战斗。