Postgres中与MySQL的HEX()和UNHEX()等效的函数是什么？

Question

我正在将一些使用MySQL的工具转换为PostgreSQL。在此过程中，我遇到了许多问题，但是大多数都已找到解决方法。唯一出现问题的是HEX()和UNHEX()函数。我尝试了encode(%s, 'hex')和decode(%s, 'hex')，虽然不再出现错误，但似乎并没有达到预期效果。请问是否有人知道在PostgreSQL中相应的函数是什么？

这里是旧的MySQL查询：

SELECT HEX(test_table.hash),
       title,
       user,
       reason,
       description,
       url,
       performed,
       comment,
       authenticated,
       status
FROM alerts
JOIN user_responses ON test_table.hash = user_responses.hash
JOIN test_status ON test_table.hash = test_status.hash
WHERE status = %s

这是我用PostgreSQL格式更新后的查询：

SELECT encode(test_table.hash, 'hex') as hash,
       title,
       user,
       reason,
       description,
       url,
       performed,
       comment,
       authenticated,
       status
FROM test_table
JOIN user_responses ON test_table.hash = user_responses.hash
JOIN test_status ON test_table.hash = test_status.hash
WHERE status = %s

谢谢！

- Neemaximo

Answer 1

create function hex(text) returns text language sql immutable strict as $$
  select encode($1::bytea, 'hex')
$$;

create function hex(bigint) returns text language sql immutable strict as $$
  select to_hex($1)
$$;

create function unhex(text) returns text language sql immutable strict as $$
  select encode(decode($1, 'hex'), 'escape')
$$;


select hex('abc'), hex(123), unhex(hex('PostgreSQL'));

结果：

╔════════╤═════╤════════════╗
║  十六进制   │ 十六进制 │   解码后    ║
╠════════╪═════╪════════════╣
║ 616263 │ 7b  │ PostgreSQL ║
╚════════╧═════╧════════════╝

这是PostgreSQL：一切皆有可能 :)

- Abelisto

Answer 2

我建议查看mysqlcompat库，它包含了许多MySQL函数移植到Postgres中。

您可以查看他们实现的HEX或UNHEX函数。特别注意，MySQL的hex函数有两种不同的工作模式：

如果参数是字符串，则将参数中的每个字符转换为两个十六进制数字。

如果参数是十进制数，则该函数返回参数的十六进制字符串表示，并被视为longlong（BIGINT）数字。

所以，如果你自己编写，请确保支持所有可能的用例，就像mysqlcompat一样。

- Josh Kupershmidt