This SQL function, `find_first_non_repeating_char`, identifies the first character in a given string that appears only once. It iterates through the string twice: first to count the occurrences of each character and then...

The function uses an array `char_counts` to store the frequency of each character, indexed by their ASCII values. The time complexity is O(N), where N is the length of the input string, because we iterate through the string twice. The space complexity is O(1) because the size of the `char_counts` array is fixed (256 for ASCII), regardless of the input string's length. The algorithm handles empty or NULL input strings by returning NULL. It correctly identifies the first non-repeating character by maintaining the order of appearance in the second pass.

FUNCTION find_first_non_repeating_char(input_string): IF input_string is NULL or empty THEN RETURN NULL END IF CREATE an array `char_counts` of size 256, initialized to 0. FOR each character `c` in input_string: INCREMEN...

This PostgreSQL function, `find_all_anagrams`, efficiently finds all starting indices of substrings in a larger string (`s`) that are anagrams of a given pattern string (`p`). It employs a sliding window approach, mainta...

The algorithm uses two frequency maps (arrays of size 26 for lowercase English letters) to store character counts: one for the pattern `p` (`p_chars`) and one for the current sliding window in `s` (`s_chars`). The time complexity is O(N), where N is the length of string `s`, because we iterate through `s` once to slide the window. The space complexity is O(1) as the frequency maps have a fixed size (26), independent of the input string lengths. Edge cases like `p` being longer than `s` are handled. The core logic involves updating `s_chars` by adding the new character entering the window and subtracting the character leaving, then comparing `s_chars` with `p_chars` at each step. The invariant maintained is that `s_chars` always reflects the character counts of the current window of size `p_len` starting at `start_index`.

FUNCTION find_all_anagrams(s, p): IF length(p) > length(s) THEN RETURN empty list END IF CREATE frequency map `p_chars` for pattern p. CREATE frequency map `s_chars` for the first window of s (size length(p)). CREATE an...

This PostgreSQL function provides a strategy for conditionally refreshing materialized views. It considers the time elapsed since the last refresh and a conceptual threshold for the number of changes in underlying tables...

The function `refresh_materialized_view_conditionally` takes the materialized view name, its last refresh timestamp, a change threshold, and a minimum interval. It first checks if the `min_interval_hours` has passed since `last_refresh_ts`. If not, it returns early. The core logic then involves estimating changes. In this simplified drill, `estimated_changes` is a placeholder; a real implementation would query system catalogs (like `pg_stat_user_tables` for `n_tup_ins`, `n_tup_upd`, `n_tup_del`) or use triggers to track modifications in the MV's base tables. If the `last_refresh_ts` is NULL (first refresh) or if `estimated_changes` exceeds `change_threshold_rows`, the materialized view is refreshed using dynamic SQL. Time complexity is dominated by the change estimation logic, which can vary greatly. Space complexity is low, mainly for variables.

FUNCTION refresh_materialized_view_conditionally(mv_name, last_refresh_ts, change_threshold, min_interval): current_ts = current_timestamp time_since_last = current_ts - last_refresh_ts IF time_since_last < min_interval:...

This SQL query uses a recursive Common Table Expression (CTE) to find all possible paths from a specified start node to a target node in a graph. The graph is represented by an `edges` table with `start_node` and `target...

The recursive CTE `graph_paths` starts with the base case: all edges originating from the `start_node`. The recursive step then joins the current paths with edges where the `target_node` of the current path matches the `start_node` of a new edge. It appends the new `target_node` to the path and increments the depth. Cycle detection is achieved by checking if the `e.target_node` is already present in `gp.path`. A depth limit (`gp.depth < 10`) is imposed to prevent infinite recursion in graphs with cycles. Time complexity can be exponential in the worst case for dense graphs, but is bounded by the depth limit and graph structure. Space complexity is related to the number and length of paths found.

WITH RECURSIVE graph_paths AS ( -- Base Case: SELECT start_node, target_node, [start_node] AS path, 0 AS depth FROM edges WHERE start_node = 'START_NODE' UNION ALL -- Recursive Step: SELECT gp.start_node, e.target_node,...

This function checks for the existence of a specific path within a JSONB document. It leverages PostgreSQL's built-in JSONB functions to traverse the document structure. This is useful for validating data structures befo...

The function `jsonb_path_exists` takes a JSONB document and a text representation of a path. It uses `jsonb_extract_path` to attempt to retrieve the value at that path. If `jsonb_extract_path` returns a non-null value, it implies the path exists, and the function returns TRUE. Otherwise, if `jsonb_extract_path` returns NULL (meaning the path or any part of it is missing), the function returns FALSE. The time complexity is dependent on the depth of the JSONB document and the length of the path expression, generally proportional to the number of keys traversed. Space complexity is minimal, primarily for storing the result.

FUNCTION jsonb_path_exists(document, path_expression): extracted_value = extract_jsonb_path(document, path_expression) IF extracted_value IS NOT NULL: RETURN TRUE ELSE: RETURN FALSE END FUNCTION