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:

1. How can I update the record?
2. 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 1.

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, and only unique within a single physical table (no inheritance or partitioning). See:

• How to delete duplicate rows without unique identifier

CREATE OR REPLACE FUNCTION f_curs1(_tbl text)
  RETURNS void
  LANGUAGE plpgsql 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$;

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 PL/pgSQL. Use the implicit cursor of a FOR loop instead:

CREATE OR REPLACE FUNCTION f_curs2(_tbl text)
  RETURNS void
  LANGUAGE plpgsql 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$;

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:

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

fiddle - demonstrating all 3 variants
Old sqlfiddle

Answer for 2.

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
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('UPDATE %s SET tbl_id = tbl_id + 1000', _tbl);
END
$func$;

I switched from %I to %s, because the regclass parameter is automatically properly escaped when (automatically) converted to text. See:

• Table name as a PostgreSQL function parameter