I have this multitenancy database let say of hotels. Each hotel Company is a schema in the database and all have same structure and table names.

Now I want to present menus from all available hotels. Basically I want something like

| schema_name | menu_id | menu_name | cost   |
| hotel_1     | 1       | Ugali     | 4000TZS|
| hotel_2     | 1       | Ugali     | 3500TZS|
| hotel_3     | 1       | Samaki    | 8000TZS|

I can get menu_id, menu_name and cost from menus tables. How do I achieve that (if it is possible at all) ?

SET SEARCH_PATH TO hotel_1, hotel_2, hotel_3 does not work and brings results from only one schema.

  • 1
    You have to write schema names explicitly. select ... from hotel_1.menu union all select ... from hotel_2.menu . search_path is more like path variable in os.
    – Sahap Asci
    Jun 27 '18 at 20:51
  • I see. Since we have schemas in the table with query select schema_name from information_schema.schemata WHERE schema_name NOT IN ('public', 'information_schema') AND schema_name NOT LIKE 'pg_%' AND schema_name NOT LIKE 'pg_%' is there a way to combine it will menu without manually adding the schema? The Schema keeps changing and adding them manually can become a problem Jun 28 '18 at 8:54
  • After looking about many ways I cannot find another, better way, to do it. Can you write it as an answer so that I can accept it? Jun 29 '18 at 10:35

The best way is writing your schema names explicitly like below;

select menu_id, menu_name, cost from hotel_1.menus
union all
select menu_id, menu_name, cost from hotel_2.menus
union all
select menu_id, menu_name, cost from hotel_3.menus

On the other hand, you can write a set returning function (or table returning function) and use execute to run dynamic query. Here is a sample set returning function for your case;

CREATE OR REPLACE FUNCTION public.get_all_menus (
  out menu_id integer,
  out menu_name TEXT,
  out cost DECIMAL(10,2)
  l_query TEXT;
  -- Create dynamic query
    array_to_string(array_agg(format('select menu_id, menu_name, cost from %I.%I', n.nspname, c.relname)) , E'\nunion all\n')
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
    c.relkind = 'r' AND
    n.nspname NOT IN ('pg_catalog', 'information_schema') AND
    n.nspname NOT LIKE '^pg_toast' AND 
    c.relname = 'menus';  --the table name which is common for all schemas 

  --run the query by using execute. 

LANGUAGE 'plpgsql'

You can use this function as if its a table. The usage is;

select * from get_all_menus();
  • The function adds great stuffs. Is there easy way to return respective schema too? I tried to add 'SELECT id, departure, destination, n.nspnspname from %I.%I', n.nspname, c.relname)...... but I get an error missing FROM-clause entry for table "n" I have the idea of what the error means but I'm struggling to find where to put that FROM clause. I have never done functional Programming with PGS. I did it a lot back in MSSQL Server days Jul 2 '18 at 7:51
  • Try format('select %L AS schema_name, %L AS table_name, menu_id, menu_name, cost from %I.%I', n.nspname, c.relname, n.nspname, c.relname)
    – Sahap Asci
    Jul 2 '18 at 8:03
  • That works fine. If I may bother you, can you recommend any good resource for learning Functions with Postgresql, especially for one with MSSQL background in Stored Proc/Functions. It can be text or Video. Thank you for great help! Jul 2 '18 at 8:11
  • I only use PostgreSQL's documentation which is quite good. You may want to check books list in postgresql.org
    – Sahap Asci
    Jul 2 '18 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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