Test if anything was updated in dynamic query

Question

I need to construct and execute an UPDATE statement dynamically. Then I need to test whether anything was updated at all. My code is as follows:

DECLARE
  v_table_name text;
  v_column_name text;
  v_debug_flag boolean;
  v_upd_stmt text;
BEGIN
  select nm_table_name, replace(nm_table_name, 'CDB_', 'ID_')
    into strict v_table_name, v_column_name
    from m_entity e
    where id=12;

  v_upd_stmt := format('update %s set id_lock_usr =null, dt_lock=null where %s=$1 returning id_lock_usr',
                         v_table_name,
                         v_column_name);
  execute v_upd_stmt using p_id;

END

How to know if anything was updated?

You should provide a *complete* function. The header and other declarations are almost always relevant. And always your version of Postgres, too. — Erwin Brandstetter, Apr 14 '17 at 16:44

score 1 · Accepted Answer · edited May 23 '17 at 10:31

How to know if anything was updated ?

Various options. In a plpgsql function, you can check the special variable FOUND to see if the last SQL command affected any rows.

IF FOUND THEN ...

However, For dynamic queries with EXECUTE use GET DIAGNOSTICS instead. The manual:

Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.

Dynamic SQL (EXECUTE) as condition for IF statement

Aside: I see lingering problems with properly escaped identifiers (especially with upper case table names like you have), possibly even SQL injection. Fix with:

DECLARE
   v_table_name text;
   v_column_name text;
   v_id_lock_usr integer;  -- guessing the data type
   i integer;
BEGIN
   SELECT nm_table_name, replace(nm_table_name, 'CDB_', 'ID_')
   INTO   strict v_table_name, v_column_name
   FROM   m_entity e
   WHERE  id = 12;

   EXECUTE format('UPDATE %I SET id_lock_usr = null, dt_lock = null
                   WHERE %I = $1 RETURNING id_lock_usr'
                 , v_table_name,
                 , v_column_name)
   INTO v_id_lock_usr;  -- to store the *single* result from RETURNING

   GET DIAGNOSTICS i = ROW_COUNT;
   IF i > 0 THEN
      -- do something
   END IF;
END

Note %I instead of %s.

In your case, if id_lock_usr returned by your query is NOT NULL (reliably), you might just test the result directly instead:

IF v_id_lock_usr IS NOT NULL ...

And you might want to schema-qualify table names to avoid ambiguities. But you must escape schema_name.table_name as two separate identifiers ..

@VadBoro: Right, sorry, for dynamic queries with `EXECUTE` you need `GET DIAGNOSTICS` (as explained in the linked answer). I updated accordingly. — Erwin Brandstetter, Apr 17 '17 at 22:02

Test if anything was updated in dynamic query

1 Answers1