Re: Function returning 2 columns evaluated twice when both columns are needed - Mailing list pgsql-general

Gerhard Wiesinger <lists@wiesinger.com> writes:
> Since getSums() is a cursor and is complex and takes long time getSums
> should only be evaluated once. Is there a better solution available to
> get both columns from the function in the select?

You need a sub-select, along the lines of

SELECT
  cur_date,
  (gs).sum_m1,
  (gs).sum_m2
  FROM
  (
   SELECT
     cur_date,
     getSums(start_ts, stop_ts) AS gs
   FROM
     getDatesTimestamps($1, $2)
   OFFSET 0
  ) AS ss
   ;

The OFFSET bit is a kluge, but is needed to keep the planner from
flattening the subquery and undoing your work.

            regards, tom lane