Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have multiple schemas that I need to query across. The schemas are not know in advance for example:

  • U111
  • U222
  • U333

I need to select across these three schemas from a specific table in each of these schemas. Can I put this query across these three schemas? If so, how can I query across them? Remember, I will not know the schema names ahead of time, so they will need to be built dynamically.

Thank you for your help!

share|improve this question
    
You could write down a function that will query the pg_namespace table (which contains all the schemas in the DB) and then run the query against every table in the schema if exists. –  Fabrizio Mazzoni Mar 22 at 16:14
    
Fabrizio, thank you for your respone. Is there is a way to accomplish this in a view instead of a function? –  user3449945 Mar 22 at 16:58
    
Is the table in all the schemas exactly the same? –  Fabrizio Mazzoni Mar 22 at 17:10
    
Yes, all schemas will have the exact same tables with each table structured exactly the same. –  user3449945 Mar 22 at 17:12
1  
Use dynamic SQL - PL/PgSQL with EXECUTE. –  Craig Ringer Mar 23 at 11:15

2 Answers 2

If you just need results from multiple schemas, you can re-use the same query string and set the search_path in between:

SET search_path = u111, public;
SELECT * FROM foo;
SET search_path = u222, public;
SELECT * FROM foo;
...

If you need to combine results from multiple schemas (probably your use-case), you can either build the statement in your client or use a plpgsql function with dynamic SQL and EXECUTE. That's what I would do. Plain SQL does not allow parametrized identifiers (schema, table, column, ...).

CREATE OR REPLACE FUNCTION foo(_schemas text[])
  RETURNS TABLE (bar int, baz text) AS  -- define matching return type
$func$
BEGIN

RETURN QUERY EXECUTE (
SELECT string_agg(format('SELECT bar, baz FROM %I.foo', s)
                , E'\nUNION ALL\n')
FROM   unnest(_schemas) s
);

END
$func$ LANGUAGE plpgsql;

SQL Fiddle.

share|improve this answer

If your table structures are the same, you could use table inheritance then create a master schema with all the table structures and have the others inherit table from that one: Here is an article we wrote describing the technique:

http://www.postgresonline.com/journal/archives/59-How-to-Inherit,-Unherit-and-Merge-Inherit.html

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.