calculation in postgresql function

Question

I'm trying to create function that serves as replacement for postgresql sum function in my query.

query itself it LOOONG and something like this

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

I want to replace this sum function with my own which alters the end value by tax calculation.

So I created pgsql function like this:

DECLARE
  v double precision;
BEGIN
  IF val > 256 THEN
    v := 256;
  ELSE
    v := val;
  END IF;
  RETURN (v*0.21)/0.79;
END;

That function uses double precision as input. Using this function gives me error though:

column "bar.tax" must appear in the GROUP BY clause or be used in an aggregate function

So I tried to use double precision[] as input type for function - in that case the error was telling me that there was no postgresql function matching the name and given input type.

So is there a way to replace sum function in my query by using pgsql functions or not - I don't want to change any other parts of my query unless I really, Really, REALLY have to.

Michael Krelin - hacker · Accepted Answer · 2012-09-12 12:40:11Z

3

Look into CREATE AGGREGATE for aggregate functions. And in your expression you can do something like sum(LEAST(tax,256)*21/79) if I read you right.

answered Sep 12, 2012 at 12:40

Michael Krelin - hacker

144k25 gold badges200 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Odif Yltsaeb Over a year ago

allright. Thanks. You even answered my question twice offering an alternative. Awesome :)

Collectives™ on Stack Overflow

calculation in postgresql function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related