主页 / dba / 问题 / 210755
Accepted
Stefano Mtangoo
Asked: 2018-06-28 10:06:11 +0800 CST

跨架构从相同表名获取数据

  • 772

我有这个多租户数据库，比如酒店。每个酒店公司都是数据库中的一个模式，并且都具有相同的结构和表名。

现在我想展示所有可用酒店的菜单。基本上我想要类似的东西

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

我可以从表中得到menu_id,menu_name和。我该如何实现（如果可能的话）？costmenus

SET SEARCH_PATH TO hotel_1, hotel_2, hotel_3不起作用，只能从一个模式中获取结果。

postgresql

1 个回答

  1. Best Answer

    最好的方法是像下面这样明确地写下你的模式名称；

    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

    另一方面，您可以编写一个集合返回函数（或表返回函数）并使用execute 来运行动态查询。这是您的案例的样本集返回函数；

    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;

    您可以像使用表格一样使用此功能。用法是；

    select * from get_all_menus();
    • 2

相关问题