pg_xlat_stat_functions.sql

--
-- Reference implementation of functions described in
-- https://www.postgresql.org/message-id/CADkLM%3DcWdTvyAEQcxw-6er3umo%3DR1ZKU_9jf2ZinNxRf9MbQZg%40mail.gmail.com
--
-- These functions were made prior to proper input functions for pg_ndistinct and pg_dependencies, hence they
-- take a json input instead, which works because both text formats are already json-compliant.
--
-- Note that type json is used instead of jsonb to ensure that the given key order is preserved.
--

CREATE OR REPLACE FUNCTION pg_xlat_ndistinct_to_attnames(ndist json) RETURNS json
BEGIN ATOMIC
SELECT
    json_agg(
        json_build_object(
            'attributes', string_to_array(e.key, ', ')::integer[],
            'ndistinct', e.value::double precision
            )
        )
FROM
    json_each_text(ndist) AS e;
END;

CREATE OR REPLACE FUNCTION pg_xlat_ndistinct_from_attnames(j json) RETURNS json
BEGIN ATOMIC
SELECT
    json_object_agg(s.key, (a.a->>'ndistinct')::double precision)
FROM
    json_array_elements(j) AS a(a)
CROSS JOIN LATERAL (
    SELECT
        array_to_string(array_agg(t.t), ', ')
    FROM
        json_array_elements_text(a.a->'attributes') as t(t)
    ) AS s(key);
END;

CREATE OR REPLACE FUNCTION pg_xlat_dependencies_to_attnames(deps json) RETURNS json
BEGIN ATOMIC
SELECT
    json_agg(
        json_build_object(
            'attributes', string_to_array(split_part(e.key, ' => ', 1), ', ')::integer[],
            'dependency', split_part(e.key, ' => ', 2)::integer,
            'degree', e.value::double precision
            )
        )
FROM
    json_each_text(deps) AS e;
END;

CREATE OR REPLACE FUNCTION pg_xlat_dependencies_from_attnames(j json) RETURNS json
BEGIN ATOMIC
SELECT
    json_object_agg(s.key || ' => ' || (a.a->>'dependency'), (a.a->>'degree')::double precision)
FROM
    json_array_elements(j) AS a(a)
CROSS JOIN LATERAL (
    SELECT
        array_to_string(array_agg(t.t), ', ')
    FROM
        json_array_elements_text(a.a->'attributes') as t(t)
    ) AS s(key);
END;

\echo demonstration data
SELECT
    '{"6, -1": 14, "6, -2": 9143, "-1, -2": 13454, "6, -1, -2": 14549}' AS ndist_input,
    '{"-2 => 6": 0.292508, "-2 => -1": 0.113999, "6, -2 => -1": 0.348479, "-1, -2 => 6": 0.839691}' AS deps_input
\gset

SELECT
    pg_xlat_ndistinct_to_attnames(:'ndist_input'::json) AS ndist_to_attnames,
    pg_xlat_dependencies_to_attnames(:'deps_input'::json) AS deps_to_attnames
\gset

SELECT
    pg_xlat_ndistinct_from_attnames(:'ndist_to_attnames'::json) AS ndist_from_attnames,
    pg_xlat_dependencies_from_attnames(:'deps_to_attnames'::json) AS deps_from_attnames
\gset


SELECT
    :'ndist_input'::json AS ndist_input,
    :'ndist_to_attnames'::json AS ndist_to_attnames,
    :'ndist_from_attnames'::json AS ndist_from_attnames,
    (:'ndist_input'::jsonb = :'ndist_from_attnames'::jsonb) AS ndist_equals,
    :'deps_input'::json AS deps_input,
    :'deps_to_attnames'::json AS deps_to_attnames,
    :'deps_from_attnames'::json AS deps_from_attnames,
    (:'deps_input'::jsonb = :'deps_from_attnames'::jsonb) AS deps_equals
\gx