postgresql - Convert value from string representation in base N to numeric -

on database keep numbers in specific notation varchar values. there way typecast values decimals selected notation?

what looking here should this:

select to_integer_with_notation('d', '20')   int4  ---------  13 

one more example:

select to_integer_with_notation('d3', '23')   int4  ---------  302 

unfortunately, there no built-in function in postgresql, can written easy:

create or replace function number_from_base(num text, base integer)   returns numeric   language sql   immutable   strict $function$ select sum(exp * cn) (   select base::numeric ^ (row_number() on () - 1) exp,          case            when ch between '0' , '9' ascii(ch) - ascii('0')            when ch between 'a' , 'z' 10 + ascii(ch) - ascii('a')          end cn   regexp_split_to_table(reverse(lower(num)), '') ch(ch) ) sub $function$; 

note: used numeric return type, int4 not enough in many cases (with longer string input).

edit: here sample reverse function, can convert bigint text representation within custom base:

create or replace function number_to_base(num bigint, base integer)   returns text   language sql   immutable   strict $function$ recursive n(i, n, r) (     select -1, num, 0   union     select + 1, n / base, (n % base)::int     n     n > 0 ) select string_agg(ch, '') (   select case            when r between 0 , 9 r::text            when r between 10 , 35 chr(ascii('a') + r - 10)            else '%'          end ch   n   >= 0   order desc ) ch $function$; 

example usage:

select number_to_base(1248, 36);  -- +----------------+ -- | number_to_base | -- +----------------+ -- | yo             | -- +----------------+