Skip to content

Add arguments to DB-layer cast_to_numeric(text) #4422

Closed
@mathemancer

Description

@mathemancer

For safety, the old msar.cast_to_numeric(text) function has to do a regex for each line to:

  • determine whether it's a number, and
  • guess the locale to actually cast it to said number.

This is super slow and pretty brittle:

  • regex for each line is slow
  • Locale is determined per-line, meaning we can't (for example) handle '1,000' vs. '1.000'. The resulting number from casting those strings is locale-dependent.
  • We end up casting each line using an independently-determined locale, leading to confusing results.

We should add a function with the signature:

msar.cast_to_numeric(num text, group_sep "char", decimal_p "char) RETURNS numeric

This function should do minimal processing and validation. It should just strip out the grouping separator, and replace the decimal_p when found with the DB-locale decimal point.

Metadata

Metadata

Assignees

Projects

No projects

Milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions