postgresql stored procedure with query on several schema

Question

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!

Version 8.1 will be out of service this year, you'd better upgrade to a newer version. PostgreSQL has become much better since november 2005 as well, just a waist of time to make something for this old and outdated version. wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy — Frank Heikens
– Frank Heikens, Commented Aug 23, 2010 at 10:18
Release policy has been moved to official site: postgresql.org/support/versioning — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 12, 2012 at 4:46

Edmund · Accepted Answer · 2010-08-23 11:57:18Z

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;
...

answered Aug 23, 2010 at 11:57

Edmund

10.8k3 gold badges42 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql stored procedure with query on several schema

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related