I am trying to write function in postgresql, that creates temp_table with columns table_name text, table_rec jsonb and fill it through for loop with table names from my table containing names of tables and records in json. I have the for loop in string and I want to execute it. But it doesnt work. I have variable rec record, sql_query text and tab_name text and I want to do this:
CREATE OR REPLACE FUNCTION public.test51(
)
RETURNS TABLE(tabel_name text, record_json jsonb)
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
declare
rec record;
tabel_name text;
tabel_names text[];
counter integer := 1;
sql_query text;
limit_for_sending integer;
rec_count integer;
begin
select into tabel_names array(select "TABLE_NAME" from public."TABLES");
create temp table temp_tab(tab_nam text, recik jsonb);
while array_length(tabel_names, 1) >= counter loop
tabel_name := '"' || tabel_names[counter] || '"';
select into limit_for_sending "TABLE_LIMIT_FOR_SENDING_DATA" from public."TABLES" where "TABLE_NAME" = tabel_name;
sql_query := 'select count(*) from public.' || tabel_name;
execute sql_query into rec_count;
if (rec_count >= limit_for_sending and limit_for_sending is not null) then
sql_query := 'for rec in select * from public.' || tabel_name || '
loop
insert into temp_tab
select ' || tabel_name || ', to_jsonb(rec);
end loop';
execute sql_query;
end if;
counter := counter + 1;
end loop;
return query
select * from temp_tabik;
drop table temp_tabik;
end;
$BODY$;
Thank you for response.