plpgsql here's how to iterate through columns and convert each to jsonb

Question

I have a table and I want to convert each column in to a jsonb array of the distinct values. I thought I would first get the column names and then iterate through each column to do the conversion.

Here's the code for a small example:

-------  EXPERIMENT WITH PLPGSQL
create temporary table example(id int primary key, str text, val integer);
insert into example values
(1, 'a', 1),
(2, 'a', 2),
(3, 'b', 2);

drop function foo();
CREATE OR REPLACE FUNCTION foo()
RETURNS text[] as $$
declare
    x text[];
    col text;
    interim text;
    y text[];
begin 
    select array (
        select column_name
        from information_schema."columns" c
        where table_name = 'example'
        ) into x;
    foreach col in array x
    loop
        execute
        'select jsonb_agg(distinct '||col||') from example' into interim;
        raise notice '% %', col,interim;
        interim := jsonb_build_object(col, interim);
        y := array_append(y,interim);
    end loop;
    raise notice 'These are columns: %', x;
    raise notice 'This is y: %', y;
    return y;
end;
$$ LANGUAGE plpgsql;

-- direct call from SQL
SELECT foo();

The output is close but looks like this:

id [1, 2, 3]
val [1, 2]
str ["a", "b"]
These are columns: {id,val,str}
This is y: {"{\"id\": \"[1, 2, 3]\"}","{\"val\": \"[1, 2]\"}","{\"str\": \"[\\\"a\\\", \\\"b\\\"]\"}"}

What I expect is 'y' will look like:

{{"id": "[1, 2, 3]"},{"val": "[1, 2]"},{"str": "[\"a\", \"b\"]"}}

What am I missing? Nothing. Figured it out except for all the escaped quotes.

[SOLUTION] So the function above was edited and pretty much does what I want. It took a few hours of looking around and digging and hammering away in my dbeaver ide. That's unfortunate because I needed to avoid that loss of time. I'm not sure why no one just posted guidance ie. references to read. Anyways, I will because too many of the plpgsql examples you will find are either too simple or way too complex. You can waste a ton of time reading in to stuff that's ultimately useless. So assuming you know how to program but are new to plpgsql here's some links:

best overview of functions in plpgsql. In particular note the 'raise notice' function, it's like 'console.log' if you're JS/node dev. This is good too but only if you have time.
the postgres docs are marginally useful. They are very comprehensive which means you will miss important details like: "When CREATE OR REPLACE FUNCTION is used to replace an existing function, the ownership and permissions of the function do not change." That's why I have the drop function ... statement. Looks like that should be a necessary inclusion if you're just prototyping repeatedly in an ide.
you notice I created a function and called it from SQL instead of using DO? This is why (DO can't RETURN anything). I wasted a lot of time early on because of this.
double dollar signs ($$)? Also known as 'dollar-quoted string constants' as explained here. Some folks use them others don't and the reasons are a mystery until someone explains it.
wondering about loops? This is a good succinct answer with a link to a more detailed answer.
notice the || in the 'execute' block? I came across those here. That was fortunate as I wasted a bit of time trying to construct the sql statement otherwise.

So those are the little things that slowed me down. HTH someone else.

plpgsql here's how to iterate through columns and convert each to jsonb

0 Answers0