The dynamic part of the plpgsql function
I have a function that traverses all points and compares them to other points (yes, I know it doesn't need to be done in plpgsql - it's an MWE toy). The function returns the points with the largest x-coordinate:
create type point as (x integer, y integer);
create or replace function test() returns set of Point as
$$
declare
p1 point;
p2 point;
bool integer;
begin
for p1 in select * from table loop
bool := 0;
for p2 in select * from table loop
if p2.x > p1.x then bool :=1;
exit;
end if;
end loop;
if bool = 0 then return next p1;
end if;
end loop;
end;
$$ language 'plpgsql';
which is working. I want to have the table name as a parameter to a function, I am confused as to where to put the operator execute
.
+3
source to share
1 answer
https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html :
The FOR-IN-EXECUTE operator is another way to iterate over strings:
t=# do
$$
declare
_t text := 'pg_tables';
_r record;
begin
for _r in execute format('select * from %I limit 4',_t) loop
raise info '%',_r.tablename;
end loop;
end;
$$
;
INFO: s141
INFO: events
INFO: tg_rep_que
INFO: t4
DO
+6
source to share