ASN.1 DER Decoder using PostgreSQL Recursive CTEs

decode_asn1_der.sql

CREATE OR REPLACE FUNCTION decode_asn1_der(asn1der bytea)
RETURNS TABLE (
  type_tag char(2),
  length integer,
  value bytea
)
LANGUAGE sql
AS $$
WITH RECURSIVE X AS (
  SELECT
    decode_asn1_der.asn1der AS asn1der,
    0 AS pos,
    NULL::text AS type_tag,
    NULL::integer AS length,
    NULL::bytea AS value
  UNION ALL
  SELECT
    x.asn1der,
    x.pos + CASE WHEN to_hex(get_byte(x.asn1der,x.Pos)) = '30' THEN 1 ELSE get_byte(x.asn1der,x.pos+1)+1 END + 1 AS pos,
    lpad(to_hex(get_byte(x.asn1der,x.pos)),2,'0') AS type_tag,
    get_byte(x.asn1der,x.pos+1) AS length,
    substring(x.asn1der from x.pos+3 for get_byte(x.asn1der,x.pos+1)) AS value
  FROM x WHERE x.pos < length(x.asn1der)
)
SELECT
  type_tag,
  length,
  value
FROM x WHERE pos > 0
$$;

-- Example:

SELECT * FROM decode_asn1_der('\x3059301306072a8648ce3d020106082a8648ce3d030107034200047837d449e1763e839de2516fed8552fadbf8da59a44eea4b62e5cae4a04b4b1f829fb89f175eb662d9033bb8d55349a1f90a21c0eef99a8f251a1ff80ca1e145'::bytea);

-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
type_tag | 30
length   | 89
value    | \x301306072a8648ce3d020106082a8648ce3d030107034200047837d449e1763e839de2516fed8552fadbf8da59a44eea4b62e5cae4a04b4b1f829fb89f175eb662d9033bb8d55349a1f90a21c0eef99a8f251a1ff80ca1e145
-[ RECORD 2 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
type_tag | 30
length   | 19
value    | \x06072a8648ce3d020106082a8648ce3d030107
-[ RECORD 3 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
type_tag | 06
length   | 7
value    | \x2a8648ce3d0201
-[ RECORD 4 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
type_tag | 06
length   | 8
value    | \x2a8648ce3d030107
-[ RECORD 5 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
type_tag | 03
length   | 66
value    | \x00047837d449e1763e839de2516fed8552fadbf8da59a44eea4b62e5cae4a04b4b1f829fb89f175eb662d9033bb8d55349a1f90a21c0eef99a8f251a1ff80ca1e145