postgresql request over several schema

Question

I have a database, with every users having a schema.

Is there a way to query a table in every schema?

Something like: select id, name from *.simulation doesn't work...

Thank you for your help !

Answer 1

No, you will need to write a function - either a server side function or a client side function in whatever language you're using - that executes the query once for each schema.

You could also create a VIEW that does UNION ALL between all the schemas, but that's going to be a lot of work to maintain if your schemas are dynamically added and removed.

Answer 2

Yes you can, use SET search_path TO ... to point to all schema's. If you don't know all the names of the schemas, wrap it in a function that first selects all schemas and then set the entire search_path.

http://www.postgresql.org/docs/current/interactive/sql-set.html