dynamic portion of plpgsql function

Question

I have a function that loops through all points and compares them to other points (yes i know this doesn't need to be done in plpgsql - this is a toy MWE). 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 works. What I want to do is be able to have the table name as a parameter of the function, I am confused as to where to put the execute statement.

Please see postgresql.org/docs/current/static/… for looping through dynamic query results, construction is FOR-IN-EXECUTE — Eugene
– Eugene, Commented May 18, 2017 at 7:52
Also, I understand that this is just a toy example, but PostgreSQL already has a point type, which serves the same purpose that the type you've just created. It has a slightly different semantics though, i.e. you access its fields through p1[0] and p1[1] (setting them in an UPDATE statement and/or PL/pgSQL also works). — pozs
– pozs, Commented May 18, 2017 at 8:18

Vao Tsun · Accepted Answer · 2017-05-18 07:56:04Z

https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html:

The FOR-IN-EXECUTE statement is another way to iterate over rows:

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

Collectives™ on Stack Overflow

dynamic portion of plpgsql function

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related