Re: PL/pgSQL - Help or advice please on using unbound cursors

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(at)ahastie(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general