Explore our sites

Stack Exchange
tour help
Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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
);
share|improve this question
1  
I don't understand from your description why you can't just use yourfinalfunc(sum(tax)) to transform the final result? this would avoid creating an aggregate function completely. –  araqnid Sep 13 '12 at 9:54
 
Because that query, which i referred to, is put together programmatically and if i were to add one function on top of another, then i would have to change alot in the process that puts the query together. Add bunch of conditions and whatnot - all that will make understanding the code much harder later on. So its easyer to just change the sum function. –  Odif Yltsaeb Sep 14 '12 at 5:40
add comment

3 Answers

up vote 2 down vote accepted

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.

share|improve this answer
 
Awesome. that gets me closer to what i want, but eeincometax finalfunc expects single float8 value not array. and array_append seems to only output array. So I still need to do the actual summing in my finalfunction or is there are core function, that outputs single value? –  Odif Yltsaeb Sep 13 '12 at 8:44
 
So you want as the state transition a function that takes two floats and returns the sum of those. Coincidentally this is what the + operator does. The function that implents + operator for float8's is called float8pl. Change the sfunc to float8pl and stype to float8 and you should be golden. –  Ants Aasma Sep 13 '12 at 8:53
 
No i want aggregate function that takes bunch of records and sums them and then takes that sum and gives it to my finalfunc as input. Like i said - i want function to could replace sum() function in my query. –  Odif Yltsaeb Sep 13 '12 at 8:58
 
Yes, this is what it would do. float8pl as the transition function sums the values up and the final value is passed to your func. Also I forgot to mention that you need to change initcond to 0.0. –  Ants Aasma Sep 13 '12 at 9:01
 
Awesome. At least i got through everything without errors. But value output seems to be wrong. Ill be debugging now :). –  Odif Yltsaeb Sep 13 '12 at 9:06
add comment
up vote 1 down vote

In addition to @Ants excellent advice:

1.) Your final function could be simplified to:

CREATE FUNCTION eeincometax(float8)
  RETURNS float8 LANGUAGE SQL AS
$func$
SELECT (least($1, 256) * 21) / 79
$func$;

2.) It seems like you are dealing with money? In this case I would strongly advise to use the type numeric (preferred) or money for the purpose. Floating point operations are often not precise enough.

3.) The initial condition of the aggregate can simply be just 0:

CREATE AGGREGATE aggregate_ee_income_tax(float8)
(
  sfunc     = float8pl
 ,stype     = float8
 ,initcond  = 0
 ,finalfunc = eeincometax
);

4.) In your case (least(sum(tax), 256) * 21) / 79 is probably faster than your custom aggregate. Aggregate functions provided by PostgreSQL are written in C and optimized for performance. I would use that instead.

share|improve this answer
 
Thanks for additional info. Just a question though - since my custom aggregate function relies of postgreSQL own float8pl function, which, as i understood from your post is also written in C and optimized - how much real overhead does my function add really? –  Odif Yltsaeb Sep 16 '12 at 12:02
 
@Zayatzz: Best to just test it. Run both variants with EXPLAIN ANALYZE - maybe a couple of times to exclude cache effects. I'd be interested in the comparison ... –  Erwin Brandstetter Sep 17 '12 at 10:38
add comment
up vote 0 down vote

Not sure, because you haven't posted much of how you're using it, but it would seem that array_agg takes only one parameter, but you have passed two (and of different types).

share|improve this answer
 
I havent passed anything - as you can see from my aggreate function declaration, that i just pass function name as one parameter, values passed to that function come from somewhere else. Apparently this is how aggregate functions work - cause what im trying to do is following the docs as closely as possible. –  Odif Yltsaeb Sep 13 '12 at 7:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.