Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


drop table if exists example;
create temporary table example(id int, str text, val integer);
insert into example values
(1, 'a', 1),
(2, 'a', 2),
(3, 'b', 2);

TABLE example
3 rows affected



id
str
val


1
a
1

2
a
2

3
b
2


CREATE OR REPLACE FUNCTION foo1(text[], text)
RETURNS text[] as $$
declare
	col text;
	interim jsonb;
	temp jsonb;
	y jsonb[];
begin 
	foreach col in array $1
	loop
		execute
		'select jsonb_agg(distinct '|| col ||') from ' || $2 into interim;
		temp := jsonb_build_object(col, interim);
		y := array_append(y,temp);
	end loop;
	return y;
end;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION foo2(_cols text[], _db text)
RETURNS jsonb[]
LANGUAGE plpgsql as 
$func$
DECLARE
    _script text := format(
                'select jsonb_agg( distinct($1)) from %1$I', _db
                );
    col text;
    interim jsonb;
    temp jsonb;
    y jsonb[];
BEGIN 
    foreach col in array _cols
    loop
        EXECUTE _script USING col INTO interim;
        temp := jsonb_build_object(col, interim);
        y := array_append(y,temp);
    end loop;
    return y;
END
$func$;
SELECT foo1(array['id','val'], text 'example');


foo1


{"{"id": [1, 2, 3]}","{"val": [1, 2]}"}


SELECT foo2(array['id','val'], text 'example');


foo2


{"{"id": ["id"]}","{"val": ["val"]}"}