Dynamic sql in plpgsql procedure with unknown number of bound variables

Question

I am rewriting an Oracle database application to PostgreSQL. Extensive structures are created dynamically there. Now I am rewriting the procedure where dynamic sql is created and maintained using Oracle dbms_sql. Dynamic SQL is constructed, then adds variables in the loop (unknown how many variables there are), and then in the second loop it binds these variables via dbms_sql.bind_variable. Variables can be of different types (anydata value). Below is an example fragment of code in Oracle (PL/SQL) with the first loop:

    v_col_id := v_cols.first;
    while v_col_id is not null loop
      if in_col_info.exists(v_col_id) then
        v_cmd := v_cmd || ',';
        v_cmd := v_cmd || ':' || in_col_info(v_col_id).name;
        v_cmd := v_cmd || ',';
        v_cmd := v_cmd || ':' ||
                 mod_com.get_pf_col_name(in_col_info(v_col_id).name);
      end if;
      v_col_id := v_cols.next(v_col_id);
    end loop;

Below is a second sample code snippet in Oracle with a second loop that binds variables to dynamic sql, bind_anydata_value is a procedure that binds variables of different types (v_cols is a associative array of anydata indexed by binary_integer):

    v_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(v_cursor, v_cmd, dbms_sql.native);
    v_col_id := v_cols.first;
    while v_col_id is not null loop
      if in_col_info.exists(v_col_id) then
        bind_anydata_value(v_cursor,
                           in_col_info(v_col_id),
                           v_cols(v_col_id));
        dbms_sql.bind_variable(v_cursor,
                               model_common.get_pf_col_name(in_col_info(v_col_id).name),
                               -1);
      end if;
      v_col_id := v_cols.next(v_col_id);
    end loop;
  
    v_result := dbms_sql.execute(v_cursor);

I only show simplified code snippets. I want to illustrate by this, what I have a problem with. I don't know how to do it in PostgreSQL to bind an unknown number of variables.

I replaced the anydata variable array in PostgreSQL with a jsonb array. I want to use the EXECUTE with USING clause. But I thought that dynamically constructing variables in the USING clause would probably fail. I looked for a solution for a long time on the forum and on the Internet, but I did not find it.

Is it possible to bind a different number of variables using the EXECUTE ... USING clause? Or is there any way to bind variables directly to dynamic cursor text like Oracle does dbms_sql.bind_variable? Maybe it's possible somehow using the format function?

Answer 1

This is not possible by PL/pgSQL. If you can use Orafce extension, then there is support of dbms_sql API https://github.com/orafce/orafce#package-dbms_sql