postgresql stored procedure with query on several schema

Question

Hello,

After some answer on a previous question (request over several schema), I try to write a stored procedure to select tables for several schemas (Each user have a schema).

create or replace public.select_simulations() returns setof simulation as $$
declare 
    users pg_user%ROWTYPE;
    simu simulation%ROWTYPE;
begin
    for users in select usename from pg_user where usename <> 'postgres' loop
        for simu in select id, name from (users.usename).simulation loop            
            return next simu;
        end loop;
    end loop;
end; 
$$

but it doesn't accept the (users.usename).simulation, and without the parenthesis it produced an error (seems to search a sub field, not a schema)...

So what is the correct syntax to tell that users.usename is a schema name ?

Thank you for your help!

Answer 1

You could take a look at the for-in-execute control structure:

FOR record_or_row IN EXECUTE text_expression LOOP 
    statements
END LOOP [ label ];

http://www.postgresql.org/docs/8.1/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

Something like:

...

for users in select usename from pg_user where usename <> 'postgres' loop
    for simu in execute 'select id, name from '||quote_ident(users.usename)||'.simulation' loop
        return next simu;
    end loop;
end loop;
...