Type-safe PostgreSQL encode/decode

typesafe_encode.sql

--
-- Define custom enum type containing supported encoding algorithms
--
CREATE TYPE encoding AS ENUM ('BASE64', 'ESCAPE', 'HEX');

--
-- Type-safe alternative to encode(bytea, text)
--
CREATE OR REPLACE FUNCTION encode (value bytea, alg encoding) RETURNS text AS $$
#print_strict_params on
DECLARE
  results text;
BEGIN
  CASE alg
    WHEN 'BASE64'::encoding THEN
         results := encode(value, 'base64');
    WHEN 'ESCAPE'::encoding THEN
         results := encode(value, 'escape');
    WHEN 'HEX'::encoding then
         results := encode(value, 'hex');
    ELSE
         RAISE EXCEPTION 'unknown encoding %', alg;
  END CASE;
  RETURN results;
END;
$$ LANGUAGE plpgsql
IMMUTABLE
RETURNS NULL ON NULL INPUT
PARALLEL SAFE;

--
-- Type-safe alternative to decode(text, text)
--
CREATE OR REPLACE FUNCTION decode (value text, alg encoding) RETURNS bytea AS $$
#print_strict_params on
DECLARE
  results bytea;
BEGIN
  CASE alg
    WHEN 'BASE64'::encoding THEN
         results := decode(value, 'base64');
    WHEN 'ESCAPE'::encoding THEN
         results := decode(value, 'escape');
    WHEN 'HEX'::encoding then
         results := decode(value, 'hex');
    ELSE
         RAISE EXCEPTION 'unknown encoding %', alg;
  END CASE;
  RETURN results;
END;
$$ LANGUAGE plpgsql
IMMUTABLE
RETURNS NULL ON NULL INPUT
PARALLEL SAFE;

--
-- Convenience function that converts from text/UTF-8 
--
CREATE OR REPLACE FUNCTION encode_from_text(value text, alg encoding) RETURNS text AS $$
  SELECT encode(convert_to(value, 'utf8'::name), alg);
$$ LANGUAGE sql
IMMUTABLE
RETURNS NULL ON NULL INPUT
PARALLEL SAFE;

--
-- Convenience function that converts to text/UTF-8
--
CREATE OR REPLACE FUNCTION decode_as_text(value text, alg encoding) RETURNS text AS $$
  SELECT convert_from(decode(value, alg), 'utf8'::name);
$$ LANGUAGE sql
IMMUTABLE
RETURNS NULL ON NULL INPUT
PARALLEL SAFE;