postgres plpgsql how to properly convert function to use FORMAT in DECLARE

Question

I am writing a function in POSTGRES v13.3 that when passed an array of column names returns an array of JSONB objects each with the distinct values of one of the columns. I have an existing script that I wish to refactor using FORMAT in the declaration portion of the function.

The existing and working function looks like below. It is passed an array of columns and a dbase name. The a loop presents each column name to an EXECUTE statement that uses JSONB_AGG on the distinct values in the column, creates a JSONB object, and appends that to an array. The array is returned on completion. This is the function:

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;

I have refactored the function to the following. The script is now in the DECLARE portion of the function.

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$;

Unfortunately the two functions give different results on a toy data set (see bottom):

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

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

Here is a db<>fiddle of the preceding.

The challenge is in the EXECUTE. In the first instance the col argument is treated as a column identifier. In the refactored function it seems to be treated as just a text string. I think my approach is consistent with the docs and tutorials (example), and the answer from this forum here and the links included therein. I have tried playing around with combinations of ", ', and || but those were unsuccessful and don't make sense in a format statement.

Where should I be looking for the error in my use of FORMAT?

NOTE 1: From the docs I have so possibly the jsonagg() and distinct are what's preventing the behaviour I want:

Another restriction on parameter symbols is that they only work in SELECT, INSERT, UPDATE, and DELETE commands. In other statement types (generically called utility statements), you must insert values textually even if they are just data values.

TOY DATA SET:

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);

score 0 · Answer 1 · answered Mar 14 '22 at 15:50

https://www.postgresql.org/docs/14/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

The command string can use parameter values, which are referenced in the command as $1, $2, etc. These symbols refer to values supplied in the USING clause.

What you want is paramterize sql identifier(column name). You cannot do that. Access column using variable instead of explicit column name
Which means that select jsonb_agg( distinct $1) from %1$I In here "$1" must be %I type. USING Expression in the manual (EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];) will pass the literal value to it. But it's valid because select distinct 'hello world' from validtable is valid.

select jsonb_agg( distinct $1) from %1$I In here $1 must be same type as %1$I namely-> sql identifier.

-- Based on the following debug code, then you can solve your problem:

CREATE OR REPLACE FUNCTION foo2(_cols text[], _db text)
RETURNS void
LANGUAGE plpgsql as
$func$
DECLARE
    col text;
     interim jsonb;temp jsonb; y jsonb[];
BEGIN
    foreach col in array _cols
    loop
        EXECUTE  format( 'select jsonb_agg( distinct ( %1I ) ) from %2I', col,_db)   INTO interim;
        raise info 'interim: %', interim;
        temp := jsonb_build_object(col, interim);
        raise info 'temp: %', temp;
        y := array_append(y,temp);
        raise info 'y: %',y;
    end loop;
END
$func$;

postgres plpgsql how to properly convert function to use FORMAT in DECLARE

1 Answers1