Functionality for bulk update #7
Description
The ll_bitemporal_update_select is limited when it comes to doing a bulk bitemporal update. It allows updating of multiple records but only if the data is same (or uses context of the updating row).
It would be great if the same behaviour can be made possible as in ll_bitemporal_insert_select. Which allows you to insert multiple records with different data.
I did some changes to the function and made it to work for my case. It would be great if you can review it. Happy to raise a PR if you don't see any problems in it.
There are 4 major operations that happens in the update_select function:
- Ending the assertion period of old records.
- Inserting a new record with effective period ended.
- Insert a new record with old values and specified effective and assertive range.
- Update the newly inserted record with the values that needs to be updated.
The 4th step uses this update statement to update the records:
format($u$ UPDATE %s t SET (%s) = (%s)
WHERE ( %s ) in ( %s ) $u$
, v_table
, p_list_of_fields
, p_values_selected_update
, v_serial_key
, array_to_string(v_keys,','));
For this to work the p_values_selected_update select query must return only one record.
If we can modify it to use the FROM clause, we can achieve the bulk update. Here's what I've done:
This is what my function call looks like:
SELECT bitemporal_internal.ll_bitemporal_update_select(
'org',
'test_table,
'id,first_name,emails',
$$SELECT id, first_name, emails FROM (VALUES ('4143558004', 'Raghav', CAST(ARRAY['[email protected]'] AS text[])), ('4028052004', 'Hettie', CAST(ARRAY['[email protected]'] AS text[]))) ASt(id, first_name, emails)$$,
'id',
$$SELECT id FROM (VALUES ('4143558004', 'Raghav', CAST(ARRAY['[email protected]'] AS text[])), ('4028052004', 'Hettie', CAST(ARRAY['[email protected]'] AS text[]))) AS t(id, first_name, emails)$$,
TEMPORAL_RELATIONSHIPS.TIMEPERIOD(NOW(), CAST('infinity' AS temporal_relationships.time_endpoint)),
TEMPORAL_RELATIONSHIPS.TIMEPERIOD(NOW(), CAST('infinity' AS temporal_relationships.time_endpoint)))
Here's what I modified in the function:
EXECUTE format($s$ SELECT 'x.' || REPLACE(%L, ',', ',x.') $s$, p_list_of_fields) into x_alias_list_of_fields;
EXECUTE format($s$ SELECT 'x.' || REPLACE(%L, ',', ',x.') $s$, p_search_fields) into x_alias_p_search_fields;
EXECUTE format($s$ SELECT 't.' || REPLACE(%L, ',', ',t.') $s$, p_search_fields) into t_alias_p_search_fields;
EXECUTE format($u$ UPDATE %s t SET (%s) = (%s)
FROM (%s) as x WHERE ( %s ) in ( %s ) AND (%s) = (%s) $u$
, v_table
, p_list_of_fields
, x_alias_list_of_fields
, p_values_selected_update
, v_serial_key
, array_to_string(v_keys,',')
, x_alias_p_search_fields
, t_alias_p_search_fields);
The update query will now look like:
UPDATE
test_table t
SET
(id, first_name, emails) = (x.id, x.first_name, x.emails)
FROM (
SELECT
id,
first_name,
emails
FROM (VALUES
('4143558004', 'Raghav', CAST(ARRAY['[email protected]'] AS text[])),
('4028052004', 'Hettie', CAST(ARRAY['[email protected]'] AS text[]))
) AS t(id, first_name, emails)
) AS x
WHERE (test_table_bt_key) IN (83, 84) AND (x.id) = (t.id)