Re: Non-decimal integer literals

On Fri, Jan 13, 2023, at 07:01, Dean Rasheed wrote:
> Attachments:
> * 0001-Add-non-decimal-integer-support-to-type-numeric.patch

Nice! This also simplifies when dealing with non-negative integers represented as byte arrays,
common in e.g. cryptography code.

Before, one had to implement numeric_from_bytes(bytea) in plpgsql [1],
which can now be greatly simplified:

create function numeric_from_bytes(bytea) returns numeric language sql as $$
select ('0'||right($1::text,-1))::numeric
$$;

\timing
select numeric_from_bytes(('\x'||repeat('0123456789abcdef',1000))::bytea);
Time: 484.223 ms -- HEAD + plpgsql numeric_from_bytes()
Time: 19.790 ms -- 0001 + simplified numeric_from_bytes()

About 25x faster!

Would we want a built-in function for this?
To avoid the text casts, but also to improve user-friendliness,
since the improved solution is still a hack a user needing it has to someone come up with or find.
The topic "Convert hex in text representation to decimal number" is an old one on Stackoverflow [2],
posted 11 years ago, with a myriad of various hackis solutions, out of which one had a bug that I reported.
Many other modern languages seems to have this as a built-in or in stdlibs:
Python3:
classmethod int.from_bytes(bytes, byteorder='big', *, signed=False)
Rust:
pub const fn from_be_bytes(bytes: [u8; 8]) -> u64

/Joel

[1] https://gist.github.com/joelonsql/f54552db1f0fd6d9b3397d255e51f58a
[2] https://stackoverflow.com/questions/8316164/convert-hex-in-text-representation-to-decimal-number