[HACKERS] Cursor estimated row count

[Michael Fuhr]

An occasionally asked question is "How can I find out how many rows
a cursor will return?" to which the answer is "Fetch them all."  But
what about a way to get the planner's estimate?  Would anybody find
that useful?  Does the code below look close to being correct?

test=> EXPLAIN SELECT * FROM pg_class;
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on pg_class  (cost=0.00..6.88 rows=188 width=163)
(1 row)

test=> BEGIN;
BEGIN
test=> DECLARE curs CURSOR FOR SELECT * FROM pg_class;
DECLARE CURSOR
test=> SELECT cursor_plan_rows('curs');
 cursor_plan_rows 
------------------
              188
(1 row)

#include "postgres.h"
#include "fmgr.h"

#include "nodes/pg_list.h"
#include "nodes/plannodes.h"
#include "utils/portal.h"

Datum   cursor_plan_rows(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(cursor_plan_rows);

Datum
cursor_plan_rows(PG_FUNCTION_ARGS)
{
    char    *portalname = PG_GETARG_CSTRING(0);
    Portal   portal;
    Plan    *plan;

    portal = GetPortalByName(portalname);

    if (!PortalIsValid(portal)) {
        ereport(ERROR,
                (errcode(ERRCODE_UNDEFINED_CURSOR),
                 errmsg("cursor \"%s\" does not exist", portalname)));
    }

    if (!portal->planTrees) {
        ereport(ERROR,
                (errcode(ERRCODE_INVALID_CURSOR_STATE),
                 errmsg("cursor \"%s\" has no plan trees", portalname)));
    }

    plan = linitial(portal->planTrees);

    if (!plan) {
        ereport(ERROR,
                (errcode(ERRCODE_INVALID_CURSOR_STATE),
                 errmsg("cursor \"%s\" plan is NULL", portalname)));
    }

    PG_RETURN_FLOAT8(plan->plan_rows);
}

-- 
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq