PostgreSQL: best way to call the same function multiple times in one query?

Question

In PostgreSQL, what is the best way to call the same function multiple times in the same query?

Example:

SELECT a.id, 
       sum_one(a.id) AS "Sum_one",
       sum_two(a.id) AS "Sum_two",
       (sum_one(a.id )+sum_two(a.id)) AS "Sum_three"
  FROM a

Where sum_one() and sum_two() are functions. I repeat the call of sum_one() and sum_two(). This will slow down queries in large databases.

I want to avoid the following statement.

(sum_one(a.id )+sum_two(a.id)) AS "Sum_three"

How can I do it that in PostgreSQL?

Erwin Brandstetter · Accepted Answer · 2011-10-12 23:40:41Z

3

You should set your function volatility to Stable.

More about function volatility.

CREATE OR REPLACE FUNCTION myFunc(prm_MyParam integer)
  RETURNS numeric AS
$BODY$
BEGIN
  {...}

END;$BODY$
  LANGUAGE plpgsql STABLE;

edited Oct 12, 2011 at 23:40

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

answered Sep 16, 2011 at 13:54

ertx

1,5442 gold badges15 silver badges22 bronze badges

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

Comments

aib · Accepted Answer · 2011-09-16 10:19:39Z

1

Use a subquery:

SELECT
    "Sum_one",
    "Sum_two",
    ("Sum_one" + "Sum_two") AS "Sum_three"
FROM (
    SELECT sum_one("A"."id") AS "Sum_one", sum_two("A"."id") AS "Sum_two" FROM A
)

answered Sep 16, 2011 at 10:19

aib

47.4k10 gold badges75 silver badges79 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: best way to call the same function multiple times in one query?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related