postgresql 在变量模式名称中查询表

evrscar2  于 2022-11-23  发布在  PostgreSQL
关注(0)|答案(1)|浏览(193)

我想在变量架构中运行表查询。
我有一个postgresql数据库,我已经为它编写了以下查询:

SELECT
    project_id, project_name, schema_p
FROM public.projects
JOIN public.schema_ps
ON public.schema_ps.root_id = public.projects.project_id
ORDER BY project_id ASC

schema_p列包含模式的名称,这些模式都有一个名为com_set的表,我需要按如下方式查询该表:

SELECT dev_id, dev_name FROM [variable_schama_name].com_set

我想做的是编写一个查询来生成一个组合结果,生成的数据集将包含project_idproject_nameschema_pdev_iddev_name。这意味着我在前三列中有重复项,在倒数第二列中有唯一项。我希望运行第一个查询以获取模式的名称,然后对每个模式运行第二个查询。
希望这一切都有意义。谢谢你的帮助!

yx2lnoni

yx2lnoni1#

一种可能的解决方案依赖于没有性能保证的动态查询:

CREATE OR REPLACE FUNCTION my_query(OUT project_id int, OUT project_name text, OUT schema_p text, OUT dev_id int, OUT dev_name text)
RETURNS setof record LANGUAGE plpgsql AS $$
DECLARE
  txt text ;
BEGIN
SELECT string_agg(
'SELECT p.project_id, p.project_name, s.schema_p, c.dev_id, c.dev_name
  FROM public.projects AS p
  INNER JOIN public.schema_ps AS s
    ON s.root_id = p.project_id
 CROSS JOIN ' || ps.schema_p || '.com_set AS c', ' UNION ALL ')
|| ' ORDER BY project_id ASC'
FROM public.schema_ps AS ps
INTO txt ;

RETURN QUERY EXECUTE txt ;

END ; $$ ;

然后调用my_query

SELECT my_query() ;

相关问题