Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Trying to create aggregate function in PostgreSQL

I'm trying to create new aggregate function in PostgreSQL to use instead of the sum() function

I started my journey in the manual here.

Since I wanted to create a function that takes an array of double precision values, sums them and then does some additional calculations I first created that final function:

takes double precision as input and gives double precision as output    
DECLARE
      v double precision;
BEGIN
      IF tax > 256 THEN
            v := 256;
      ELSE
            v := tax;
      END IF;
      RETURN v*0.21/0.79;
END;

Then I wanted to create the aggregate function that takes an array of double precision values and puts out a single double precision value for my previous function to handle.

CREATE AGGREGATE aggregate_ee_income_tax (float8[]) (
  sfunc = array_agg
 ,stype = float8
 ,initcond = '{}'
 ,finalfunc = eeincometax);

What I get when I run that command is:

ERROR: function array_agg(double precision, double precision[]) does not exist

I'm somewhat stuck here, because the manual lists array_agg() as existing function. What am I doing wrong?

Also, when I run:

\da
                     List of aggregate functions
 Schema | Name | Result data type | Argument data types | Description 
--------+------+------------------+---------------------+-------------
(0 rows)

My installation has no aggregate functions at all? Or does only list user defined functions?

Basically what I'm trying to understand:

1) Can I use an existing functions to sum up my array values?

2) How can I find out about input and ouptut data types of functions? Docs claim that array_agg() takes any kind of input.

3) What is wrong with my own aggregate function?

Edit 1

To give more information and clearer picture of what I'm trying to achieve:

I have one huge query over several tables which goes something like this:

SELECT sum(tax) ... from (SUBQUERY) as foo group by id

I want to replace that sum function with my own aggregate function so I don't have to do additional calculations on backend - since they can all be done on database level.

Edit 2

Accepted Ants's answer. Since final solution comes from comments I post it here for reference:

CREATE AGGREGATE aggregate_ee_income_tax (float8)
(
 sfunc = float8pl
,stype = float8
,initcond = '0.0'
,finalfunc = eeincometax
);
like image 472
Odif Yltsaeb Avatar asked Jul 03 '26 06:07

Odif Yltsaeb


1 Answers

Array agg is an aggregate function not a regular function, so it can't be used as a state transition function for a new aggregate. What you want to do is to create an aggregate function which has a state transition function that is identical to array_agg and a custom final func.

Unfortunately the state transition function of array_agg is defined in terms of an internal datatype so it can't be reused. Fortunately there is an existing function in core that already does what you want.

CREATE AGGREGATE aggregate_ee_income_tax (float8)(
    sfunc = array_append,
    stype = float8[],
    initcond = '{}',
    finalfunc = eeincometax);

Also note that you had your types mixed up, you probably want aggregate a set of floats to an array, not a set of arrays to a float.

like image 69
Ants Aasma Avatar answered Jul 04 '26 21:07

Ants Aasma



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!