Releases

Release notes for Resource Surveillance (surveilr) 0.32.0

What’s New

---

This release introduces a rich set of text manipulation and regular expression functions capabilities into surveilr. Usage examples can be found in the assurance test cases.

Text Functions

1. text_substring

Extracts a substring from the input string, starting from the specified index, and optionally for a given length.

Syntax:

text_substring(string, start [, length])

Example:

SELECT text_substring('hello world', 7);
-- Result: 'world'

SELECT text_substring('hello world', 7, 5);
-- Result: 'world'

2. text_slice

Extracts a substring from the input string starting at a given index and ending at an optional index.

Syntax:

text_slice(string, start [, end])

Example:

SELECT text_slice('hello world', 7);
-- Result: 'world'

SELECT text_slice('hello world', -5, -2);
-- Result: 'wor'

3. text_left

Returns the first n characters from the input string.

Syntax:

text_left(string, length)

Example:

SELECT text_left('hello world', 5);
-- Result: 'hello'

4. text_right

Returns the last n characters from the input string.

Syntax:

text_right(string, length)

Example:

SELECT text_right('hello world', 5);
-- Result: 'world'

5. text_index

Returns the position of the first occurrence of the substring in the input string.

Syntax:

text_index(string, substring)

Example:

SELECT text_index('hello yellow', 'ello');
-- Result: 2

6. text_last_index

Returns the position of the last occurrence of the substring in the input string.

Syntax:

text_last_index(string, substring)

Example:

SELECT text_last_index('hello yellow', 'ello');
-- Result: 8

7. text_contains

Checks if the input string contains the specified substring.

Syntax:

text_contains(string, substring)

Example:

SELECT text_contains('hello yellow', 'ello');
-- Result: 1

8. text_has_prefix

Checks if the input string starts with the specified substring.

Syntax:

text_has_prefix(string, prefix)

Example:

SELECT text_has_prefix('hello yellow', 'hello');
-- Result: 1

9. text_has_suffix

Checks if the input string ends with the specified substring.

Syntax:

text_has_suffix(string, suffix)

Example:

SELECT text_has_suffix('hello yellow', 'yellow');
-- Result: 1

10. text_count

Counts the occurrences of the substring within the input string.

Syntax:

text_count(string, substring)

Example:

SELECT text_count('hello yellow', 'ello');
-- Result: 2

11. text_like

Performs case-insensitive string comparison.

Syntax:

text_like(pattern, string)

Example:

SELECT text_like('cóm_ está_', 'CÓMO ESTÁS');
-- Result: 1

12. text_split

Splits the input string by the specified separator and returns the nth part.

Syntax:

text_split(string, separator, n)

Example:

SELECT text_split('one|two|three', '|', 2);
-- Result: 'two'

13. text_concat

Concatenates multiple strings and returns the result.

Syntax:

text_concat(string1, string2, ...)

Example:

SELECT text_concat('one', 'two', 'three');
-- Result: 'onetwothree'

14. text_join

Joins strings using the specified separator.

Syntax:

text_join(separator, string1, string2, ...)

Example:

SELECT text_join('|', 'one', 'two');
-- Result: 'one|two'

15. text_repeat

Repeats the input string the specified number of times.

Syntax:

text_repeat(string, count)

Example:

SELECT text_repeat('one', 3);
-- Result: 'oneoneone'

16. text_ltrim

Trims characters from the beginning of the input string.

Syntax:

text_ltrim(string [, characters])

Example:

SELECT text_ltrim('  hello');
-- Result: 'hello'

17. text_rtrim

Trims characters from the end of the input string.

Syntax:

text_rtrim(string [, characters])

Example:

SELECT text_rtrim('hello  ');
-- Result: 'hello'

18. text_trim

Trims characters from both ends of the input string.

Syntax:

text_trim(string [, characters])

Example:

SELECT text_trim('  hello  ');
-- Result: 'hello'

19. text_lpad

Pads the input string to the specified length by prepending characters.

Syntax:

text_lpad(string, length [, fill])

Example:

SELECT text_lpad('hello', 7, '*');
-- Result: '**hello'

20. text_rpad

Pads the input string to the specified length by appending characters.

Syntax:

text_rpad(string, length [, fill])

Example:

SELECT text_rpad('hello', 7, '*');
-- Result: 'hello**'

21. text_upper

Converts the input string to uppercase.

Syntax:

text_upper(string)

Example:

SELECT text_upper('cómo estás');
-- Result: 'CÓMO ESTÁS'

22. text_lower

Converts the input string to lowercase.

Syntax:

text_lower(string)

Example:

SELECT text_lower('CÓMO ESTÁS');
-- Result: 'cómo estás'

23. text_title

Converts the input string to title case.

Syntax:

text_title(string)

Example:

SELECT text_title('cómo estás');
-- Result: 'Cómo Estás'

24. text_replace

Replaces occurrences of a substring with another string.

Syntax:

text_replace(string, old_substring, new_substring [, count])

Example:

SELECT text_replace('hello', 'l', '*');
-- Result: 'he**o'

25. text_reverse

Reverses the input string.

Syntax:

text_reverse(string)

Example:

SELECT text_reverse('hello');
-- Result: 'olleh'

26. text_length

Returns the number of characters in the input string.

Syntax:

text_length(string)

Example:

SELECT text_length('hello');
-- Result: 5

27. text_size

Returns the number of bytes in the input string.

Syntax:

text_size(string)

Example:

SELECT text_size('hello');
-- Result: 5

28. text_bitsize

Returns the number of bits in the input string.

Syntax:

text_bitsize(string)

Example:

SELECT text_bitsize('one');
-- Result: 24

---

Regular Expression Functions

1. regexp_like

Checks if the source string matches the pattern.

Syntax:

regexp_like(source, pattern)

Example:

SELECT regexp_like('the year is 2021', '[0-9]+');
-- Result: 1

2. regexp_substr

Returns a substring of the source string that matches the pattern.

Syntax:

regexp_substr(source, pattern)

Example:

SELECT regexp_substr('the year is 2021', '[0-9]+');
-- Result: '2021'

3. regexp_capture

Returns the nth matching group within a substring.

Syntax:

regexp_capture(source, pattern [, n])

Example:

SELECT regexp_capture('years is 2021', '\\d\\d(\\d\\d)', 1);
-- Result: '21'

4. regexp_replace

Replaces all matching substrings with the replacement string.

Syntax:

regexp_replace(source, pattern, replacement)

Example:

SELECT regexp_replace('the year is 2021', '[0-9]+', '2050');
-- Result: 'the year is 2050'

Bug Fixes

---

1. Fixed Uniform Resource Table Query Bug

• Issue: There was no clear way to query and retrieve the latest file content from the uniform_resource table, especially after reverting a file to a previous state. Additionally, the updated_at field remained empty for all entries, making it difficult to track file version updates.
• Fix: The bug was fixed by adding the updated_at and last_modified_at field in the ON CONFLICT query part, ensuring proper tracking of file version updates.

---

Assets

Please find the release here.