Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qYAUH-000x4P-5w for pgsql-hackers@arkaria.postgresql.org; Mon, 21 Aug 2023 19:22:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qYAUE-00AAbF-JU for pgsql-hackers@arkaria.postgresql.org; Mon, 21 Aug 2023 19:22:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qYAUD-00AAb1-TZ for pgsql-hackers@lists.postgresql.org; Mon, 21 Aug 2023 19:22:49 +0000 Received: from mail-qt1-x82f.google.com ([2607:f8b0:4864:20::82f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qYAU9-000CnG-C9 for pgsql-hackers@postgresql.org; Mon, 21 Aug 2023 19:22:48 +0000 Received: by mail-qt1-x82f.google.com with SMTP id d75a77b69052e-40fd6da0325so24161721cf.3 for ; Mon, 21 Aug 2023 12:22:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692645764; x=1693250564; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:from:to:cc:subject:date:message-id:reply-to; bh=M5xmpFcw8j1AsN4YlXEVjA3Ga0LtMxf2wO48CsZa8SE=; b=UfVd0vffVITffyr53KsHO0FYB3+ESJNRmwKup73EvGLMURLKvubRTAex0X4rtVLptd rr06r4U3KK3HEwWORlGKzuY9H/KVVZMDoDTH6d5jc4wI/Gyi+xEckIKNBdErm2ustPcy hGqpxlMUFnRVtEI5UjaajfJmzjar/32J6+kp4JZyg2KbgYeA+8miwsFbDJY1SVVOSAXo wZCV2jg5g9ZsDv9hTWFh1LaA5xyNGSLiaSgaKN1aGPQyVosOsHfaNzAxKwUAXpXA5Bxw AkOllw7YflHOtB/2MzjH0YPRft7Q40/H2Yv8QrSyHrTzJCUQ4b9X4dhXPHfQcIEj7/ZC 2c4w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692645764; x=1693250564; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=M5xmpFcw8j1AsN4YlXEVjA3Ga0LtMxf2wO48CsZa8SE=; b=lGUEqyNsu97/GfREr3RBX1BjUpS95iS4AEiG852FahrzjExzPHnYFTCKEjYVzY2+Ze Wcp65wUvPpVBjVhs8PMN/WQs2S66irEXpSQsh/bRnfSYgCjRxvqXHpzI/TNfkcDXhTrO w8wdmHj58wKLj+iMwIUqwZdAltr3izkR54ghKde5ikTphtIFNqhltSq6PQU9oHi59ycI QGZJ5e/ddElgpe2csMrg5+cMAVvm2Sz8w2TNFGgqiYG0ok3fVENWqNDEbUjJqf8v2/i5 u54oxuB/jiU7tWnkdI0R55pFEyr/+sG2deaMncrjtC39eL/uUsF69BgRZ31ztxVCA3SP H0hw== X-Gm-Message-State: AOJu0Yywa6UH6te4gds/h/cJLs4HDHEqC9utpT+Vb1oeurJRDD6LgiTJ zfiqucla9zr2wCID11os4jkCfXrq3lk= X-Google-Smtp-Source: AGHT+IFgjhXXAUMTm1qeGUZfYVxvOdZZhA6zLUA404jx7SaQ+JvQDrs5I1nvnrUz8zwfiDLAak4vLg== X-Received: by 2002:a05:6870:4289:b0:1be:dfdf:cb1 with SMTP id y9-20020a056870428900b001bedfdf0cb1mr10469097oah.46.1692645346210; Mon, 21 Aug 2023 12:15:46 -0700 (PDT) Received: from nathanxps13 ([2600:1700:48f8:340f:8b81:11fa:89eb:a36a]) by smtp.gmail.com with ESMTPSA id eg22-20020a056870989600b001a68feb9440sm4596162oab.9.2023.08.21.12.15.44 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 21 Aug 2023 12:15:45 -0700 (PDT) Date: Mon, 21 Aug 2023 12:15:28 -0700 From: Nathan Bossart To: Dean Rasheed Cc: John Naylor , Kirk Wolak , Peter Eisentraut , Eric Radman , pgsql-hackers@postgresql.org Subject: Re: [PATCH] Add function to_oct Message-ID: <20230821191528.GA443047@nathanxps13> References: <20230815041129.GB2294364@nathanxps13> <20230815171715.GA2312399@nathanxps13> <20230816142410.GA2651383@nathanxps13> <20230817152628.GA3148583@nathanxps13> <20230820152551.GB290672@nathanxps13> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="SUOF0GtieIMvvwua" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --SUOF0GtieIMvvwua Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Mon, Aug 21, 2023 at 09:31:37AM +0100, Dean Rasheed wrote: > Hmm, I think just including the doc text update, without the examples > of positive and negative inputs, might not be sufficient to make the > meaning clear to everyone. I added some examples for negative inputs. > Something else that bothers me slightly is the function naming -- > "hexadecimal" gets abbreviated to "hex", "octal" gets abbreviated to > "oct", but "binary" is left as-is. I think it ought to be "to_bin()" > on consistency grounds, even though I understand the words "to bin" > could be interpreted differently. (Looking elsewhere for precedents, > Python has bin(), oct() and hex() functions.) I renamed it to to_bin(). > Also, I think the convention is to always list functions > alphabetically, so to_oct() should really come after to_hex(). I reordered the functions in the docs. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com --SUOF0GtieIMvvwua Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v9-0001-add-to_bin-and-to_oct.patch" From 131ee7fcfc62040572425cf1a090c0a6967d7559 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Mon, 21 Aug 2023 11:29:51 -0700 Subject: [PATCH v9 1/1] add to_bin() and to_oct() --- doc/src/sgml/func.sgml | 59 +++++++++++++++++- src/backend/utils/adt/varlena.c | 86 +++++++++++++++++++-------- src/include/catalog/pg_proc.dat | 12 ++++ src/test/regress/expected/strings.out | 62 ++++++++++++++++++- src/test/regress/sql/strings.sql | 15 ++++- 5 files changed, 204 insertions(+), 30 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index be2f54c914..7a0d4b9134 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -3737,6 +3737,32 @@ repeat('Pg', 4) PgPgPgPg + + + + to_bin + + to_bin ( integer ) + text + + + to_bin ( bigint ) + text + + + Converts the number to its equivalent two's complement binary + representation. + + + to_bin(2147483647) + 1111111111111111111111111111111 + + + to_bin(-1234) + 11111111111111111111101100101110 + + + @@ -3750,11 +3776,42 @@ repeat('Pg', 4) PgPgPgPg text - Converts the number to its equivalent hexadecimal representation. + Converts the number to its equivalent two's complement hexadecimal + representation. to_hex(2147483647) 7fffffff + + + to_hex(-1234) + fffffb2e + + + + + + + to_oct + + to_oct ( integer ) + text + + + to_oct ( bigint ) + text + + + Converts the number to its equivalent two's complement octal + representation. + + + to_oct(2147483647) + 17777777777 + + + to_oct(-1234) + 37777775456 diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c index b1ec5c32ce..72e1e24fe0 100644 --- a/src/backend/utils/adt/varlena.c +++ b/src/backend/utils/adt/varlena.c @@ -4919,53 +4919,87 @@ array_to_text_internal(FunctionCallInfo fcinfo, ArrayType *v, return result; } -#define HEXBASE 16 /* - * Convert an int32 to a string containing a base 16 (hex) representation of - * the number. + * Workhorse for to_bin, to_oct, and to_hex. Note that base must be > 1 and <= + * 16. */ -Datum -to_hex32(PG_FUNCTION_ARGS) +static inline text * +convert_to_base(uint64 value, int base) { - uint32 value = (uint32) PG_GETARG_INT32(0); - char *ptr; const char *digits = "0123456789abcdef"; - char buf[32]; /* bigger than needed, but reasonable */ - ptr = buf + sizeof(buf) - 1; - *ptr = '\0'; + /* We size the buffer for to_bin's longest possible return value. */ + char buf[sizeof(uint64) * BITS_PER_BYTE]; + char *const end = buf + sizeof(buf); + char *ptr = end; + + Assert(base > 1); + Assert(base <= 16); do { - *--ptr = digits[value % HEXBASE]; - value /= HEXBASE; + *--ptr = digits[value % base]; + value /= base; } while (ptr > buf && value); - PG_RETURN_TEXT_P(cstring_to_text(ptr)); + return cstring_to_text_with_len(ptr, end - ptr); +} + +/* + * Convert an integer to a string containing a base-2 (binary) representation + * of the number. + */ +Datum +to_bin32(PG_FUNCTION_ARGS) +{ + uint64 value = (uint32) PG_GETARG_INT32(0); + + PG_RETURN_TEXT_P(convert_to_base(value, 2)); +} +Datum +to_bin64(PG_FUNCTION_ARGS) +{ + uint64 value = (uint64) PG_GETARG_INT64(0); + + PG_RETURN_TEXT_P(convert_to_base(value, 2)); } /* - * Convert an int64 to a string containing a base 16 (hex) representation of + * Convert an integer to a string containing a base-8 (oct) representation of * the number. */ Datum -to_hex64(PG_FUNCTION_ARGS) +to_oct32(PG_FUNCTION_ARGS) +{ + uint64 value = (uint32) PG_GETARG_INT32(0); + + PG_RETURN_TEXT_P(convert_to_base(value, 8)); +} +Datum +to_oct64(PG_FUNCTION_ARGS) { uint64 value = (uint64) PG_GETARG_INT64(0); - char *ptr; - const char *digits = "0123456789abcdef"; - char buf[32]; /* bigger than needed, but reasonable */ - ptr = buf + sizeof(buf) - 1; - *ptr = '\0'; + PG_RETURN_TEXT_P(convert_to_base(value, 8)); +} - do - { - *--ptr = digits[value % HEXBASE]; - value /= HEXBASE; - } while (ptr > buf && value); +/* + * Convert an integer to a string containing a base-16 (hex) representation of + * the number. + */ +Datum +to_hex32(PG_FUNCTION_ARGS) +{ + uint64 value = (uint32) PG_GETARG_INT32(0); + + PG_RETURN_TEXT_P(convert_to_base(value, 16)); +} +Datum +to_hex64(PG_FUNCTION_ARGS) +{ + uint64 value = (uint64) PG_GETARG_INT64(0); - PG_RETURN_TEXT_P(cstring_to_text(ptr)); + PG_RETURN_TEXT_P(convert_to_base(value, 16)); } /* diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 12fac15ceb..e893b49eb8 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -3707,6 +3707,18 @@ { oid => '2768', descr => 'split string by pattern', proname => 'regexp_split_to_array', prorettype => '_text', proargtypes => 'text text text', prosrc => 'regexp_split_to_array' }, +{ oid => '9030', descr => 'convert int4 number to binary', + proname => 'to_bin', prorettype => 'text', proargtypes => 'int4', + prosrc => 'to_bin32' }, +{ oid => '9031', descr => 'convert int8 number to binary', + proname => 'to_bin', prorettype => 'text', proargtypes => 'int8', + prosrc => 'to_bin64' }, +{ oid => '9032', descr => 'convert int4 number to oct', + proname => 'to_oct', prorettype => 'text', proargtypes => 'int4', + prosrc => 'to_oct32' }, +{ oid => '9033', descr => 'convert int8 number to oct', + proname => 'to_oct', prorettype => 'text', proargtypes => 'int8', + prosrc => 'to_oct64' }, { oid => '2089', descr => 'convert int4 number to hex', proname => 'to_hex', prorettype => 'text', proargtypes => 'int4', prosrc => 'to_hex32' }, diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out index 62698569e1..b7500d9c0e 100644 --- a/src/test/regress/expected/strings.out +++ b/src/test/regress/expected/strings.out @@ -2129,8 +2129,68 @@ select split_part('@joeuser@mydatabase@','@',-2) AS "mydatabase"; (1 row) -- --- test to_hex +-- test to_bin, to_oct, and to_hex -- +select to_bin(-1234) AS "11111111111111111111101100101110"; + 11111111111111111111101100101110 +---------------------------------- + 11111111111111111111101100101110 +(1 row) + +select to_bin(-1234::bigint); + to_bin +------------------------------------------------------------------ + 1111111111111111111111111111111111111111111111111111101100101110 +(1 row) + +select to_bin(256*256*256 - 1) AS "111111111111111111111111"; + 111111111111111111111111 +-------------------------- + 111111111111111111111111 +(1 row) + +select to_bin(256::bigint*256::bigint*256::bigint*256::bigint - 1) AS "11111111111111111111111111111111"; + 11111111111111111111111111111111 +---------------------------------- + 11111111111111111111111111111111 +(1 row) + +select to_oct(-1234) AS "37777775456"; + 37777775456 +------------- + 37777775456 +(1 row) + +select to_oct(-1234::bigint) AS "1777777777777777775456"; + 1777777777777777775456 +------------------------ + 1777777777777777775456 +(1 row) + +select to_oct(256*256*256 - 1) AS "77777777"; + 77777777 +---------- + 77777777 +(1 row) + +select to_oct(256::bigint*256::bigint*256::bigint*256::bigint - 1) AS "37777777777"; + 37777777777 +------------- + 37777777777 +(1 row) + +select to_hex(-1234) AS "fffffb2e"; + fffffb2e +---------- + fffffb2e +(1 row) + +select to_hex(-1234::bigint) AS "fffffffffffffb2e"; + fffffffffffffb2e +------------------ + fffffffffffffb2e +(1 row) + select to_hex(256*256*256 - 1) AS "ffffff"; ffffff -------- diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql index ca32f6bba5..3959678992 100644 --- a/src/test/regress/sql/strings.sql +++ b/src/test/regress/sql/strings.sql @@ -685,10 +685,21 @@ select split_part('joeuser@mydatabase','@',-3) AS "empty string"; select split_part('@joeuser@mydatabase@','@',-2) AS "mydatabase"; -- --- test to_hex +-- test to_bin, to_oct, and to_hex -- -select to_hex(256*256*256 - 1) AS "ffffff"; +select to_bin(-1234) AS "11111111111111111111101100101110"; +select to_bin(-1234::bigint); +select to_bin(256*256*256 - 1) AS "111111111111111111111111"; +select to_bin(256::bigint*256::bigint*256::bigint*256::bigint - 1) AS "11111111111111111111111111111111"; + +select to_oct(-1234) AS "37777775456"; +select to_oct(-1234::bigint) AS "1777777777777777775456"; +select to_oct(256*256*256 - 1) AS "77777777"; +select to_oct(256::bigint*256::bigint*256::bigint*256::bigint - 1) AS "37777777777"; +select to_hex(-1234) AS "fffffb2e"; +select to_hex(-1234::bigint) AS "fffffffffffffb2e"; +select to_hex(256*256*256 - 1) AS "ffffff"; select to_hex(256::bigint*256::bigint*256::bigint*256::bigint - 1) AS "ffffffff"; -- -- 2.25.1 --SUOF0GtieIMvvwua--