This PL/pgSQL function implements an iterative binary search algorithm to efficiently find the index of a specific target value within a sorted array of integers. Binary search is a fundamental algorithm for searching in...

The `iterative_binary_search` function operates on a sorted array by repeatedly dividing the search interval in half. It initializes `low` and `high` pointers to the boundaries of the array. The `WHILE` loop continues as long as `low` is less than or equal to `high`, ensuring the search interval is valid. In each iteration, the `mid` index is calculated. If the element at `mid` matches the `target_value`, its index is returned. If the `target_value` is greater than `sorted_array[mid]`, the search continues in the right half by setting `low` to `mid + 1`. Conversely, if the `target_value` is smaller, the search continues in the left half by setting `high` to `mid - 1`. This process guarantees that if the target exists, it will be found. The time complexity is O(log n), where n is the number of elements in the array, due to the halving of the search space in each step. The space complexity is O(1) as it only uses a few variables. Edge cases like an empty array or the target not being present are handled by returning -1.

FUNCTION iterative_binary_search(sorted_array, target_value): IF sorted_array is empty THEN RETURN -1 END IF low = 0 (or 1 for 1-indexed arrays) high = length(sorted_array) - 1 (or length for 1-indexed arrays) WHILE low...

This PL/pgSQL function implements a robust transactional retry mechanism. It attempts to execute a critical database operation and, if a deadlock error (SQLSTATE '40P01') occurs, it automatically retries the operation up...

The function `perform_critical_operation_with_retry` uses a `LOOP` construct to repeatedly attempt an operation. Inside the loop, a `BEGIN...EXCEPTION` block handles potential errors. Specifically, it catches the `SQLSTATE '40P01'` error, which signifies a deadlock. If caught, the retry count is incremented, and if the maximum retries haven't been exceeded, the loop continues after a short `pg_sleep`. If the operation succeeds, `v_success` is set to `TRUE`, and the loop `EXIT`s. Any other exception will cause the function to return `FALSE`. The time complexity is difficult to define precisely due to the unpredictable nature of deadlocks and retries, but in the best case (no retries), it's O(1) for the operation itself. In the worst case, it's O(N * C), where N is the number of retries and C is the cost of the operation. Space complexity is O(1) as it only uses a few variables.

FUNCTION perform_critical_operation_with_retry(max_retries, operation_details): retry_count = 0 LOOP: BEGIN TRANSACTION: TRY: execute_operation(operation_details) RETURN TRUE // Success CATCH SQLSTATE '40P01' (Deadlock):...

This PL/pgSQL function addresses the critical challenge of concurrent data modification by implementing conflict resolution strategies. It allows multiple processes to attempt updates on a shared resource, identified by...

The `update_shared_resource_with_conflict_resolution` function uses `FOR UPDATE` to acquire an exclusive lock on the target row, preventing other transactions from modifying it until the current transaction commits or rolls back. It then checks the `p_resolution_strategy`. For 'LWW', it unconditionally updates the row, incrementing the `version` and setting `last_updated_by` and `last_updated_at`. For 'FWW', it only updates if the `v_current_version` is 0 (or the initial state), effectively allowing only the first write. The 'MERGE' strategy in this example is a simplified text concatenation; a real-world merge would likely involve comparing versions or timestamps provided by the client and applying more complex logic (e.g., summing numbers, merging lists). The time complexity is dominated by the `SELECT ... FOR UPDATE` and the subsequent `UPDATE`, which is typically O(1) for a single row operation assuming efficient indexing on `resource_id`. Space complexity is O(1) for the function's variables.

CREATE TABLE shared_resource (resource_id, name, value, version, last_updated_by, last_updated_at) FUNCTION update_shared_resource_with_conflict_resolution(resource_id, new_value, updater_name, strategy): SELECT version,...

This PL/pgSQL function demonstrates efficient processing of large datasets by fetching and processing records in manageable batches. It utilizes a cursor to iterate through rows from a source table and accumulates them u...

The function `process_records_in_batches` opens a cursor to iterate over `source_table`. It fetches records one by one within a `LOOP`. A counter `v_batch_count` tracks the number of records in the current batch. When `v_batch_count` reaches `p_batch_size`, a message is logged, and the counter is reset. The loop terminates when `FETCH` returns `NOT FOUND`. After the loop, if `v_batch_count` is greater than zero, it signifies a final partial batch that is also processed. This ensures all records are accounted for. The time complexity is O(N), where N is the total number of records, as each record is fetched and processed once. The space complexity is O(1) as it only uses a few variables to manage the batching and counters.

FUNCTION process_records_in_batches(batch_size): DECLARE cursor FOR SELECT ... FROM source_table ORDER BY ... batch_count = 0 total_processed = 0 OPEN cursor LOOP: FETCH cursor INTO record_vars IF NOT FOUND: EXIT LOOP PR...

This PL/pgSQL function provides a flexible data validation framework. It accepts multiple input parameters (e.g., username, email, age) and checks them against a series of predefined rules, such as length constraints, fo...

The `validate_user_profile` function takes several parameters and uses a `v_errors` array to collect any validation failures. Each validation rule is implemented as an `IF` statement. For example, username length is checked, email format is validated using a regular expression (`~`), and age is checked against a numerical range. If a rule fails, an appropriate error message is appended to `v_is_valid` is set to `FALSE`. After all checks, if `v_is_valid` is still `TRUE`, it returns `TRUE` with `NULL` error message. Otherwise, it returns `FALSE` and a semicolon-separated string of all collected error messages using `array_to_string`. The time complexity is O(k * R), where k is the number of validation rules and R is the average cost of applying a rule (e.g., regex matching). Space complexity is O(E), where E is the number of validation errors collected, for the `v_errors` array.

FUNCTION validate_user_profile(username, email, age, phone_number): errors = empty list is_valid = TRUE IF username is invalid (e.g., too short): add 'Username error' to errors is_valid = FALSE IF email is invalid (e.g.,...

This PL/pgSQL function automates schema modifications by dynamically generating and executing `ALTER TABLE` statements. It targets tables matching a specified pattern, adds a new column with a given type, and optionally...

The function `add_column_to_tables_by_pattern` uses a cursor (`cur_tables`) to iterate through table names in the current schema that match the provided `p_pattern`. For each table, it queries `information_schema.columns` to check if `p_column_name` already exists. If the column does not exist, it constructs an `ALTER TABLE` statement using `format()` for safe identifier quoting (`%I`). The `p_column_type` is directly appended, and if `p_default_value` is provided, it's appended using `format()` as well. The generated SQL is then executed using `EXECUTE`. The time complexity is roughly O(T * (C + E)), where T is the number of tables matching the pattern, C is the cost of checking column existence (querying `information_schema`), and E is the cost of executing the `ALTER TABLE` statement. Space complexity is O(1) for variables, plus the space for the dynamic SQL string.

FUNCTION add_column_to_tables_by_pattern(pattern, column_name, column_type, default_value): DECLARE cursor FOR SELECT tablename FROM pg_tables WHERE schemaname = current_schema() AND tablename LIKE pattern OPEN cursor LO...

This PL/pgSQL function efficiently retrieves all descendants of a given employee in an organizational hierarchy. It leverages a recursive Common Table Expression (CTE) to traverse the `employees` table, starting from a s...

The `get_all_descendants` function uses a recursive CTE named `employee_hierarchy`. The anchor member selects the employee specified by `p_employee_id`. The recursive member then joins `employees` with the `employee_hierarchy` CTE on `e.manager_id = eh.employee_id`, effectively finding all direct reports of employees already in the hierarchy. This process repeats until no new direct reports are found. The final `SELECT` statement retrieves all employees from the `employee_hierarchy` CTE, filtering out the starting employee (`p_employee_id`) itself, and orders them by `employee_id`. The time complexity of a recursive CTE can vary significantly depending on the depth and breadth of the hierarchy and the indexing on `manager_id`. In the worst case, it can be exponential, but with proper indexing, it's often closer to O(N), where N is the number of nodes in the subtree being traversed. Space complexity is O(D), where D is the maximum recursion depth, to store intermediate results.

CREATE TABLE employees (employee_id, name, manager_id) FUNCTION get_all_descendants(start_employee_id): WITH RECURSIVE employee_hierarchy AS ( -- Anchor member SELECT employee_id, name, manager_id FROM employees WHERE em...

This PL/pgSQL solution establishes an audit trail for changes made to a `target_table`. It employs a trigger function, `log_target_table_changes`, which automatically executes after any INSERT, UPDATE, or DELETE operatio...

The `log_target_table_changes` trigger function is invoked for each row affected by an INSERT, UPDATE, or DELETE statement on `target_table`. It uses special trigger variables like `TG_OP` (operation type), `TG_TABLE_NAME`, `OLD` (old row data), and `NEW` (new row data). Based on `TG_OP`, it constructs a record in the `audit_log` table. For INSERTs, only `new_values` are logged. For UPDATEs, both `old_values` and `new_values` are logged. For DELETEs, only `old_values` are logged. The `row_to_json` function converts row data to JSON, which is then cast to `JSONB` for efficient storage. The time complexity of the trigger function itself is O(1) per row operation, but the overall impact depends on the number of rows modified and the cost of inserting into `audit_log`. The space complexity is O(1) for the trigger function's variables, but the `audit_log` table can grow significantly.

CREATE TABLE target_table (...) CREATE TABLE audit_log (...) FUNCTION log_target_table_changes(): user = current_user IF operation is INSERT: INSERT INTO audit_log (table_name, operation, user_name, new_values) VALUES (T...