On Wed, 25 Jan 2023 at 21:43, Peter Eisentraut
<peter.eisentraut@enterprisedb.com> wrote:
>
> On 24.01.23 14:10, Dean Rasheed wrote:
> > I also think it might be useful for it to gain a couple of boolean options:
> >
> > 1). An option to output a signed value (defaulting to false, to
> > preserve the current two's complement output).
>
> I find the existing behavior so strange, I would rather give up and
> invent a whole new function family with correct behavior, which could
> then also support octal and binary, and perhaps any base. This could be
> something generally named, like "convert" or "format".
>
> > 2). An option to output the base prefix "0x" (which comes after the
> > sign, making it inconvenient for the user to add themselves).
>
> This could also be handled with a "format"-like function.
>
Yeah, making a break from the existing to_hex() functions might not be
a bad idea.
My only concern with something general like convert() or format() is
that it'll end up being hard to use, with lots of different formatting
options, like to_char(). In fact Oracle's to_char() has an 'X'
formatting option to output hexadecimal, though it's pretty limited
(e.g., it doesn't support negative inputs). MySQL has conv() that'll
convert between any two bases, but it does bizarre things for negative
inputs or inputs larger than 2^64.
TBH, I'm not that interested in bases other than hex (I mean who
really uses octal anymore?), and I don't particularly care about
formats other than the format we accept as input. For that, just
adding a numeric_to_hex() function would suffice. That works fine for
int4 and int8 inputs too, and doesn't preclude adding a more general
base-conversion / formatting function later, if there's demand.
Regards,
Dean