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..

Peck

How do you know the column is named tp_id if the table name is dynamic? – Delladelle

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

Delladelle

Excellent answer. Thank you for all the time you spent on it. I'll be following your posts for the piece of wisdom. Quick follow up question: what's the complexity of EXECUTE format('UPDATE %I SET tbl_id = tbl_id + 100 WHERE ctid = $1', _tbl)? Would postgres create the fully-fledged SQL query (with all the syntactic and semantic checking, plan computation and so on) to identify and update the record, or it tells postgres, that it could use the pointer to the data pointed out by the cursor to update one field in it? – Peck

@arthur: There is no pointer involved in this UPDATE. It's dynamic SQL, which has to be planned and executed. If it wasn't dynamic, the query plan would saved and reused. Since Postgres 9.2 the query planner tries to be smart about the decision whether to reuse ... – Delladelle

thanks a lot for the answer (although it is somewhat depressing that I cannot update a field pointed by a refcursor). Now I am simply returning the whole updated table as an answer of the function (which replaced the original table with the updated one). This must be faster compared to bombing postgres with individual update queries, what do you think? Unfortunatelly, I cannot formulate the update in terms of sets as tp_id can only be computed during the sequential scan of the table :-( – Peck

Thanks for the sqlfiddle!! totally helped me figure out what I was missing. – Schoof