Thread: WIP: updatable cursors in plpgsql

Hello,

this small patch allows using updatable cursors in plpgsql.

Regards
Pavel Stehule

"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> this small patch allows using updatable cursors in plpgsql.

Why do we need this?

            regards, tom lane

2007/6/11, Tom Lane <tgl@sss.pgh.pa.us>:
> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
> > this small patch allows using updatable cursors in plpgsql.
>
> Why do we need this?
>
For stored procedures. Updatable cursors are used mainly in transform
procedures, and without suppport in plpgsql, you have to write
external procedure. It similar with support scrollable cursors, which
was added into plpgsql now. It's not strong argument. With this patch
will be less diference between cursors supported by PostgreSQL and
cursors in plpgsql.

Updatable cursor are currently substituted using ctid, but updatable
cursors are more clean and readable.

PL/pgSQL can be consistent in support PostgreSQL SQL statements. It's
little bit strange, PostgreSQL offer some functionality, which cannot
be used from PL/pgSQL.

Regards
Pavel Stehule

"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> 2007/6/11, Tom Lane <tgl@sss.pgh.pa.us>:
>> Why do we need this?
>>
> For stored procedures.

No, the question is what is the patch trying to accomplish, because
as far as I can see it's wrong.  It seems to be trying to insert the
plpgsql name of the cursor, which is not necessarily the SQL name.

            regards, tom lane

>
> No, the question is what is the patch trying to accomplish, because
> as far as I can see it's wrong.  It seems to be trying to insert the
> plpgsql name of the cursor, which is not necessarily the SQL name.
>


All explicit cursors (what I know) use named SQL cursors. SQL name is
checked in OPEN statement. Refcursors are problematic. But refcursors
are not updatable.

We have to check this case and raise error. It's correct - holdable
cursors aren't updatable. It's WIP patch

Regards
Pavel Stehule

"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> All explicit cursors (what I know) use named SQL cursors. SQL name is
> checked in OPEN statement. Refcursors are problematic. But refcursors
> are not updatable.

Sure they are, and besides which a bound cursor can still have a name
different from the SQL name --- you just assign something to it before
opening it.  So this patch just plain doesn't work.

As the code stands plpgsql will try to issue something like

    UPDATE/DELETE ... WHERE CURRENT OF $1

Since we don't try to do anything with the cursor name until runtime,
it seems that this would work if we allowed a parameter symbol there.
Offhand that doesn't look hard.

            regards, tom lane

>
> As the code stands plpgsql will try to issue something like
>
>         UPDATE/DELETE ... WHERE CURRENT OF $1
>
> Since we don't try to do anything with the cursor name until runtime,
> it seems that this would work if we allowed a parameter symbol there.
> Offhand that doesn't look hard.
>

I tested it. It's great. Thank You
Pavel Stehule