Hello,
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 !
views:
17answers:
2
+1
A:
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.
Magnus Hagander
2010-08-23 09:15:45
Thank you, I try to write a stored procedure, with some difficulties... (Another question: http://stackoverflow.com/questions/3546488/postgresql-stored-procedure-with-querie-on-several-schema)
rbleu
2010-08-23 10:02:14
A:
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
Frank Heikens
2010-08-23 09:24:30
That will still only get you the table from the first schema listed that has one named that, not all of them at once.
Magnus Hagander
2010-08-23 12:07:43