Converting Base64 to Hex in Redshift / python

Question

We are migrating our database from Postgres to Redshift and we are having some challenges with converting Base64 string to Hex.

In the old situation (in postgres) we have a function :

CREATE OR REPLACE FUNCTION hex_to_int(hexval character varying)
 RETURNS integer
 LANGUAGE plpgsql
 IMMUTABLE STRICT
AS $$
DECLARE
   result  int;
BEGIN
 EXECUTE 'SELECT x''' || hexval || '''::int' INTO result;
 RETURN result;
END;
$$

And we call this in the select:

SELECT 
encode(decode('AQDAAi0EAAoFAAcN5A=='::text, 'base64'::text), 'hex'::text) AS hexresult 
FROM table

The result is shown as: 0100c0022d04000a0500070de4

Redshift does not seem to support plpgsql functions, now looking for a different way to do this.

I have tried to do this with base64.b64decode

As a result I see:

Could you please check that your question is complete? You provided a function called `hex_to_int`, but your sample did not call the function. It appears that your requirement is to take a base64 string (eg `'AQDAAi0EAAoFAAcN5A=='`) and convert it to hex digits (`0100c0022d04000a0500070de4`) within an Amazon Redshift SELECT statement. Is that correct? — John Rotenstein, Apr 01 '18 at 22:00

score 2 · Accepted Answer · answered Apr 01 '18 at 22:31

This Amazon Redshift User-Defined Function will convert base64 into hex digits:

CREATE FUNCTION f_base64hexdigits (s varchar)
  RETURNS varchar
IMMUTABLE
AS $$
  import base64
  import binascii
  return binascii.hexlify(base64.b64decode(s))
$$ LANGUAGE plpythonu;

Use it like this:

SELECT f_base64hexdigits('AQDAAi0EAAoFAAcN5A==')

Output:

0100c0022d04000a0500070de4

Converting Base64 to Hex in Redshift / python

1 Answers1