PostgreSQL - 返回键值对数组的最佳方式 [英] PostgreSQL - best way to return an array of key-value pairs
问题描述
我想选择多个字段，其中一个字段需要是数组的每个元素包含两个值的数组。每个数组项都需要包含一个名称（字符变化）和一个ID（数字）。我知道如何返回一个单一值的数组（使用 ARRAY 关键字），但我不知道如何返回一个对象的数组本身包含两个值。
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.
查询类似于
SELECT
t.field1,
t.field2,
ARRAY(--with each element containing two values i.e. {'TheName', 1 })
FROM MyTable t
我读到一种方法是选择一个类型的值，然后创建一个类型的数组。问题是，函数的其余部分已经返回一个类型（这意味着我将有嵌套类型 - 是否OK？如果是这样，你将如何在应用程序代码中读取这些数据 - 例如使用.Net数据提供者像NPGSQL ？）
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?)
任何帮助都非常感谢。
推荐答案
没有你的应用程序的更多的知识我不能够让你一路到你需要的结果。但我们可以得到很远。对于初学者，有 ROW 函数：
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)
PostgreSQL是一个类型相同的名字，所以如果你已经有一个这样的表，你也有一个类型。例如：
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:
# 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)
人就像一个类型。
现在这不太可能是你想象的那么多帮助两个原因：
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.
您的语言的PostgreSQL驱动程序可能无法处理嵌套在一行中的行值数据。
Your language's PostgreSQL driver may not be able to handle row-valued data nested in a row.
我不能说NPGSQL，但由于这是一个非常PostgreSQL特定的功能，你不会在支持其他数据库的库中找到支持。例如，Hibernate不能处理在一行中获取作为单元格值存储的对象。我甚至不确定JDBC能否为Hibernate提供有用的信息，所以问题可能非常深。
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.
所以，你在这里做的是可行的，只要你能生活没有很多的美味。我建议不要追求它，因为它将是一个艰难的战斗的整个过程，除非我真的错误。
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.
这篇关于PostgreSQL - 返回键值对数组的最佳方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
