在Postgres中有效地循环通过VARIADIC

Question

我在Postgres中有下表

通常会像下面这样填充

id    day       visits      passes
1   Monday     {11,13,19}   {13,17}
2   Tuesday    {7,9}        {11,13,19}
3   Wednesday  {2,5,21}     {21,27}
4   Thursday   {3,11,39}    {21,19}` 

为了在一定天数内获得visit或passesid，我编写了以下函数

CREATE OR REPLACE FUNCTION day_entries(p_column TEXT,VARIADIC ids int[]) RETURNS bigint[] AS
$$
DECLARE result bigint[];
DECLARE hold bigint[];
BEGIN
FOR i IN 1 .. array_upper(ids,1) LOOP
execute format('SELECT %I FROM days WHERE id = $1',p_column) USING ids[i] INTO hold;
result := unnest(result) UNION unnest(hold);
END LOOP;
RETURN result;
END;
$$
LANGUAGE 'plpgsql';

它适用于随后对day_entries('visits',1,2,3)返回的调用

{11,9,19,21,5,13,2,7}

虽然它完成了这项工作，但我担心的是，根据我一天前编写 Postgres 函数的知识，我已经在这个过程中处理了一个或多个效率。 该功能可以以某种方式变得更容易吗？

另一个问题与其说是问题，不如说是好奇 - 结果中元素的顺序似乎与触及的三行中visits条目的顺序无关。 虽然就我而言这不是一个问题，但我很好奇为什么会发生这种情况。

Answer 1

您可以在单个语句中进行取消嵌套和聚合，无需循环。您可以将ANY运算符与数组一起使用来选择所有匹配的行。

CREATE OR REPLACE FUNCTION day_entries(p_column TEXT, variadic p_ids int[]) 
RETURNS bigint[] AS
$$
DECLARE 
result bigint[];
BEGIN
execute 
format('SELECT array(select unnest(%I) from days WHERE id = any($1))', p_column) 
USING p_ids -- pass the whole array as a parameter
INTO result;
RETURN result;
END;
$$
LANGUAGE plpgsql;

---

与您的问题无关，但我认为您的设计走错了路。虽然数组在开始时对初学者来说可能看起来很有趣，但它们应该很少使用。

如果你发现自己来回解嵌套和聚合东西，这强烈表明某些东西可以改进。

我会将您的表分成两个表，一个存储"天"信息，另一个存储同一表中的访问和传递，并有一列区分两者。然后查找访问就像添加一个 where... = 'visit'一样简单，而不必处理(缓慢且容易出错的(动态 SQL。

在不知道更多详细信息的情况下，我可能会创建这样的表：

create table days
(
id integer not null primary key,
day character varying(9) not null
);
create table event
(
day_id integer not null references days,
event_id integer not null,
event_type varchar(10) not null check (event_type in ('visit', 'pass'))
);

event_id甚至可能是您尚未向我们展示的另一个表的外来键 - 这同样是您无法真正使用非规范化表做的事情。

获取特定日期的所有访问就像：_

select event_id
from event
where day_id in (1,2)
and event_type = 'visit';

或者，如果您确实需要将其作为数组：

select array_agg(event_id)
from event
where day_id in (1,2)
and event_type = 'visit';

在线示例