PostgreSQL - 返回键值对数组的最佳方式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9129855/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL - best way to return an array of key-value pairs
提问by harman_kardon
I'm trying to select a number of fields, one of which needs to be an array with each element of the array containing twovalues. 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 ARRAYkeyword) 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?)
我读到这样做的一种方法是将值选择到一个类型中，然后创建一个该类型的数组。问题是，该函数的其余部分已经返回一个类型（这意味着我会有嵌套类型 - 可以吗？如果是这样，你将如何在应用程序代码中读回这些数据 - 即使用像 NPGSQL 这样的 .Net 数据提供程序?)
Any help is much appreciated.
任何帮助深表感谢。
采纳答案by Daniel Lyons
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 ROWfunction:
我怀疑如果不了解您的应用程序，我将无法让您一路获得所需的结果。但我们可以走得很远。对于初学者，有以下ROW功能：
# 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 peoplejust like a type.
在第三个查询中看到我people就像一个类型一样使用。
Now this is not likely to be as much help as you'd think for two reasons:
现在这不太可能像您想象的那样有帮助，原因有两个：
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.
Your language's PostgreSQL driver may not be able to handle row-valued data nested in a row.
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.
我找不到任何方便的语法来从嵌套行中提取数据。
我可能遗漏了一些东西，但我只是没有看到很多人使用这种语法。我在文档中看到的唯一示例是将行值作为参数并对其进行处理的函数。我没有看到将行从单元格中拉出并查询其中的一部分的示例。看起来你可以这样打包数据，但之后就很难解构了。您最终将不得不创建大量存储过程。
您语言的 PostgreSQL 驱动程序可能无法处理嵌套在一行中的行值数据。
我不能说 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.
所以，你在这里做的事情是可行的，前提是你可以在没有很多细节的情况下生活。不过，我建议不要追求它，因为这将是一场艰苦的战斗，除非我真的被误导了。
回答by Erwin Brandstetter
ARRAYs can only hold elements of the same type
ARRAY 只能容纳相同类型的元素
Your example displays a textand an integervalue (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 typeand then form an ARRAY of that composite type like you already mentioned yourself.
您的示例显示 atext和一个integer值（ 周围没有单引号1）。通常不可能在数组中混合类型。要将这些值放入数组中，您必须创建一个composite type，然后形成一个复合类型的数组，就像您自己已经提到的那样。
Alternativelyyou can use the data types jsonin Postgres 9.2+, jsonbin Postgres 9.4+ or hstorefor key-value pairs.
或者，您可以使用jsonPostgres 9.2+、jsonbPostgres 9.4+ 或hstore键值对中的数据类型。
Of course, you can cast the integerto 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.
当然，您可以将 转换integer为text，并使用二维文本数组。考虑下面演示中数组输入的两种语法变体，并查阅有关数组输入的手册。
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 ARRAYconstructor error out:
有一个限制需要克服。如果您尝试将 ARRAY（从键和值构建）聚合到二维数组中，聚合函数array_agg()或ARRAY构造函数会出错：
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 txtarris 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 anyarrayit 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.
注意附加ARRAY[]层以使其成为多维数组。
Update for Postgres 9.5+
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()接受数组输入的变体，你可以用这个替换我上面的自定义函数：
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(expression)
...
输入数组连接成一个更高维度的数组（输入必须都具有相同的维度，并且不能为空或 NULL）
