width_bucket() with dynamic array input

width_bucket_arr_input.sql

WITH q_buckets AS (
    SELECT
        10 AS buckets,
        100 AS max_bar_width,
        '■' AS bar_char
),
q_bounds AS (
    SELECT
        min(abalance) AS min_val,
        max(abalance) AS max_val,
        max(abalance) - min(abalance) AS max_min_diff,
        (max(abalance) - min(abalance)) / max(buckets) AS bucket_step
    FROM
        pgbench_accounts, q_buckets
),
q_bucket_bounds AS (
    SELECT
        array_agg(bucket_lower_bound) AS bucket_lower_bounds_arr
    FROM (
        SELECT
            min_val + bucket_nr * bucket_step AS bucket_lower_bound,
            min_val + (bucket_nr + 1) * bucket_step AS bucket_higher_bound
        FROM
            q_buckets, q_bounds, generate_series(0, buckets - 1) bucket_nr)
),
q_bucket_bounds_w_ordinality AS (
    SELECT *
    FROM
        unnest((SELECT bucket_lower_bounds_arr FROM q_bucket_bounds))
        WITH ORDINALITY x (bucket_lower_bound, bucket)
),
q_bucketed AS (
    SELECT
        width_bucket(abalance, (SELECT bucket_lower_bounds_arr FROM q_bucket_bounds)) AS bucket,
        count(*) AS bucket_items
    FROM pgbench_accounts
    GROUP BY 1
    ORDER BY 1
),
q_buckets_max_count AS (
    -- Needed for bucket count "normalization", for display purposes
    SELECT max(bucket_items) AS max_bucket_items FROM q_bucketed
)
SELECT
    b.bucket,
    '[' || bb.bucket_lower_bound::text || ',' || coalesce((
        SELECT
            bucket_lower_bounds_arr[b.bucket + 1]
        FROM q_bucket_bounds)::text, (select max_val::text from q_bounds)) || ')' AS RANGE,
    b.bucket_items AS count,
    repeat((
        SELECT
            bar_char
        FROM q_buckets), (bucket_items::numeric / (
            SELECT
                max_bucket_items
            FROM q_buckets_max_count) * (
            SELECT
                max_bar_width
            FROM q_buckets))::int) AS count_as_bar
FROM
    q_bucketed b
    JOIN q_bucket_bounds_w_ordinality bb USING (bucket);