A postgres pure function that allow you to generate periscope embedded urls.

periscope_url.sql

-- We must have a function that given the json data and the api key, it must generate the correct periscope_url
-- ########################################
-- select periscope_url('jsondata', 'api');
-- #########################################

-- JSON DATA USED by DOCS: '{"dashboard":7863,"embed":"v2","filters":[{"name":"Filter1","value":"value1"},{"name":"Filter2","value":"1234"}]}'
-- API KEY USED BY DOCS: 'e179017a-62b0-4996-8a38-e91aa9f1'
-- Proof Of Concept:
--    psql=# select periscope_url('{"dashboard":7863,"embed":"v2","filters":[{"name":"Filter1","value":"value1"},{"name":"Filter2","value":"1234"}]}', 'e179017a-62b0-4996-8a38-e91aa9f1');
--    https://www.periscopedata.com/api/embedded_dashboard?data=%7B%22dashboard%22%3A7863%2C%22embed%22%3A%22v2%22%2C%22filters%22%3A%5B%7B%22name%22%3A%22Filter1%22%2C%22value%22%3A%22value1%22%7D%2C%7B%22name%22%3A%22Filter2%22%2C%22value%22%3A%221234%22%7D%5D%7D&signature=adcb671e8e24572464c31e8f9ffc5f638ab302a0b673f72554d3cff96a692740

CREATE EXTENSION pgcrypto; 

CREATE OR REPLACE FUNCTION periscope_url(jsonblob text, apikey text) RETURNS text AS $$
				DECLARE
								path text;
								result text;
        BEGIN
								path := '/api/embedded_dashboard?data=' || encode_uri(jsonblob);
								result := hmac(path, apikey, 'sha256');
								result := 'https://www.periscopedata.com' || path || '&signature=' || substring(result from 3);
								RETURN result;
        END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION encode_uri(input text)
  RETURNS text
  LANGUAGE plpgsql
  IMMUTABLE STRICT
AS $function$
DECLARE
  parsed text;
  safePattern text;
BEGIN
  safePattern = 'a-zA-Z0-9_~/\-\.';
  IF input ~ ('[^' || safePattern || ']') THEN
    SELECT STRING_AGG(fragment, '')
    INTO parsed
    FROM (
      SELECT prefix || encoded AS fragment
      FROM (
        SELECT COALESCE(match[1], '') AS prefix,
               upper(COALESCE('%' || encode(match[2]::bytea, 'hex'), '')) AS encoded
        FROM (
          SELECT regexp_matches(
            input,
            '([' || safePattern || ']*)([^' || safePattern || '])?',
            'g') AS match
        ) matches
      ) parsed
    ) fragments;
    RETURN parsed;
  ELSE
    RETURN input;
  END IF;
END;
$function$