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

AFAIK,如果没有“模板”变量或参数,则无法声明多态类型的变量.

第Declaring Function Parameters章末尾的手册中有相关示例,但未涉及此技巧：将另一个IN,INOUT或OUT参数与数据类型ANYELEMENT一起添加到函数定义中.它自动解析为匹配元素类型,可以(ab)直接在函数体内用作变量,也可以用作更多变量的模板：

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

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

   RETURN ret;
END
$func$ LANGUAGE plpgsql;

有关：

> Can I make a plpgsql function return an integer without using a variable?

像这样的复制类型只能在DECLARE部分中使用,并且是不同的类型转换. It is explained in the manual here.

分配默认值,因此添加的参数不必包含在函数调用中：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,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有点痛苦：

> How to determine if NULL is contained in an array in Postgres?

所有这些功能都只是概念的证明.我也不会用.代替：

使用纯SQL的卓越解决方案

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

> PostgreSQL unnest() with element number

单值的基本代码：

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

返回：

uniq
------------
{1,4}

关于DISTINCT ON：

> Select first row in each GROUP BY group?

内置于查询中：

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,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
         ) 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.