Thread: Prepare/Declare

Am I correct to assume that the backend does not implement cursor
declaration from prepared statements? If so, is there a reason for this?

That is something like this:

PREPARE p AS   SELECT * FROM foo;
DECLARE c CURSOR for p;

AFAIRC the standard says this group of statements are perfectly legal and ecpg
accepts it with its old logic. Moving to real prepared statement I don't
like to lose this feature. 

Michael
-- 
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

Michael Meskes <meskes@postgresql.org> writes:
> PREPARE p AS
>     SELECT * FROM foo;
> DECLARE c CURSOR for p;

> AFAIRC the standard says this group of statements are perfectly legal

I'd be interested to see where you draw that conclusion, since
(a) PREPARE statements of that form are not in the standard, and
(b) DECLARE CURSOR is clearly defined as taking a <query expression>.

It doesn't seem like an amazingly sensible thing to do, either.
DECLARE CURSOR affects the plan generated for its query (eg,
backwards-scrollability may be required), so it's not certain that
a previously PREPARE'd plan could be used.

You can achieve something approximating this at the protocol level,
since you can do partial fetches from a portal created by Bind'ing
the prepared statement.  That won't let you fetch backwards nor
persist the cursor past end of transaction, but maybe you don't
need those things.
        regards, tom lane

On Thu, May 24, 2007 at 04:07:27PM -0400, Tom Lane wrote:
> Michael Meskes <meskes@postgresql.org> writes:
> > PREPARE p AS
> >     SELECT * FROM foo;
> > DECLARE c CURSOR for p;
> 
> > AFAIRC the standard says this group of statements are perfectly legal
> 
> I'd be interested to see where you draw that conclusion, since
> (a) PREPARE statements of that form are not in the standard, and
> (b) DECLARE CURSOR is clearly defined as taking a <query expression>.

Sorry, should have been more precise. I was talking about embedded SQL
standard. Just look for "dynamic cursors".

> You can achieve something approximating this at the protocol level,
> since you can do partial fetches from a portal created by Bind'ing
> the prepared statement.  That won't let you fetch backwards nor
> persist the cursor past end of transaction, but maybe you don't
> need those things.

I could also keep my old simultaing code for this special case, which is
probably the best way to do it.

Michael
-- 
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

Michael Meskes <meskes@postgresql.org> writes:
> On Thu, May 24, 2007 at 04:07:27PM -0400, Tom Lane wrote:
>> I'd be interested to see where you draw that conclusion, since
>> (a) PREPARE statements of that form are not in the standard, and
>> (b) DECLARE CURSOR is clearly defined as taking a <query expression>.

> Sorry, should have been more precise. I was talking about embedded SQL
> standard. Just look for "dynamic cursors".

Oh, I see what you're looking at.  But my point here is that this
version of PREPARE has zip to do with ours: it seems more akin to
plpgsql's EXECUTE, since AFAICT you are supposed to give it a string
value that then gets parsed as a SQL statement.  Also it lacks any
way to define parameters for the statement.

> I could also keep my old simultaing code for this special case, which is
> probably the best way to do it.

Yeah, I think keeping this version of PREPARE on the ecpg side is
probably best.
        regards, tom lane