greenplum (postgresql) 自定义数据集合去重函数 function
创建函数 function
-- 数组去除重复的值,去重后按升序排序
CREATE OR REPLACE FUNCTION array_distinct ( varchar [] )
RETURNS TABLE ( val varchar [] )
AS $$ WITH
FIRST AS ( SELECT val FROM UNNEST ( $1 ) AS val ),
SECOND AS ( SELECT val FROM FIRST GROUP BY val ORDER BY val )
SELECT ARRAY_AGG ( val ) FROM SECOND
$$ LANGUAGE SQL STRICT IMMUTABLE;
调用方式
select array_distinct(varchar[]);
示范