Обсуждение: PL/pgSQL - Help or advice please on using unbound cursors

Hi all,

Apologies if the answer to my question is "obvious", but I'm fairly new
to writing functions in PG. I think my problem is has a simple solution,
but I'm damned if I can find it :-/

(Postgres v9.1.1 on Linux 64-bit)

I'm trying to write a function which will :-

1. Take 3 input params; a catalog, schema and name for a table (to
uniquely identify the target table)
2. Take further input params indicating the ORDER by clauses when
reading the table (see step 4)
3. Identify and drop the primary key from the table
4. Create a cursor to scan the table in the required sequence
5. UPDATE the record currently referenced by the cursor to set a new
primary key.
6. Close the cursor
7. Restore the primary key

I'm stuck on step 5 when looping around the records returned from the
cursor. Reading the doco (from both PG and Oracle), I believe I can only
use an unbound cursor when the SELECT statement is built dynamically via
the function, so using the FOR/NEXT construct is not an option as that
only works with bound cursors.

The problem I have is that I cannot for the life of me work out how I
check for dropping off the end of the table when I cursor down it.
Here's an example code fragment where I'm cursoring down the cursor
results and attempting to detect I've dropped of the end :-

EXECUTE ''DECLARE cursor1 CURSOR FOR SELECT "ident" FROM '' || tableHN
|| '' ORDER BY "Name" FOR UPDATE'';
LOOP
     EXECUTE ''FETCH NEXT FROM cursor1 INTO rec'';
     recCount = recCount + 1;
     RAISE NOTICE ''Fetched ok %'', recCount;
     IF FOUND THEN
         EXECUTE ''UPDATE '' || tableHN || '' SET "%1" = '' || recCount
|| '' WHERE CURRENT OF cursor1'';
     ELSE
         RAISE NOTICE ''Not Found'';
         EXIT;
      END IF;
END LOOP;

I never see the "Not Found" notice, so the "IF FOUND" test never appears
to be triggered. Although I can catch this with a BEGIN + EXCEPTION
triggered when the UPDATE call occurs after processing the last record,
this results in the transaction being rolled back, so I loose the
changes. I've also tried using "IF cursor1%notfound" but I get an error
which I guess is because the cursor is not a bound cursor.

Any advice on the "correct" way to detect end-of-resultset when using a
cursor in this way or any other thoughts please.

Many thanks
Andrew

Hello

dynamic SQL has not impact on FOUND variable - use GET DIAGNOSTICS
varname = ROW_COUNT instead.

Regards

Pavel Stehule



2012/7/23 Andrew Hastie <andrew@ahastie.net>:
> Hi all,
>
> Apologies if the answer to my question is "obvious", but I'm fairly new to
> writing functions in PG. I think my problem is has a simple solution, but
> I'm damned if I can find it :-/
>
> (Postgres v9.1.1 on Linux 64-bit)
>
> I'm trying to write a function which will :-
>
> 1. Take 3 input params; a catalog, schema and name for a table (to uniquely
> identify the target table)
> 2. Take further input params indicating the ORDER by clauses when reading
> the table (see step 4)
> 3. Identify and drop the primary key from the table
> 4. Create a cursor to scan the table in the required sequence
> 5. UPDATE the record currently referenced by the cursor to set a new primary
> key.
> 6. Close the cursor
> 7. Restore the primary key
>
> I'm stuck on step 5 when looping around the records returned from the
> cursor. Reading the doco (from both PG and Oracle), I believe I can only use
> an unbound cursor when the SELECT statement is built dynamically via the
> function, so using the FOR/NEXT construct is not an option as that only
> works with bound cursors.
>
> The problem I have is that I cannot for the life of me work out how I check
> for dropping off the end of the table when I cursor down it. Here's an
> example code fragment where I'm cursoring down the cursor results and
> attempting to detect I've dropped of the end :-
>
> EXECUTE ''DECLARE cursor1 CURSOR FOR SELECT "ident" FROM '' || tableHN || ''
> ORDER BY "Name" FOR UPDATE'';
> LOOP
>     EXECUTE ''FETCH NEXT FROM cursor1 INTO rec'';
>     recCount = recCount + 1;
>     RAISE NOTICE ''Fetched ok %'', recCount;
>     IF FOUND THEN
>         EXECUTE ''UPDATE '' || tableHN || '' SET "%1" = '' || recCount || ''
> WHERE CURRENT OF cursor1'';
>     ELSE
>         RAISE NOTICE ''Not Found'';
>         EXIT;
>      END IF;
> END LOOP;
>
> I never see the "Not Found" notice, so the "IF FOUND" test never appears to
> be triggered. Although I can catch this with a BEGIN + EXCEPTION triggered
> when the UPDATE call occurs after processing the last record, this results
> in the transaction being rolled back, so I loose the changes. I've also
> tried using "IF cursor1%notfound" but I get an error which I guess is
> because the cursor is not a bound cursor.
>
> Any advice on the "correct" way to detect end-of-resultset when using a
> cursor in this way or any other thoughts please.
>
> Many thanks
> Andrew
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Yep, that fixed it. Many thanks for the pointer.

 From a performance point of view, I did look at using MOVE rather than
FETCH before I call UPDATE as I don't actually need to read the data
before applying the update. However in this situation, the ROW_COUNT is
always zero and can't be used which I suspect is reasonable.

Anyway, thanks for the help.