DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=aAM7cbUFN1cDTHpHjonVt81QeuMgAr0fZoBofuB6TKqJlGgPiFN/+pWUp1drpQh64xP98sgL11rVWK7mgsge+7NiFzX6kYk1Uy4CvGcSJm9JDlZ6llV0rzMb+DdwY4W+tC3yX7P5LNwFUe+MZAO794qgGW37j/kWwn6pFWNDdzk=
Message-ID: <162867790711011405x22b205afj9b89645e4f6a2174@mail.gmail.com>
Date: Thu, 1 Nov 2007 22:05:38 +0100
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: "Dawid Kuroczko" <qnex42@gmail.com>
Subject: Re: Scrolling cursors in PL/PgSQL
Cc: "Postgres General" <pgsql-general@postgresql.org>,
	pgsql-sql <pgsql-sql@postgresql.org>
In-Reply-To: <758d5e7f0711011319t5e752dsad6481d1427a46ff@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <758d5e7f0711011319t5e752dsad6481d1427a46ff@mail.gmail.com>

On 01/11/2007, Dawid Kuroczko <qnex42@gmail.com> wrote:
> Hello.
>
> Is there a way to scroll a cursor from within PL/PgSQL?
>
> I tried EXECUTE, but:
>
> ERROR:  cannot manipulate cursors directly in PL/pgSQL
> HINT:  Use PL/pgSQL's cursor features instead.
>
> The idea would be that PL/pgsql function would look
> through (all) query results, then rewind the cursor and
> finally return it.
>
> My fellow developers have created a function which run
> the (complex) query twice (first a count(*) on the result
> set, then return the row count and cursor to the results.
> Then they created a version which uses temporary
> table as a placeholder for the query results, and results
> count and a pointer to select * from tmp_table.
>
> Now I imagined a function which would open the cursor,
> MOVE FORWARD ALL, then MOVE ABSOLUTE 0;
> and return count and a cursor, yet it seems it won't work.
>
> Any other idea how to efficiently solve such a problem?
>

Hello

PostgreSQL 8.3 allows scrollable cursors in plpgsql

Regards
Pavel Stehule