CODEWITHSUNDEEP
performancepostgresql
Kamran Omar
Kamran Omar

Reputation: 1887

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

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?

Upvotes: 0

Views: 2536

Answers (2)

ertx
ertx

Reputation: 1544

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;

Upvotes: 3

aib
aib

Reputation: 46921

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
)

Upvotes: 1

Related Questions