#PostgreSQL

Postgres Connection::quoteIdentifiers() also should not quote square brackets in REGEXP_REPLACE() function calls.

We have a field that contains values like "R123", "A354", etc., and wanted to sort it in a view numerically, without the letter. The view includes that field, and also includes a sort criteria to sort on that field (even though it starts with a letter).

My solution was to use hook_view_query_alter to add a new field which contains only the numeric part, and then to modify the sort to use that field, instead of original field with the letter prefix. This has worked nicely in production on MySQL for over a year now.

// Get the number out, and cast it to integer.
$sql_to_extract_number = "CAST(REGEXP_REPLACE(field_number_with_letter_prefix_value, '^[^0-9]*([0-9]+)', '\\1') AS INTEGER)";

// Add our new field, containing the number.
$query->addField(NULL, $sql_to_extract_number, 'my_new_field');

// Switch the sort to use this field instead of field_number_with_letter_prefix
$query->orderby[0]['field'] = 'my_new_field';

But we've recently switched to Postgres, and now we get this error from the CAST function: Invalid text representation: 7 ERROR: invalid input syntax for type integer: "R1234".

The '^[^0-9]*([0-9]+)' regex is getting converted to '^"^0-9"*("0-9"+)', which is obviously broken and makes the replacement fail.

In this case, I was able to get around the issue by changing the regex to '^\\D*(\\d+)'. But square brackets are allowed in a regex, so they should not be causing confusing errors in this context.