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