create or replace function naturalsort(text)
returns bytea
language sql
immutable strict
as $f$
select string_agg(convert_to(coalesce(r[2],
length(length(r[1])::text) || length(r[1])::text || r[1]),
'SQL_ASCII'),'\x00')
from regexp_matches($1, '0*([0-9]+)|([^0-9]+)', 'g') r;
$f$;
Note the null byte \x00. Simply printing the function definition to
the schema causes issues as, when a user performs rails db:schema:load, Rails complains: "ArgumentError: string contains null
byte."
In order to prevent Rails from interpreting the string as having a null
byte in the middle of the string, resulting in an ArgumentError, we
escape the null byte. This results in Rails being able to load the
string to and to create the function appropriately.
I further tested this change by directly checking Postgres' definition
of the function with \df+ {functionname}.
Update
Function#to_schema
in order to generate schema output that maintains escapes appropriately.For example, say you have a function that includes an escape sequence, such as the popular
naturalsort
function (source http://www.rhodiumtoad.org.uk/junk/naturalsort.sql):Note the null byte
\x00
. Simply printing the function definition to the schema causes issues as, when a user performsrails db:schema:load
, Rails complains: "ArgumentError: string contains null byte."In order to prevent Rails from interpreting the string as having a null byte in the middle of the string, resulting in an
ArgumentError
, we escape the null byte. This results in Rails being able to load the string to and to create the function appropriately.I further tested this change by directly checking Postgres' definition of the function with
\df+ {functionname}
.