sql - 测试动态查询中是否有任何更新

Question

我需要动态地构造和执行 UPDATE 语句。然后我需要测试是否有任何更新。我的代码如下:

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

如何知道是否有任何更新？

Answer 1

How to know if anything was updated ?

各种选择。在 plpgsql 函数中，您可以检查 special variable FOUND查看最后一个 SQL 命令是否影响了任何行。

IF FOUND THEN ...

但是，对于使用 EXECUTE 的动态查询，请改用 GET DIAGNOSTICS。 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

旁白:我看到正确转义的标识符(尤其是像您这样的大写表名)存在挥之不去的问题，甚至可能是 SQL 注入(inject)。修复:

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

请注意 %I 而不是 %s。

在您的情况下，如果您的查询返回的 id_lock_usr 是 NOT NULL(可靠)，您可能只是直接测试结果:

IF v_id_lock_usr IS NOT NULL ...

您可能希望对表名进行模式限定以避免歧义。但是你必须将 schema_name.table_name 作为两个单独的标识符转义..

相关:

• Are PostgreSQL column names case-sensitive?
• Define table and column names as arguments in a plpgsql function?
• Table name as a PostgreSQL function parameter