Understanding memory

When a procedure is written, it makes sense to think for a moment about memory consumption. There are three important issues here:

• Procedures and cursors

• Handling set returning functions

• Assigning memory parameters to functions

In this section, you will be guided through all of these topics.

Procedures and cursors

The first thing to be discussed is the way large result sets should be handled inside a procedure. In normal programming, the general rule is that in the case of large amounts of data, a cursor is needed. The idea behind a cursor is that only small portions of data are fetched and processed at a time.

Languages such as PL/pgSQL provide functionality to open, close, and fetch data from cursors. However, in most cases, you don't actually need to rely on this functionality. The beauty of a procedural language is that internally, everything is a cursor! Here is an example:

CREATE FUNCTION process() RETURNS int AS
$$
        DECLARE
                v_rec           RECORD...