Optimizing cursor loops in relational database systems using custom aggregates

A method for converting a loop to a declarative structure query language (SQL) query that invokes a custom aggregate function, the method comprising:

• receiving the loop for execution, the loop comprising a select query and a loop body including a program fragment that can be evaluated over a result set of the select query one row at a time;
• automatically constructing the custom aggregate function according to a contract, the custom aggregate function expressing the loop body;
• and
• automatically rewriting the select query to the declarative SQL query that invokes the custom aggregate function.

A method for converting a FOR loop comprising a fixed iteration space to a declarative structured query language (SQL) query that invokes a custom aggregate function, the method comprising:

• rewriting the fixed iteration space of the FOR loop as a select query using common table expressions (CTE) based on a CTE template;
• defining a cursor on the select query, the cursor comprising a loop body from the FOR loop;
• automatically constructing the custom aggregate function according to a contract, the custom aggregate function expressing the loop body of the cursor;
• and
• automatically rewriting the select query to form the declarative SQL query that invokes the custom aggregate function.