Amazon中的十六进制字符串到整数转换

Question

基于ParAccel，而后者基于Postgres。从我的研究来看，在Postgres中执行十六进制字符串到整数转换的首选方法似乎是通过一个位字段，如这个回答所概述的。

就bigint而言，这将是：

select ('x'||lpad('123456789abcdef',16,'0'))::bit(64)::bigint

不幸的是，这在Redshift上失败了，因为：

ERROR: cannot cast type text to bit [SQL State=42846] 

在Postgres8.1ish(接近Redshift兼容级别)中，还有哪些其他方式来执行这种转换？在Redshift中不支持UDF，数组、regex函数或集生成函数也不支持。

Answer 1

看起来他们在某个时候为此添加了一个函数：STRTOL

语法 Num_string(基地) 返回类型 BIGINT如果num_string为null，则返回NULL。

，例如

SELECT strtol('deadbeef', 16);

返回：3735928559

Answer 2

假设您想要一个简单的数字对数字序号位置转换(即您不担心两个赞美的否定，等等)，我认为这应该适用于一个8.1等效的DB：

CREATE OR REPLACE FUNCTION hex2dec(text) RETURNS bigint AS $$
SELECT sum(CASE WHEN v >= ascii('a') THEN v - ascii('a') + 10 ELSE v - ascii('0') END * 16^ordpos)::bigint
FROM (
    SELECT n-1, ascii(substring(reverse($1), n, 1))
    FROM generate_series(1, length($1)) n
) AS x(ordpos, v);
$$ LANGUAGE sql IMMUTABLE;

函数形式是可选的，它只会使它更容易避免重复多次参数。无论如何，它都应该是内线的。效率可能会很糟糕，但大多数可用的更聪明的工具似乎无法在那么老的版本上使用，这至少是可行的：

regress=> CREATE TABLE t AS VALUES ('c13b'), ('a'), ('f');
regress=> SELECT hex2dec(column1) FROM t;
 hex2dec 
---------
   49467
      10
      15
(3 rows)

如果您可以使用regexp_split_to_array和generate_subscripts，它可能会更快。或者慢一点。我还没试过。另一个可能的技巧是使用数字映射数组而不是CASE，如下所示：

'[48:102]={0,1,2,3,4,5,6,7,8,9,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,10,11,12,13,14,15}'::integer[]

您可以将其用于：

CREATE OR REPLACE FUNCTION hex2dec(text) RETURNS bigint AS $$
SELECT sum(
  ('[48:102]={0,1,2,3,4,5,6,7,8,9,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,10,11,12,13,14,15}'::integer[])[ v ]
  * 16^ordpos
)::bigint
FROM (
    SELECT n-1, ascii(substring(reverse($1), n, 1))
    FROM generate_series(1, length($1)) n
) AS x(ordpos, v);
$$ LANGUAGE sql IMMUTABLE;

就我个人而言，我会用客户端来代替，而不是争论旧的PostgreSQL叉子的有限功能，尤其是那些您无法在其上加载自己的用户定义的C函数，或者使用PL/Perl等。

在实际的PostgreSQL中，我只需要使用这个：

ex2dec.c

#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"
#include "errno.h"
#include "limits.h"
#include <stdlib.h>

PG_MODULE_MAGIC;

Datum from_hex(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(hex2dec);

Datum
hex2dec(PG_FUNCTION_ARGS)
{
    char *endpos;
    const char *hexstr = text_to_cstring(PG_GETARG_TEXT_PP(0));
    long decval = strtol(hexstr, &endpos, 16);
    if (endpos[0] != '\0')
    {
        ereport(ERROR, (ERRCODE_INVALID_PARAMETER_VALUE, errmsg("Could not decode input string %s as hex", hexstr)));
    }
    if (decval == LONG_MAX && errno == ERANGE)
    {
        ereport(ERROR, (ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE, errmsg("Input hex string %s overflows int64", hexstr)));
    }
    PG_RETURN_INT64(decval);
}

Makefile

MODULES = hex2dec
DATA = hex2dec--1.0.sql
EXTENSION = hex2dec

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

hex2dec.control

comment = 'Utility function to convert hex strings to decimal'
default_version = '1.0'
module_pathname = '$libdir/hex2dec'
relocatable = true

hex2dec--1.0.sql

CREATE OR REPLACE FUNCTION hex2dec(hexstr text) RETURNS bigint
        AS 'hex2dec','hex2dec'
        LANGUAGE c IMMUTABLE STRICT;

COMMENT ON FUNCTION hex2dec(hexstr text)
IS 'Decode the hex string passed, which may optionally have a leading 0x, as a bigint. Does not attempt to consider negative hex values.';

用法：

CREATE EXTENSION hex2dec;

postgres=# SELECT hex2dec('7fffffffffffffff');
       hex2dec       
---------------------
 9223372036854775807
(1 row)

postgres=# SELECT hex2dec('deadbeef');
  hex2dec   
------------
 3735928559
(1 row)

postgres=# SELECT hex2dec('12345');
 hex2dec 
---------
   74565
(1 row)

postgres=# select hex2dec(to_hex(-1));
  hex2dec   
------------
 4294967295
(1 row)

postgres=# SELECT hex2dec('8fffffffffffffff');
ERROR:  Input hex string 8fffffffffffffff overflows int64

postgres=# SELECT hex2dec('0x7abcz123');
ERROR:  Could not decode input string 0x7abcz123 as hex

表现上的不同是..。值得注意。给定的样本数据：

CREATE TABLE randhex AS 
SELECT '0x'||to_hex( abs(random() * (10^((random()-.5)*10)) * 10000000)::bigint) AS h
FROM generate_series(1,1000000);

从十六进制到十进制的转换使用C扩展从温暖的缓存中获取大约1.3，这对于一百万行来说不是很好。在没有任何转换的情况下阅读它们需要0.95s。基于SQL的hex2dec方法处理相同的行花费了36秒钟。坦率地说，SQL方法如此之快给我留下了深刻的印象，而且让C感到惊讶的是，它的速度如此之慢。

Answer 3

一个可能的解释是从text到bit(n)的转换依赖于无文档的行为，我重复汤姆·莱恩的话

这依赖于位类型输入转换器的一些未记录的行为，但我认为没有理由期望它会中断。一个可能更大的问题是，它需要PG >= 8.3，因为在此之前没有文本可以使用。

亚马逊衍生产品显然不允许这种无文档化的特性。这并不奇怪，因为它是基于Postgres8.1，那里根本没有演员。

在这一密切相关的答复中曾引用如下：

将文本表示中的十六进制转换为十进制数