我正在写一个遍历数组的多态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;
AFAIK,如果没有“模板”变量或参数,则不能声明多态类型的变量。
有在本章的端部的手册中相关实施例声明功能参数,但是这招不被覆盖:添加另一个IN
,INOUT
或OUT
与数据类型参数ANYELEMENT
的函数定义。它会自动解析为匹配的元素类型,可以直接在函数体内用作变量,也可以用作更多变量的模板:
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;
有关的:
像这样的复制类型仅在本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 9.4中,使用WITH ORDINALITY
保留元素的原始顺序。详细说明:
单个值的基本代码:
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
:
内置到查询中:
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
不可用:
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句