Postgresql aggregate function sample

最新推荐文章于 2024-08-13 16:09:20 发布

jeremy_pg

最新推荐文章于 2024-08-13 16:09:20 发布

阅读量546

点赞数

分类专栏： postgres

本文链接：https://blog.csdn.net/qixiaoya/article/details/103424090

版权

postgres 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

create the sfunc

CREATE or replace FUNCTION uuid_set_intersect(uuid[], uuid[])
  RETURNS uuid[]
  language plpgsql
  IMMUTABLE
as $FUNCTION$
declare  _r uuid[];
begin
    if $1 = '{00000000-0000-0000-0000-000000000000}'::uuid[] then 
        _r := $2;
    else 
        SELECT ARRAY(
            SELECT UNNEST($1)
            INTERSECT
            SELECT UNNEST($2)
        ) into _r;
    end if;
    return _r;
end;
$FUNCTION$;

drop aggregate if exists uuid_set_intersect_agg(uuid[]);
create aggregate uuid_set_intersect_agg(uuid[])(
    sfunc = uuid_set_intersect, 
    stype = uuid[],
    INITCOND  = '{00000000-0000-0000-0000-000000000000}'
);