Skip site navigation (1) Skip section navigation (2)

Re: Tuning queries inside a function

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
Cc: Mike Nolan <nolan(at)gw(dot)tssi(dot)com>,Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>,pgsql general list <pgsql-general(at)postgresql(dot)org>
Subject: Re: Tuning queries inside a function
Date: 2005-05-02 19:05:08
Message-ID: 1025.1115060708@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> Mike Nolan wrote:
>> That part I get, but I cannot seem to get an 'explain select....' to return
>> the explain output inside a function.

> Oh interesting. Hmmm. Alvaro can you think of a way to execute the 
> result into a variable and return it as a notice?

I think it's done already, at least if you are using a recent release.
I note the following relevant items in the CVS log:

2005-04-05 14:05  tgl

	* doc/src/sgml/plpgsql.sgml, src/pl/plpgsql/src/gram.y: Adjust
	grammar for plpgsql's OPEN command so that a cursor can be OPENed
	on non-SELECT commands such as EXPLAIN or SHOW (anything that
	returns tuples is allowed).  This flexibility already existed for
	bound cursors, but OPEN was artificially restricting what it would
	take.  Per a gripe some months back.

2005-02-10 15:36  tgl

	* src/backend/: executor/spi.c, tcop/pquery.c (REL8_0_STABLE),
	executor/spi.c, tcop/pquery.c: Fix SPI cursor support to allow
	scanning the results of utility commands that return tuples (such
	as EXPLAIN).  Per gripe from Michael Fuhr.  Side effect: fix an old
	bug that unintentionally disabled backward scans for all
	SPI-created cursors.

(The latter is in 8.0.2 and up, the former only in CVS tip.)

This is relevant to plpgsql because both "FOR ... IN query" and plpgsql
cursors depend on SPI cursors.

			regards, tom lane

In response to

pgsql-general by date

Next:From: Dianne ChenDate: 2005-05-02 19:22:31
Subject: Re: 7.3.9 Install Question - init.d/postgresql error?
Previous:From: Peter WilsonDate: 2005-05-02 18:56:27
Subject: Re: DBmirror replication - replacement for DBMirror.pl

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group