How to execute a piece of code for each element of the result of a dynamic query in PostgreSQL?-CodePudding

I have a dynamic query in my PostgreSQL function:

execute format('select something from %', table_name)

I want to run some code for every record returned by the above statement.

How can I do this?

My first idea is as follows.

I need to do something like this:

execute format('select something from %', table_name)
into my_data;

for my_record in my_data
loop

-- Do something with my_record

end loop;

In order to use the above code in a function or procedure, I need to declare the variables my_data and my_record.

my_record has the data type record. What could the data type of my_data be?

CodePudding user response：

You can use a dynamic statement in a FOR loop:

DECLARE
   r record;
BEGIN
   FOR r IN
      EXECUTE format('select something from %I', table_name)
   LOOP
      /* "r" now contains a table row */
   END LOOP;
END;