Fetch Data from same table name across schema 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.
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)
)
RETURNS SETOF RECORD AS
$$
DECLARE
  l_query TEXT;
BEGIN
  -- Create dynamic query
  SELECT
    array_to_string(array_agg(format('select menu_id, menu_name, cost from %I.%I', n.nspname, c.relname)) , E'\nunion all\n')
  INTO
    l_query
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE
    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. 
  RETURN QUERY EXECUTE l_query;

END;
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;


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

select * from get_all_menus();