Skip to content

Instantly share code, notes, and snippets.

@meglio
Last active July 4, 2021 04:29
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save meglio/b75d012fd4ae5f2a208a6b6b1f24a4be to your computer and use it in GitHub Desktop.
Save meglio/b75d012fd4ae5f2a208a6b6b1f24a4be to your computer and use it in GitHub Desktop.
Scans all text values from the given table/column names and, given a text prefix, finds the maximum natural number prefixed with that prefix.
drop function if exists maybe_to_natural_number;
create or replace function maybe_to_natural_number(str text) returns integer
language plpgsql immutable leakproof returns null on null input parallel safe as $$
begin
if regexp_match(str, '^\d+$') is null then
return null;
end if;
return str::int8;
end;
$$;
drop function if exists extract_max_number_from_prefixed_value;
create or replace function extract_max_number_from_prefixed_value(
table_name text,
column_name text,
text_value_prefix text,
out max_index_found integer
) language plpgsql as $$
begin
execute format('
select max(
case when starts_with(%I, $1) then maybe_to_natural_number(trim(substring(%I, char_length($2)+1))) else null end
)
from %I',
column_name,
column_name,
table_name
) into max_index_found
using text_value_prefix, text_value_prefix;
end;
$$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment