Writing aggregates is not hard and can be highly beneficial for performing more complex operations. In this section, the plan is to write a hypothetical aggregate, which has already been discussed in this chapter.

Implementing hypothetical aggregates is not too different from writing normal aggregates. The really hard part is figuring out when to actually use one. To make this section as easy to understand as possible, I have decided to include a trivial example: given a specific order, what would the result be if we added abc to the end of the string?

Here is how it works:

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 ] 
   [ , INITCOND = initial_condition ] 
   [ , PARALLEL = { SAFE  | RESTRICTED | UNSAFE } ] [ , HYPOTHETICAL ] 
) 

Two functions will be needed. The sfunc function will be called for every line:

CREATE FUNCTION hypo_sfunc(text, text)  
   RETURNS text  AS 
$$  
   BEGIN 
         RAISE  NOTICE 'hypo_sfunc called with  % and %', $1, $2;  
         RETURN $1 || $2; 
   END; 
$$ LANGUAGE 'plpgsql'; 

Two parameters will be passed to the procedure. The logic is the same as it was previously. Just like we did earlier, a final function call can be defined:

CREATE FUNCTION hypo_final(text, text,  text)  
   RETURNS text  AS 
$$  
   BEGIN 
         RAISE  NOTICE 'hypo_final called with  %, %, and %', 
               $1, $2, $3;  
         RETURN $1 || $2; 
   END; 
$$ LANGUAGE 'plpgsql'; 

Once these functions are in place, the hypothetical aggregate can be created:

CREATE AGGREGATE whatif(text ORDER  BY text)   
(   
   INITCOND = 'START', 
   STYPE  = text, 
   SFUNC  = hypo_sfunc,  
   FINALFUNC = hypo_final,  
   FINALFUNC_EXTRA = true,  
   HYPOTHETICAL 
); 

Note that the aggregate has been marked as hypothetical so that PostgreSQL will know what kind of aggregate it actually is.

Now that the aggregate has been created, it is possible to run it:

test=# SELECT whatif('abc'::text) WITHIN GROUP (ORDER BY id::text)  
       FROM     generate_series(1, 3) AS id; 
psql: NOTICE:  hypo_sfunc called with  START  and 1 
psql: NOTICE:  hypo_sfunc called with  START1 and 2 
psql: NOTICE:  hypo_sfunc called with  START12 and 3 
psql: NOTICE:  hypo_final called with  START123, abc,  and <NULL> 
   whatif 
-------------  
 START123abc 
(1 row) 

The key to understanding all of these aggregates is really to fully see when each kind of function is called and how the overall machinery works.