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
