On Wed, 25 Jan 2023 at 21:43, Peter Eisentraut <peter.eisentr...@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
