PostgreSQL聚合函数超出范围

本文介绍了PostgreSQL聚合函数超出范围的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试创建一个函数，该函数将找到 tsrange 的交集，但是我无法使它起作用：

I am trying to create a function that will find the intersection of tsrange, but I can't get it work:

CREATE AGGREGATE intersection ( tsrange ) ( SFUNC = *, STYPE = tsrange )

推荐答案

对您的尝试有两个修改。首先，我认为您不能将运算符用作SFUNC，因此您需要定义一个命名函数来执行交集，并使用它。

There are two modifications to your attempt. First, I don't think you can use an operator as the SFUNC, so you need to define a named function to do the intersection, and use that.

CREATE or REPLACE FUNCTION int_tsrange(a tsrange, b tsrange) returns tsrange language plpgsql as 'begin return a * b; end';

第二，范围的默认值为空范围-因此，交集始终为空。您需要将范围初始化为无限范围’[，]’才能开始聚合。聚合定义如下所示：

Secondly, the default value for a range is the empty range -- so the intersection will always be empty. You need to initialize the range to an infinite range '[,]' to begin the aggregate. The aggregate definition then looks like:

CREATE AGGREGATE intersection ( tsrange ) ( SFUNC = int_tsrange, STYPE = tsrange, INITCOND = '[,]' );