不支持AGGREGATE关键字

--创建object type
CREATE or REPLACE type secmax_context AS object(
  firmax NUMBER, 
  secmax NUMBER, 
  static FUNCTION ODCIAggregateInitialize(sctx IN OUT secmax_context) RETURN NUMBER, 
  member FUNCTION ODCIAggregateIterate(self  IN OUT secmax_context,value IN NUMBER) RETURN NUMBER, 
  member FUNCTION ODCIAggregateMerge(self IN OUT secmax_context, ctx2 IN secmax_context)RETURN NUMBER, 
  member FUNCTION ODCIAggregateTerminate(self IN secmax_context,returnValue OUT NUMBER,flags IN NUMBER) RETURN NUMBER
);

--实现object type
create or replace type body secmax_context is 
 
static function ODCIAggregateInitialize(sctx IN OUT secmax_context) return number is 
begin
  sctx := secmax_context(0, 0);
  return ODCIConst.Success;
end;
 
member function ODCIAggregateIterate(self IN OUT secmax_context, value IN number) return number is
begin
  if value > self.firmax then
    self.secmax := self.firmax;
    self.firmax := value;
  elsif value > self.secmax then
    self.secmax := value;
  end if;
  return ODCIConst.Success;
end;
 
member function ODCIAggregateTerminate(self IN secmax_context, returnValue OUT number, flags IN number) return number is
begin
  returnValue := self.secmax;
  return ODCIConst.Success;
end;
 
member function ODCIAggregateMerge(self IN OUT secmax_context, ctx2 IN secmax_context) return number is
begin
  if ctx2.firmax > self.firmax then
    if ctx2.secmax > self.firmax then 
      self.secmax := ctx2.secmax;
          else
      self.secmax := self.firmax;
    end if;
    self.firmax := ctx2.firmax;
  elsif ctx2.firmax > self.secmax then
    self.secmax := ctx2.firmax;
  end if;
  return ODCIConst.Success;
end;
end;

--创建聚合函数
CREATE FUNCTION SecMax (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING secmax_context;

select secmax(id) from test;

CREATE AGGREGATE name ( [ [ argmode ] [ argname ] arg_data_type [ , ... ] ]
                        ORDER BY [ argmode ] [ argname ] arg_data_type [ , ... ] ) (
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , SSPACE = state_data_size ]
    [ , FINALFUNC = ffunc ]
    [ , FINALFUNC_EXTRA ]
    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , INITCOND = initial_condition ]
    [ , HYPOTHETICAL ]
    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
)

canno=> create publication pub1 for all tables ;
CREATE PUBLICATION
canno=> CREATE AGGREGATE max2(int)
canno-> (
canno(>   INITCOND = 0,
canno(>   SFUNC = second_max,
canno(>   STYPE = int
canno(> );
CREATE AGGREGATE
canno=>
canno=> create or replace function re_max2 (int,int) returns int as $$
canno$> declare
canno$>   result int;
canno$> begin
canno$>   if $1 <=$2
canno$>   then
canno$>       result=$2;
canno$>   else
canno$>       result=$1;
canno$>   end if;
canno$>   return result;
canno$> end;
canno$> $$ language plpgsql ;
CREATE FUNCTION
canno=> select max2(id) from a;
 max2
------
   10
(1 row)