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.
It seems you have some table that contains the information for which tables you want to return all rows as JSONB. And that meta-table also contains a column that sets a threshold under which the rows should not be returned.
You don't need the temp table or an array to store the table names. You can iterate through the query on the TABLES table and run the dynamic SQL directly in that loop.
return query in PL/pgSQL doesn't terminate the function, it just appends the result of the query to the result of the function.
Dynamic SQL is best created using the format() function because it is easier to read and using the %I placeholder will properly deal with quoted identifiers (which is really important as you are using those dreaded upper case table names)
As far as I can tell, your function can be simplified to:
CREATE OR REPLACE FUNCTION public.test51()
RETURNS TABLE(tabel_name text, record_json jsonb)
LANGUAGE plpgsql
AS
$BODY$
declare
rec record;
sql_query text;
rec_count bigint;
begin
for rec in
select "TABLE_NAME" as table_name, "TABLE_LIMIT_FOR_SENDING_DATA" as rec_limit
from public."TABLES"
loop
if rec.rec_limit is not null then
execute format('select count(*) from %I', rec.table_name)
into rec_count;
end if;
if (rec.rec_limit is not null and rec_count >= rec.rec_limit) then
sql_query := format('select %L, to_jsonb(t) from %I as t', rec.table_name, rec.table_name);
return query execute sql_query;
end if;
end loop;
end;
$BODY$;
Some notes