Обсуждение: Cursor Error

Could somebody translate this error message for me??

Bob

"cursor <unnamed portal 1> is not simply updateable scan of table "p_id"

"Bob Pawley" <rjpawley@shaw.ca> writes:
> Could somebody translate this error message for me??
> "cursor <unnamed portal 1> is not simply updateable scan of table "p_id"

You're trying to do an "UPDATE WHERE CURRENT OF cursor", right?
What it means is that the cursor definition is too complicated for
Postgres to figure out which row to update.  Without seeing the
cursor definition it's hard to say more.

            regards, tom lane

Right.

This is the cursor statement.

 Open procgraphic for select p_id.p_id.process_id from p_id.p_id,
processes_count
 where p_id.p_id.p_id_id = processes_count.p_id_id;

 If process_total = 1 Then

  Fetch first from procgraphic into process_id;

  Update p_id.p_id
  set proc_graphic_position = '1'
  where current of procgraphic;

I get the same error message when I define the cursor with the same select.

I am not sure how to make the query simpler and still have it access the
right row on fetch.

Bob



----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "PostgreSQL" <pgsql-general@postgresql.org>
Sent: Thursday, July 31, 2008 9:30 AM
Subject: Re: [GENERAL] Cursor Error


> "Bob Pawley" <rjpawley@shaw.ca> writes:
>> Could somebody translate this error message for me??
>> "cursor <unnamed portal 1> is not simply updateable scan of table "p_id"
>
> You're trying to do an "UPDATE WHERE CURRENT OF cursor", right?
> What it means is that the cursor definition is too complicated for
> Postgres to figure out which row to update.  Without seeing the
> cursor definition it's hard to say more.
>
> regards, tom lane

"Bob Pawley" <rjpawley@shaw.ca> writes:
> Right.
> This is the cursor statement.

>  Open procgraphic for select p_id.p_id.process_id from p_id.p_id,
> processes_count
>  where p_id.p_id.p_id_id = processes_count.p_id_id;

Sorry, we're not bright enough to handle WHERE CURRENT OF on a join
--- per the fine manual,

    The cursor must be a simple (non-join, non-aggregate) query on
    the UPDATE's target table.

I don't recall offhand whether there's some deep technical reason
for the restriction against joins, or we just didn't get around to
it.  In any case, you'll need to change the cursor to return the
table's primary key and use that to target the UPDATE.

            regards, tom lane

Is it allowed to declare a cursor in this manner??

Declare
 procgraphic cursor for select p_id.p_id.process_id from p_id.p_id,
processes_count
   where p_id.p_id.p_id_id = processes_count.p_id_id;

Bob



----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "PostgreSQL" <pgsql-general@postgresql.org>
Sent: Thursday, July 31, 2008 9:50 AM
Subject: Re: [GENERAL] Cursor Error


> "Bob Pawley" <rjpawley@shaw.ca> writes:
>> Right.
>> This is the cursor statement.
>
>>  Open procgraphic for select p_id.p_id.process_id from p_id.p_id,
>> processes_count
>>  where p_id.p_id.p_id_id = processes_count.p_id_id;
>
> Sorry, we're not bright enough to handle WHERE CURRENT OF on a join
> --- per the fine manual,
>
> The cursor must be a simple (non-join, non-aggregate) query on
> the UPDATE's target table.
>
> I don't recall offhand whether there's some deep technical reason
> for the restriction against joins, or we just didn't get around to
> it.  In any case, you'll need to change the cursor to return the
> table's primary key and use that to target the UPDATE.
>
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Bob Pawley wrote:
> Is it allowed to declare a cursor in this manner??
>
> Declare
> procgraphic cursor for select p_id.p_id.process_id
> from p_id.p_id, processes_count
> where p_id.p_id.p_id_id = processes_count.p_id_id;

Using DECLARE instead of OPEN? Yes, but that won't somehow make a cursor
involving a join updatable. See:

http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html

DECLARE and OPEN do not have exactly the same meaning, as explained by
the above documentation. To use a cursor defined with DECLARE you must
use OPEN - see section 38.7.2.3 ("Opening a Bound Cursor") of the
documentation.

--
Craig Ringer