在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或passes I，我编写了以下函数

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'。

在不了解更多细节的情况下，我可能会创建这样的表：

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甚至可能是另一个表的外键，您还没有向我们展示过--同样，对于非规范化表，您也不能这样做。

在特定的日子里获得所有的访问，就像简单的as:_一样

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

在线示例