如何获取数组元素的类型？

Question

我正在编写一个多态 PL/pgSQL 函数，它可以迭代数组。我想使用 FOREACH，但是我不知道如何声明一个正确类型的临时变量。

以下是我的函数，更多信息请参见第4行的注释。

CREATE OR REPLACE FUNCTION uniq(ary anyarray) RETURNS anyarray AS $$
DECLARE
  ret ary%TYPE := '{}';
  v ???; -- how do I get the element type of @ary@?
BEGIN
  IF ary IS NULL THEN
    return NULL;
  END IF;

  FOREACH v IN ARRAY ary LOOP
    IF NOT v = any(ret) THEN
      ret = array_append(ret, v);
    END IF;
  END LOOP;

  RETURN ret;
END;
$$ LANGUAGE plpgsql;

- Nate Symer

Answer 1

主要问题的答案

据我所知，您不能声明一个多态类型的变量没有"模板"变量或参数。

在章节声明函数参数的末尾有相关示例，但是这个技巧没有被涵盖：向函数定义中添加另一个IN、INOUT 或 OUT参数，并使用数据类型ANYELEMENT。它将自动解析匹配的元素类型，并可以直接用作函数内部的变量或更多变量的模板：

CREATE OR REPLACE FUNCTION uniq1(ary ANYARRAY, <b>v ANYELEMENT = NULL</b>)
  RETURNS anyarray AS
$func$
DECLARE
   ret      ary%TYPE := '{}';
   <b>some_var v%TYPE;  -- we could declare more variables now
                     -- but we don't need to</b>
BEGIN
   IF ary IS NULL THEN
      RETURN NULL;
   END IF;

   FOREACH <b>v</b> IN ARRAY ary LOOP  <b>-- instead, we can use v directly</b>
      IF NOT v = any(ret) THEN
         ret := array_append(ret, v);
      END IF;
   END LOOP;

   RETURN ret;
END
$func$  LANGUAGE plpgsql;

相关信息：

• 我能否使plpgsql函数在不使用变量的情况下返回整数？

仅在DECLARE部分和类型强制转换中才能像这样复制类型。手册中有解释。

指定默认值，以便添加的参数不必包含在函数调用中：ANYELEMENT= NULL

调用（未更改）：

SELECT uniq1('{1,2,1}'::int[]);
SELECT uniq1('{foo,bar,bar}'::text[]);

更好的函数

为了方便起见，我会使用一个 OUT 参数并倒转测试逻辑：

CREATE OR REPLACE FUNCTION uniq2(ary ANYARRAY, elem ANYELEMENT = NULL
                               , OUT ret ANYARRAY)
  RETURNS anyarray AS
$func$
BEGIN
   IF ary IS NULL
      THEN RETURN;
      ELSE ret := '{}';  -- init
   END IF;

   FOREACH elem IN ARRAY ary LOOP
      IF elem = ANY(ret) THEN  -- do nothing
      ELSE
         ret := array_append(ret, elem);
      END IF;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

但这仍然不能涵盖所有包含NULL元素的情况。

适当的函数

为了使函数也适用于NULL元素：

CREATE OR REPLACE FUNCTION uniq3(ary ANYARRAY, elem ANYELEMENT = NULL
                               , OUT ret ANYARRAY)
  RETURNS anyarray AS
$func$
BEGIN
   IF ary IS NULL
      THEN RETURN;
      ELSE ret := '{}';  -- init
   END IF;

   FOREACH elem IN ARRAY ary LOOP
      IF elem IS NULL THEN  -- special test for NULL
         IF array_length(array_remove(ret, NULL), 1) = array_length(ret, 1) THEN
            ret := array_append(ret, NULL);
         END IF;
      ELSIF elem = ANY(ret) THEN  -- do nothing
      ELSE
         ret := array_append(ret, elem);
      END IF;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

在数组中检查是否为NULL有点麻烦：

• 如何确定Postgres数组中是否包含NULL？

所有这些函数都只是“概念证明”。我不会使用任何一个。相反：

使用纯SQL的卓越解决方案

在Postgres 9.4中，使用WITH ORDINALITY来保留元素的原始顺序。 详细说明：

• 带有元素编号的PostgreSQL unnest()

单个值的基本代码：

SELECT ARRAY (
   SELECT elem
   FROM  (
      SELECT DISTINCT ON (elem) elem, i
      FROM   unnest('{1,2,1,NULL,4,NULL}'::int[]) WITH ORDINALITY u(elem, i)
      ORDER  BY elem, i
      ) sub
   ORDER  BY i) AS uniq;

返回值：

uniq
------------
{1,2,NULL,4}

关于 DISTINCT ON：

• 如何在每个 GROUP BY 分组中选择第一行？

内置于查询中：

SELECT *
FROM   test t
     , LATERAL (
   SELECT ARRAY (
      SELECT elem
      FROM  (
         SELECT DISTINCT ON (elem) elem, i
         FROM   unnest(t.arr) WITH ORDINALITY u(elem, i)
         ORDER  BY elem, i
         ) sub
      ORDER BY i) AS arr
   ) a;

这里有一个非常小的特例：它会返回一个空数组或者是一个NULL数组。为了确保覆盖所有情况：

SELECT t.*, CASE WHEN t.arr IS NULL THEN NULL ELSE a.arr END AS arr
FROM   test t
     , LATERAL (
   SELECT ARRAY (
      SELECT elem
      FROM  (
         SELECT DISTINCT ON (elem) elem, ord
         FROM   unnest(t.arr) WITH ORDINALITY u(elem, ord)
         ORDER  BY elem, ord
         ) sub
      ORDER BY ord) AS arr
   ) a;

或者：

SELECT *
FROM   test t
LEFT   JOIN LATERAL (
   SELECT ARRAY (
      SELECT elem
      FROM  (
         SELECT DISTINCT ON (elem) elem, i
         FROM   unnest(t.arr) WITH ORDINALITY u(elem, i)
         ORDER  BY elem, i
         ) sub
      ORDER BY i) AS arr
   ) a ON t.arr IS NOT NULL;

在 Postgres 9.3 或更早版本中，您可以使用 generate_subscripts() 进行替代：

SELECT *
FROM   test t
     , LATERAL (
   SELECT ARRAY (
      SELECT elem
      FROM  (
         SELECT DISTINCT ON (t.arr[i]) t.arr[i] AS elem, i
         FROM   generate_subscripts(t.arr, 1) i
         ORDER  BY t.arr[i], i
         ) sub
      ORDER  BY i
      ) AS arr
   ) a;

我们需要在sqlfiddle中使用此功能，但目前仅支持pg 9.3，因此无法使用“WITH ORDINALITY”功能。请参见：SQL Fiddle。

- Erwin Brandstetter

---

我喜欢 WITH ORDINALITY 部分。那正是我在寻找的。 - Nate Symer

非常好的答案，我正在寻找同样的内容，但在其他地方找不到，如果您可以提供文档链接，那就太好了——> variable％TYPE。 它仅适用于变量声明，我尝试将其用于类型转换，但它没有起作用。 - abhirathore2006

@abhirathore2006：我在上面添加了一个%TYPE手册的链接。类型转换是完全不同的东西。 - Erwin Brandstetter

Answer 2

我不知道如何声明任意数组参数的基本类型变量（文档没有提到这种可能性）。

您可以使用整数变量和FOR LOOP：

CREATE OR REPLACE FUNCTION uniq(ary anyarray) RETURNS anyarray AS $$
DECLARE
  ret ary%TYPE := '{}';
  i int;
BEGIN
  IF ary IS NULL THEN
    return NULL;
  END IF;

  FOR i IN array_lower(ary, 1) .. array_upper(ary, 1) LOOP
    IF NOT ary[i] = any(ret) THEN
      ret = array_append(ret, ary[i]);
    END IF;
  END LOOP;

  RETURN ret;
END;
$$ LANGUAGE plpgsql;

然而，循环和变量可能并不是必需的：

create or replace function uniq_without_loop(arr anyarray)
returns anyarray language plpgsql as $$
begin
    return (
        select array_agg(distinct elem)
        from unnest(arr) elem);
end $$;

上述函数版本不改变数组顺序：

create or replace function unsorted_uniq_without_loop(arr anyarray)
returns anyarray language plpgsql as $$
begin
    return (
        select array_agg(elem)
        from (
            select elem 
            from (
                select distinct on(elem) elem, row_number() over ()
                from unnest(array[arr]) elem
                ) sub
            order by row_number
            ) sub);
end $$;

- klin

---

我尝试了你的第二个解决方案。问题是，我使用的数据必须保持顺序，但是你的第二个解决方案打乱了顺序。 - Nate Symer

有趣的是，在Postgres 9.4版本被冻结发布之前，有人提交了一个补丁，实现了arr％elemtype。 - Nate Symer

我已经添加了不对结果进行排序的版本。这种特殊类型的想法是有道理的。 - klin