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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general