How to work with a iterator variable on a dynamic select query in PL/pgSQL?

Question

I need to deliver a cash flow report in PostgreSQL 9.1.

In the balance line (BalanceLine), a function (getBalanceLine) is responsible for fetching the sum of the bank accounts and its credit or debit position.

The number row (num_row) needs to be decreased until zero to indicate to the function getBalanceLine that a temporary table has to be DROPed. That temporary table keeps the balance line results as "working memory" for the calculations.

The problem is that the num_row variable, on the function getBalanceLine is not decreasing according with instructions on my LOOP clause.

The following is the PL/pgSQL function:

DROP FUNCTION IF EXISTS report_cash_flow();

CREATE FUNCTION report_cash_flow() RETURNS TABLE(Date date, Company varchar(128), Bank varchar(64), Partner varchar(128), Document varchar(64), Credit numeric, Debit numeric, Line integer, BalanceLine numeric) AS 
$BODY$

DECLARE
    num_row int := 0;

BEGIN

    num_row = ( SELECT
                COUNT(*)
            FROM
                account_move_line aml
                INNER JOIN  res_company rc ON rc.id = aml.company_id
                INNER JOIN  res_partner rp ON rp.id = aml.partner_id
                INNER JOIN  account_journal aj ON aj.id = aml.journal_id
                INNER JOIN  account_account aa ON aa.id = aml.account_id
            WHERE
                aml.state = 'valid'
                AND aml.reconcile_id IS NULL );


    FOR Date, Company, Bank, Partner, Document, Credit, Debit, Line, BalanceLine IN

        SELECT
            aml.date_maturity AS Date,
            rc.name AS Company,
            aj.name AS Bank,
            rp.name AS Partner,
            aml.name AS Document,
            aml.credit AS Credit,
            aml.debit AS Debit,
            num_row AS Line,
            getBalanceLine(aml.credit, aml.debit, num_row) AS BalanceLine
        FROM
            account_move_line aml
            INNER JOIN  res_company rc ON rc.id = aml.company_id
            INNER JOIN  res_partner rp ON rp.id = aml.partner_id
            INNER JOIN  account_journal aj ON aj.id = aml.journal_id
            INNER JOIN  account_account aa ON aa.id = aml.account_id
        WHERE
            aml.state = 'valid'
            AND aml.reconcile_id IS NULL
        ORDER BY
            Document

        LOOP
            num_row := num_row - 1;
            RAISE NOTICE '%', num_row;
            RETURN NEXT;

        END LOOP;

    RETURN;

END;
$BODY$
LANGUAGE 'plpgsql';

SELECT * FROM report_cash_flow();

The following is the result of the query:

date        company     bank    partner     document    credit  debit     line  balanceline
01/10/2013  Company 1   Bank 1  Partner 1   00003621/1  0.00    520.56    4       1.024,00
01/10/2013  Company 1   Bank 2  Partner 2   00003622/1  32.00   0.00      4         922,00
09/10/2014  Company 1   Bank 1  Partner 3   00003623/1  0.00    18009.65  4     -17.087,65
10/10/2014  Company 1   Bank 2  Partner 4   00003624/1  6126.95 0.00      4     -10.960,70

The following is the result of RAISE NOTICE '%', num_row:

NOTICE:  4
NOTICE:  3
NOTICE:  2
NOTICE:  1

Answer 1

The query providing the rows for your FOR loop is executed once before the first iteration of the loop. You would have to call the function getBalanceLine() in the loop, not in the base query.

However, your whole approach is needlessly verbose and expensive.

Simplify, step 1

CREATE FUNCTION report_cash_flow()
  RETURNS TABLE(Date date, Company text, Bank text, Partner text, Document text, Credit numeric, Debit numeric, Line integer, BalanceLine numeric) AS 
$func$
DECLARE
    _iterator int := 0;
BEGIN
    FOR Date, Company, Bank, Partner, Document, Credit, Debit, Line IN
        SELECT  aml.date_maturity AS Date -- alias useless
                rc.name AS Company,
                aj.name AS Bank,
                rp.name AS Partner,
                aml.name AS Document,
                aml.credit AS Credit,
                aml.debit AS Debit,
                count(*) OVER () AS Line  -- = initial num_row
        FROM    account_move_line aml
        JOIN    res_company       rc ON rc.id = aml.company_id
        JOIN    res_partner       rp ON rp.id = aml.partner_id
        JOIN    account_journal   aj ON aj.id = aml.journal_id
        JOIN    account_account   aa ON aa.id = aml.account_id
        WHERE   aml.state = 'valid'
        AND     aml.reconcile_id IS NULL
        ORDER   BY Document
    LOOP
        BalanceLine := getBalanceLine(Credit, Debit, Line - _iterator);
        RETURN NEXT;
        _iterator := _iterator + 1;
        RAISE NOTICE '%', Line - _iterator;         
    END LOOP;
    RETURN;
END
$func$ LANGUAGE plpgsql;

• Execute function in loop.
• No need to run a second query for the count. You can do this in a single SELECT with a window function.
• Introduce an additional variable to count loops, _iterator in my case.
  I prepend variables with _ to avoid naming collisons.
• Don't quote the language name plpgsql, it's an identifier.

Simplify, step 2

You can probably use this single plain query with window functions instead:

SELECT aml.date_maturity AS Date
     , rc.name AS Company
     , aj.name AS Bank
     , rp.name AS Partner
     , aml.name AS Document
     , aml.credit
     , aml.debit
     , count(*) OVER () AS Line  -- or the decreasing number?
     , getBalanceLine(aml.credit
                    , aml.debit
                    , count(*) OVER () + 1 -
                      row_number() OVER (ORDER  BY Document)) AS BalanceLine 
FROM   account_move_line aml
JOIN   res_company       rc ON rc.id = aml.company_id
JOIN   res_partner       rp ON rp.id = aml.partner_id
JOIN   account_journal   aj ON aj.id = aml.journal_id
JOIN   account_account   aa ON aa.id = aml.account_id
WHERE  aml.state = 'valid'
AND    aml.reconcile_id IS NULL
ORDER  BY Document;

Or use the more sophisticated:

count(*) OVER (ORDER BY Document
               ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

More explanation:

• How to use a ring data structure in window functions