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@xxxxxxxxxxx>: > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general