Cursor estimated row count

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Cursor estimated row count
Date: 2005-11-12 18:31:44
Message-ID: 20051112183144.GA91710@winnie.fuhr.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2005-11-12 18:50:20 Re: Cursor estimated row count
Previous Message Tom Lane 2005-11-12 17:44:23 Re: SIGSEGV taken on 8.1 during dump/reload