Re: Function execution consuming lot of memory and eventually making server unresponsive - Mailing list pgsql-performance

Merlin Moncure <mmoncure@gmail.com> writes:
> Your entire function could probably be reduced to one SQL expression
> with some thought.

Or if not that, at least try to get rid of the use of varchar.  All
those forced varchar-to-date-and-back conversions are expensive.
I'm also more than a tad worried by this:

>        v_loopingdate := TO_CHAR(DATE(p_startdate), ''mm-dd-yyyy'');
>
>        WHILE (DATE(v_loopingdate) <= DATE(p_enddate)) LOOP

There's nothing here guaranteeing that DATE() will think its input
is in mm-dd-yyyy format.  If DateStyle is set to something else,
the logic would at least be wrong, and very possibly that explains
your infinite loop.

Learn to use PG's type system instead of fighting it.  Your code
will be shorter, clearer, faster, and less error-prone.

            regards, tom lane