如何获取数组元素的类型? [英] How do I get the type of an array's elements?

问题描述

我正在编写在数组上迭代的多态 PL/pgSQL函数.我对使用FOREACH感兴趣，但是我不知道如何声明正确类型的临时变量.

I'm writing a polymorphic PL/pgSQL function that iterates over an array. I am interested in using FOREACH, however I cannot figure out how to declare a temporary variable with the right type.

下面是我的函数，有关更多信息，请参见第4行上的注释.

My function is below, for more information see the comment on line 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;

推荐答案

主要问题的答案

AFAIK，您不能在没有模板"变量或参数的情况下声明多态类型 的变量.

声明函数参数，但没有涵盖 此技巧:使用以下命令添加另一个IN，INOUT或OUT参数函数定义的数据类型ANYELEMENT.它会自动解析为匹配的元素类型，并且可以直接在函数体内用作变量或用作更多变量的模板:

There are related examples in the manual at the end of the chapter Declaring Function Parameters, but this trick is not covered: add another IN, INOUT or OUT parameter with data type ANYELEMENT to the function definition. It resolves to the matching element type automatically and can be (ab)used as variable inside the function body directly or as template for more variables:

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;

相关:

• 我可以使plpgsql函数在不使用变量的情况下返回整数吗?

类似的复制类型仅在DECLARE部分中有效，并且是不同的类型转换. 在此处的手册中对此进行了说明.

Copying types like that only works in the DECLARE section and is different type casting. It is explained in the manual here.

分配默认值，因此添加的参数不必包含在函数调用中:ANYELEMENT = NULL

Assign a default value, so the added parameter does not have to be included in the function call: ANYELEMENT= NULL

通话(不变):

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

更好的功能

为方便起见，我实际上将使用OUT参数并反转测试逻辑:

Better function

I would actually use an OUT parameter for convenience and invert the test logic:

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元素的情况.

But this still does not cover all cases containing NULL elements.

同样适用于NULL元素:

To work for NULL elements as well:

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有点麻烦:

Checking for NULL in an array is a bit of a pain:

• 如何确定NULL是否包含在Postgres的数组中?

所有这些功能都是概念证明.我不会使用都不使用.相反:

All of these functions are just proof of concept. I would use neither. Instead:

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

In Postgres 9.4 use WITH ORDINALITY to preserve original order of elements. Detailed explanation:

• 具有元素编号的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数组.覆盖所有基地:

This has a tiny corner case: it returns an empty array a NULL array. To cover all bases:

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():

In Postgres 9.3 or older you can substitute with 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不可用:

We need this in sqlfiddle, which currently only supports pg 9.3, so WITH ORDINALITY is not available:

SQL小提琴.

这篇关于如何获取数组元素的类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！