sql - MySQL's HEX() and UNHEX() equivalent in Postgres?

Question

I'm in the process of converting some tools over that are using MySQL to PostgreSQL. With that, I've run into a number of issues but was able to find most everything. The one I'm having an issue with is HEX() and UNHEX(). I've tried encode(%s, 'hex') and decode(%s, 'hex') which did actually stop causing me to have errors, but it still did not seem to do the trick. Does anyone have an idea to what the equivalent of those functions would be in Postgres?

Here is the old MySQL query:

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

And here is my updated query in PostgreSQL format:

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

Thanks!

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'));

Result:

╔════════╤═════╤════════════╗
║  hex   │ hex │   unhex    ║
╠════════╪═════╪════════════╣
║ 616263 │ 7b  │ PostgreSQL ║
╚════════╧═════╧════════════╝

It is PostgreSQL: everything possible :)