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

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 '14 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 '14 at 16:58
    
Is the table in all the schemas exactly the same? –  Fabrizio Mazzoni Mar 22 '14 at 17:10
    
Yes, all schemas will have the exact same tables with each table structured exactly the same. –  user3449945 Mar 22 '14 at 17:12
1  
Use dynamic SQL - PL/PgSQL with EXECUTE. –  Craig Ringer Mar 23 '14 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  -- matching return type
$func$
BEGIN

RETURN QUERY EXECUTE (
   SELECT string_agg(format('SELECT bar, baz FROM %I.foo', sch), ' UNION ALL ')
   FROM   unnest(_schemas) sch
   );

END
$func$ LANGUAGE plpgsql;

Builds and executes a query of the following form dynamically:

SELECT bar, baz FROM u111.foo
UNION ALL
SELECT bar, baz FROM u222.foo
UNION ALL
SELECT bar, baz FROM u333.foo;

Schema names are escaped as identifiers properly to defend against SQL injection.

SQL Fiddle (returning query string instead of executing it).

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.