在复合类型数组中搜索元素

Question

使用 PostgreSQL 9.0，

我有以下表设置

CREATE TABLE person (age integer, last_name text, first_name text, address text);

CREATE TABLE my_people (mperson person[]);

INSERT INTO my_people VALUES(array[ROW(44, 'John', 'Smith', '1234 Test Blvd.')::person]);

现在，我希望能够编写一个 select 语句，可以在 mperson 数组列中搜索和比较复合类型的值。

示例：

SELECT * FROM my_people WHERE 20 > ANY( (mperson) .age);

但是，当尝试执行此查询时，我收到以下错误：

ERROR:  column notation .age applied to type person[], which is not a composite type
LINE 1: SELECT mperson FROM my_people WHERE 20 > ANY((mperson).age);

因此，您可以看到我正在尝试测试数组内复合类型的值。

我知道，我不应该在表中使用数组和复合材料，但这最适合我们的应用程序要求。

另外，我们有几个嵌套的复合数组，因此如果有一个通用的解决方案允许我搜索多个级别，我们将不胜感激。

Answer 1

临时测试设置：

CREATE TEMP TABLE person (age integer, last_name text, first_name text
                                                     , address text);
CREATE TEMP TABLE my_people (mperson person[]);

-- test-data, demonstrating 3 different syntax styles:
INSERT INTO my_better_people (mperson)
VALUES
   (array[(43, 'Stack', 'Over', '1234 Test Blvd.')::person])

  ,(array['(44,John,Smith,1234 Test Blvd.)'::person,
          '(21,Maria,Smith,1234 Test Blvd.)'::person])

  ,('{"(33,John,Miller,12 Test Blvd.)",
      "(22,Frank,Miller,12 Test Blvd.)",
      "(11,Bodi,Miller,12 Test Blvd.)"}');

调用（几乎解决方案）：

SELECT (p).*
FROM   (
   SELECT unnest(mperson) AS p
   FROM   my_people) x
WHERE  (p).age > 33;

返回：

 age | last_name | first_name |     address
-----+-----------+------------+-----------------
  43 | Stack     | Over       | 1234 Test Blvd.
  44 | John      | Smith      | 1234 Test Blvd.

• 键是 unnest() 函数，在 9.0 中可用。
• 您在示例中的错误是您忘记了中间的 ARRAY 层。 unnest() 每个基本元素返回一行，然后您可以访问复杂类型中的列，如图所示。

---

美丽新世界

如果您确实想要一个完整的人而不是符合条件的个人，我建议您在表中添加主键并按如下步骤操作：

CREATE TEMP TABLE my_better_people (id serial, mperson person[]);

-- shortcut to populate the new world by emigration from the old world ;)
INSERT INTO my_better_people (mperson)
SELECT mperson FROM my_people;

查找个人：

SELECT id, (p).*
FROM  (
   SELECT id, unnest(mperson) AS p
   FROM   my_better_people) x
WHERE  (p).age > 20;

查找整个人（解决方案） ：

SELECT *
FROM   my_better_people p
WHERE  EXISTS (
   SELECT 1 
   FROM (
      SELECT id, unnest(mperson) AS p
      FROM   my_better_people
      ) x
   WHERE  (p).age > 20
   AND    x.id = p.id
   );

• 你可以在没有主键的情况下做到这一点，但那是愚蠢的。

Answer 2

您的情况下的构造 ANY 看起来是多余的。您可以这样编写查询：

SELECT * FROM my_people WHERE (mperson[1]).age < 20;

当然，如果此数组中有多个值，那么这是行不通的，但您也无法以其他方式获取确切的数组元素。

为什么你需要数组？您可以在每行只写入一个 person 类型的元素。

另请检查优秀的 HStore 模块，它可能更适合您的一般需求。