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

I'm trying to select a number of fields, one of which needs to be an array with each element of the array containing two values. Each array item needs to contain a name (character varying) and an ID (numeric). I know how to return an array of single values (using the ARRAY keyword) but I'm unsure of how to return an array of an object which in itself contains two values.

我正在尝试选择一些字段，其中一个字段需要是一个数组，数组的每个元素都包含两个值。每个数组项都需要包含一个名称(字符变化)和一个ID(数字)。我知道如何返回单个值的数组(使用array关键字)，但我不确定如何返回包含两个值的对象的数组。

The query is something like

查询类似于

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

I read that one way to do this is by selecting the values into a type and then creating an array of that type. Problem is, the rest of the function is already returning a type (which means I would then have nested types - is that OK? If so, how would you read this data back in application code - i.e. with a .Net data provider like NPGSQL?)

我读过这样做的一种方法是将值选择为一个类型，然后创建该类型的数组。问题是，函数的其余部分已经返回了一个类型(这意味着我将有嵌套类型)，这样可以吗?如果是这样，您将如何在应用程序代码中读取这些数据——例如使用。net数据提供程序(如NPGSQL) ?

Any help is much appreciated.

非常感谢您的帮助。

2 个解决方案

#1

I suspect that without having more knowledge of your application I'm not going to be able to get you all the way to the result you need. But we can get pretty far. For starters, there is the ROW function:

我怀疑如果我不了解你的应用程序，我就无法让你得到你需要的结果。但是我们可以走得很远。对于初学者，有行函数:

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

So that right there lets you bundle a whole row into a cell. You could also make things more explicit by making a type for it:

这样你就可以把一整行放到一个单元格中。你也可以为它创建一个类型，使事情更明确:

# 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)

Incidentally, whenever you make a table, PostgreSQL makes a type of the same name, so if you already have a table like this you also have a type. For example:

顺便说一句，每当创建一个表时，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)

See in the third query there I used people just like a type.

在第三个查询中，我使用的人就像一个类型。

Now this is not likely to be as much help as you'd think for two reasons:

这可能不像你想的那么有帮助有两个原因:

1. I can't find any convenient syntax for pulling data out of the nested row.

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

   I may be missing something, but I just don't see many people using this syntax. The only example I see in the documentation is a function taking a row value as an argument and doing something with it. I don't see an example of pulling the row out of the cell and querying against parts of it. It seems like you can package the data up this way, but it's hard to deconstruct after that. You'll wind up having to make a lot of stored procedures.

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

2. Your language's PostgreSQL driver may not be able to handle row-valued data nested in a row.

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

   I can't speak for NPGSQL, but since this is a very PostgreSQL-specific feature you're not going to find support for it in libraries that support other databases. For example, Hibernate isn't going to be able to handle fetching an object stored as a cell value in a row. I'm not even sure the JDBC would be able to give Hibernate the information usefully, so the problem could go quite deep.

   我不能代表NPGSQL，但是由于这是一个非常特定于postgresql的特性，所以在支持其他数据库的库中无法找到对它的支持。例如，Hibernate无法处理获取存储为行中的单元格值的对象。我甚至不确定JDBC是否能够有效地向Hibernate提供信息，因此问题可能会非常严重。

So, what you're doing here is feasible provided you can live without a lot of the niceties. I would recommend against pursuing it though, because it's going to be an uphill battle the whole way, unless I'm really misinformed.

所以，你在这里所做的一切都是可行的，前提是你可以在没有很多细节的情况下生活。但我建议不要去追求它，因为这将是一场艰苦的战斗，除非我真的被误导了。

#2

ARRAYs can only hold elements of the same type

Your example displays a text and an integer value (no single quotes around 1). It is generally impossible to mix types in an array. To get those values into an array you have to create a composite type and then form an ARRAY of that composite type like you already mentioned yourself.

您的示例显示一个文本和一个整数值(在1附近没有单引号)。在数组中混合类型通常是不可能的。要将这些值放入一个数组中，您必须创建一个复合类型，然后形成一个类似您已经提到过的组合类型的数组。

Alternatively you can use the data types json in Postgres 9.2+, jsonb in Postgres 9.4+ or hstore for key-value pairs.

或者，您可以在Postgres 9.2+中使用json数据类型，在Postgres 9.4+中使用jsonb，或者在hstore中使用键-值对。

---

Of course, you can cast the integer to text, and work with a two-dimensional text array. Consider the two syntax variants for a array input in the demo below and consult the manual on array input.

当然，可以将整数转换为文本，并使用二维文本数组。请考虑下面演示中的数组输入的两种语法变体，并参考数组输入的手册。

There is a limitation to overcome. If you try to aggregate an ARRAY (build from key and value) into a two-dimensional array, the aggregate function array_agg() or the ARRAY constructor error out:

要克服这一障碍是有限度的。如果您试图将一个数组(从键和值构建)聚合到一个二维数组中，则聚合函数array_agg()或数组构造函数出错:

ERROR:  could not find array type for data type text[]

There are ways around it, though.

不过，有很多方法可以解决这个问题。

Aggregate key-value pairs into a 2-dimensional array

PostgreSQL 9.1 with standard_conforming_strings= on:

PostgreSQL 9.1带有standard_conforming_strings= on:

CREATE TEMP TABLE tbl(
 id     int
,txt    text
,txtarr text[]
);

The column txtarr is just there to demonstrate syntax variants in the INSERT command. The third row is spiked with meta-characters:

列txtarr仅用于演示INSERT命令中的语法变体。第三行中加入了元字符:

INSERT INTO tbl VALUES
 (1, 'foo', '{{1,foo1},{2,bar1},{3,baz1}}')
,(2, 'bar', ARRAY[['1','foo2'],['2','bar2'],['3','baz2']])
,(3, '}b",a{r''', '{{1,foo3},{2,bar3},{3,baz3}}'); -- txt has meta-characters

SELECT * FROM tbl;

Simple case: aggregate two integer (I use the same twice) into a two-dimensional int array:

简单情况:将两个整数(我使用相同的两次)聚合到一个二维int数组中:

Update: Better with custom aggregate function

With the polymorphic type anyarray it works for all base types:

多态性类型anyarray适用于所有基类型:

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

Call:

电话:

SELECT array_agg_mult(ARRAY[ARRAY[id,id]]) AS x        -- for int
      ,array_agg_mult(ARRAY[ARRAY[id::text,txt]]) AS y -- or text
FROM   tbl;

Note the additional ARRAY[] layer to make it a multidimensional array.

注意附加的数组[]层，使其成为多维数组。

Update for Postgres 9.5+

Postgres now ships a variant of array_agg() accepting array input and you can replace my custom function from above with this:

Postgres现在提供了接受数组输入的array_agg()的一个变体，您可以用以下代码替换上面的自定义函数:

The manual:

手册:

array_agg(expression)
...
input arrays concatenated into array of one higher dimension (inputs must all have same dimensionality, and cannot be empty or NULL)

array_agg(表情)…输入数组连接到一个更高维度的数组中(输入必须具有相同的维数，不能为空或空)