sql - Test if anything was updated in dynamic query -
i need construct , execute update
statement dynamically. need test whether updated @ all. code 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_') strict v_table_name, v_column_name m_entity e id=12; v_upd_stmt := format('update %s set id_lock_usr =null, dt_lock=null %s=$1 returning id_lock_usr', v_table_name, v_column_name); execute v_upd_stmt using p_id; end
how know if updated?
how know if updated ?
various options. in plpgsql function, can check special variable found
see if last sql command affected rows.
if found ...
however, dynamic queries execute
use get diagnostics
instead. the manual:
note in particular
execute
changes output ofget diagnostics
, not changefound
.
related:
aside: see lingering problems escaped identifiers (especially upper case table names have), possibly sql injection. fix with:
declare v_table_name text; v_column_name text; v_id_lock_usr integer; -- guessing data type integer; begin select nm_table_name, replace(nm_table_name, 'cdb_', 'id_') strict v_table_name, v_column_name m_entity e id = 12; execute format('update %i set id_lock_usr = null, dt_lock = null %i = $1 returning id_lock_usr' , v_table_name, , v_column_name) v_id_lock_usr; -- store *single* result returning diagnostics = row_count; if > 0 -- end if; end
note %i
instead of %s
.
in case, if id_lock_usr
returned query not null
(reliably), might test result directly instead:
if v_id_lock_usr not null ...
and might want schema-qualify table names avoid ambiguities. must escape schema_name.table_name
2 separate identifiers ..
related:
Comments
Post a Comment