Forum OpenACS Q&A: Response to Postgres feature questions

Collapse
Posted by Daryl Biberdorf on

I just use Vim's split screen feature to leave the cursor definitions open in the top window, while scrolling the code at the bottom. I'd have to split the screen anyway if the query in question spans lots of line (as many of mine do) -- having the LOOP six or seven lines away from the FOR is a REAL readability disaster for me.

The use of explicit cursors also allows me to define variables and parameters of the row type. For example:

CREATE PACKAGE BODY mypackage
IS
  CURSOR mycur IS SELECT col1, col2, col3 FROM mytable;

  PROCEDURE cleanup_row( p_row IN mycur%ROWTYPE )
  IS
  BEGIN
    -- do something with p_row
  END cleanup_row;
BEGIN
  FOR myrec IN mycur LOOP
    cleanup_row( myrec );
  END LOOP;
END mypackage;

As another point, on a recent code audit resulting from several key changes in our data model, I had to look through all our PL/SQL procedures to find the affected queries. Those that were declared as parameterized cursors in the header of each program block were LOTS easier to find and fix than those that made use of program variables (e.g., SELECT ... FROM ... WHERE colname = varname).

Basically, I guess I like the fact that explicit, parameterized cursors allow me to treat queries as functions that return result sets.

As you say, a lot of it's a matter of taste. However, I'm not convinced that performance should be the sole criterion here. I really value Mr. Kyte's thoughts on the matter, but he only rarely uses queries of any significant size in his examples. That, and I don't want to create views for queries that are used in exactly one program unit.