Returning setof derived from for loop in plpgsql

Question

I have the below function. It is intended to loop through every schema and return all user IDs from a table in that schema. The schemas have the format: users[0-100].

$body$
declare
  r varchar;           
  sql text := 'select userid from $1.users';
begin
  for r in select schema_name from information_schema.schemata where schema_name ~ '^users'
  loop
    return query select format('select userid from %s.users', r);
  end loop;
  return;
end
$body$

I have tried a few different ways, but I haven't been able to return a set. Where am I going wrong?

Answer 1

This code:

return query select format('select userid from %s.users', r);

returns the text of queries to the caller instead of executing them and returning their results. You want a three-steps operation: build-execute-return.

These steps are easier to figure out if the code is broken into two lines (assuming query_text is a text variable declared above):

query_text := format('select userid from %I.users', r);
return query execute query_text;

Note: I've also changed %s to %I, the specifier dedicated to identifiers. If the schema identifier required quoting, it would happen automatically, whereas with %s the query would fail.