Update record of a cursor where the table name is a parameter

Question

I am adjusting some PL/pgSQL code so my refcursor can take the table name as parameter. Therefore I changed the following line:

declare
 pointCurs CURSOR FOR SELECT * from tableName for update;

with this one:

OPEN pointCurs FOR execute 'SELECT * FROM ' || quote_ident(tableName) for update;

I adjusted the loop, and voilà, the loop went through. Now at some point in the loop I needed to update the record (pointed by the cursor) and I got stuck. How should I properly adjust the following line of code?

UPDATE tableName set tp_id = pos where current of pointCurs;

I fixed the quotes for the tableName and pos and added the EXECUTE clause at the beginning, but I get the error on the where current of pointCurs.

Questions:

(i) How can I update the record?
(ii) The function was working properly for tables from the public schema and failed for tables from other schemas (e.g., trace.myname).

Any comments are highly appreciated..

Answer 1

Answer for (i)

1. Explicit (unbound) cursor

EXECUTE is not a "clause", but a PL/pgSQL command to execute SQL strings. Cursors are not visible inside the command. You need to pass values to it.

Hence, you cannot use the special syntax WHERE CURRENT OFcursor. I use the system column ctid instead to determine the row without knowing the name of a unique column. Note that ctid is only guaranteed to be stable within the same transaction.

CREATE OR REPLACE FUNCTION f_curs1(_tbl text)
  RETURNS void AS
$func$
DECLARE
   _curs refcursor;
   rec record;
BEGIN
   OPEN _curs FOR EXECUTE 'SELECT * FROM ' || quote_ident(_tbl) FOR UPDATE;

   LOOP
      FETCH NEXT FROM _curs INTO rec;
      EXIT WHEN rec IS NULL;

      RAISE NOTICE '%', rec.tbl_id;

      EXECUTE format('UPDATE %I SET tbl_id = tbl_id + 10 WHERE ctid = $1', _tbl)
      USING rec.ctid;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Why format() with %I?

There is also a variant of the FOR statement to loop through cursors, but it only works for bound cursors. We have to use an unbound cursor here.

2. Implicit cursor in FOR loop

There is normally no need for explicit cursors in plpgsql. Use the implicit cursor of a FOR loop instead:

CREATE OR REPLACE FUNCTION f_curs2(_tbl text)
  RETURNS void AS
$func$
DECLARE
   _ctid tid;
BEGIN
   FOR _ctid IN EXECUTE 'SELECT ctid FROM ' || quote_ident(_tbl) FOR UPDATE
   LOOP
      EXECUTE format('UPDATE %I SET tbl_id = tbl_id + 100 WHERE ctid = $1', _tbl)
      USING _ctid;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

3. Set based approach

Or better, yet (if possible!): Rethink your problem in terms of set-based operations and execute a single (dynamic) SQL command:

-- Set-base dynamic SQL
CREATE OR REPLACE FUNCTION f_nocurs(_tbl text)
  RETURNS void AS
$func$
BEGIN
   EXECUTE format('UPDATE %I SET tbl_id = tbl_id + 1000', _tbl);
   -- add WHERE clause as needed
END
$func$  LANGUAGE plpgsql;

SQL Fiddle demonstrating all 3 variants.

Answer for (ii)

A schema-qualified table name like trace.myname actually consists of two identifiers. You have to

• either pass and escape them separately,
• or go with the more elegant approach of using a regclass type:

CREATE OR REPLACE FUNCTION f_nocurs(_tbl regclass)
  RETURNS void AS
$func$
BEGIN
   EXECUTE format('UPDATE %s SET tbl_id = tbl_id + 1000', _tbl);
END
$func$  LANGUAGE plpgsql;

I switched from %I to %s, because the regclass parameter is automatically properly escaped when (automatically) converted to text.
More details in this related answer:

• Table name as a PostgreSQL function parameter