Is there a way to store rows returned by EXECUTE query_str in a temp table or use as a subquery?

Question

considering a function like this:

drop function if exists some_func;
create function some_func(table_name text, field_name text)
returns void as $$
declare query_str text;
begin
    query_str := format(
        'update %1$I' ||
        'set %2$I = 1', ||
        'some_other_value = null' ||
        'returning id, %1$s, %2$I',
        table_name,
        field_name
    );
    query_str = case when field_name = 'some value'
        -- offtopic: i'd also appreciate a better way todo this :)
        then regexp_replace(query_str, ',\s*some_other_value = null', '')
        else query_str
    end;
    execute query_str;
end;
$$ language plpgsql;

i need a way to save the rows returned from this update query execute query_str; in a temp table or use directly in another update query. something like:

create temp table my_tmp_tbl on commit drop as execute query_str;
-- or
with update_sql as execute query_str create temp table my_tmp_table as select * from update_sql;
-- or
update some_other_table
set some_field = update_query.field_name
from execute squery_str as update_query
where some_other_table.object_id = update_query.id;

is it possible to do something along those lines?

Answer 1

PL/pgSQL has not any direct tool for this task.

I think so there are three possibilities:

1. using FOR EXECUTE cycle:

DECLARE r record;
BEGIN
  FOR r IN EXECUTE 'UPDATE ... RETURNING id, c2, c3'
  LOOP
    INSERT INTO tmp_tab VALUES(r.id, r.c2, r.c3);
  END LOOP;
END;

2. using RETURN QUERY EXECUTE and INSERT SELECT:

CREATE OR REPLACE FUNCTION fx()
RETURNS TABLE(id int, c2 int, c3 int)
AS $$
BEGIN
  RETURN QUERY EXECUTE 'UPDATE ... RETURNING id, c2, c3';
END;
$$ LANGUAGE plpgsql

CREATE OR REPLACE FUNCTION outer_fx()
RETURNS void AS $$
BEGIN
  INSERT INTO tmp_tab SELECT * FROM fx();
END;
$$ LANGUAGE plpgsql;

3. Probably the best way is storing data in tmp table directly inside dynamic SQL:

DECLARE r record;
BEGIN
  EXECUTE 'WITH q1 AS (UPDATE ... RETURNING id, c2, c3)'
          'INSERT INTO tmp_tab SELECT * FROM q1';
  ...