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

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

你的例子中的结构看起来是多余的。您可以这样编写查询：

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

当然，如果这个数组中有多个值，这将不起作用，但是您也不能通过其他方式获得确切的数组元素。

你为什么需要数组呢？每行只能写一个person类型的元素。

还可以查看优秀的HStore模块，它可能更适合您的一般需求。

Answer 2

临时测试设置：

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.)"}');

Call (几乎是解决方案的)：

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() function, that's available in 9.0.

• Your的错误是忘记了数组之间的层，这就是
• 键。对于每个基本元素，unnest()返回一行，然后您可以作为demonstrated.

访问复杂类型中的列

美丽的新世界

如果你真的想要一个完整的人，而不是一个符合条件的个人，我建议你在表中添加一个主键，并按照以下步骤进行：

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;

查找所有人(solution)：

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

• 您可以在没有主键的情况下完成此操作，但这将是愚蠢的。