create or replace function run_length_encode(str) as table
 (WITH RECURSIVE cte AS (
  SELECT
    1 AS pos,
    1 AS counter,
    SUBSTRING(str, 1, 1) AS char,
    SUBSTRING(str, 2, 1) AS nextc,
  UNION ALL
  SELECT
    pos + 1 as pos,
    CASE
      WHEN nextc = char THEN counter + 1
      ELSE 1
    END as counter,
    nextc as char,
    SUBSTRING(str, 2 + pos, 1) as nextc
  FROM cte
  WHERE pos < LENGTH(str)
 )
 SELECT STRING_AGG( counter || char, '' ORDER BY pos) AS encoded_string
 FROM cte
 WHERE nextc != char
 );