PL/pgSQL用于多合一动态查询 [英] PL/pgSQL for all-in-one dynamic query

问题描述

我正在使用PostigreSQL10.我有一个多合一查询，用户可以选择类别样式，事件，区域的非标准组合来搜索构造.请记住，类别样式，事件，区域位于不同的表中.

I am using PostigreSQL10. I have an all-in-one query where a user can pick a non-standard combination of category style, event, area to search for a constructions. Keep in mind that category style, event, area are in different tables.

我想避免多个IF和JOIN.我还想避免查询计划程序每次都为错误的参数组合和查询的后继重新编译而缓存错误的计划.因此，我必须使用动态SQL.

I want to avoid the multiple IFs and JOINs. I also want to avoid the query planner caching the wrong plan for the wrong parameter combination and the sequel recompilation of the query every single time. So I have to use dynamic SQL.

要在PostgreSQL中获得动态SQL，我必须使用PL/pgSQL.但是，根据其文档

To get dynamic SQL in PostgreSQL, I have to use PL/pgSQL. But, according to its documentation

在PL/pgSQL函数内部生成动态命令，即 涉及不同表或不同数据类型的命令 每次执行时. PL/pgSQL缓存计划的常规尝试 用于命令的命令在这种情况下将不起作用.为了处理这种 问题，提供了EXECUTE语句.还没有计划 缓存通过EXECUTE执行的命令.相反，该命令是 每次运行语句时都要计划好时间.如果有多行 返回，则仅第一个将分配给INTO变量-

to generate dynamic commands inside your PL/pgSQL functions, that is, commands that will involve different tables or different data types each time they are executed. PL/pgSQL's normal attempts to cache plans for commands will not work in such scenarios. To handle this sort of problem, the EXECUTE statement is provided. Also, there is no plan caching for commands executed via EXECUTE. Instead, the command is always planned each time the statement is run. If multiple rows are returned, only the first will be assigned to the INTO variable - here

和

直接出现在PL/pgSQL函数中的

SQL命令必须参考 每次执行时都有相同的表和列；也就是说，你不能 在SQL命令中使用参数作为表或列的名称.到 克服此限制，您可以使用以下命令构造动态命令 PL/pgSQL EXECUTE语句—以执行新的解析为代价 分析并为每次执行构建新的执行计划-这里

SQL commands that appear directly in a PL/pgSQL function must refer to the same tables and columns on every execution; that is, you cannot use a parameter as the name of a table or column in an SQL command. To get around this restriction, you can construct dynamic commands using the PL/pgSQL EXECUTE statement — at the price of performing new parse analysis and constructing a new execution plan on every execution - here

因此，我猜测PL/pgSQL不是适合我的情况的工具，因为我有多个表.

So, I guess that PL/pgSQL is not the right tool for my case, since I have multiple tables.

我的问题是:PL/pgSQL是否实际上不适合我的情况，或者我缺少什么? 子问题:如果不合适，如何为postgreSQL编写动态sql语法，找不到任何教程.

My question is: is PL/pgSQL actually not suitable for my case, or am I missing something ? Sub-question: if it not suitable, how can I syntax dynamic sql for postgreSQL, I cannot find any tutorial.

谢谢

推荐答案

您可以在pl/pgsql execute中运行大多数查询.

You can run most queries inside pl/pgsql execute.

例如，这2个表联接选择将很好地工作:

For example, this 2 table join select will work just fine:

drop table if exists dyn_tab1;
create table dyn_tab1 (id int primary key, value text);
insert into dyn_tab1 values (1, 'test1'), (2, 'test2');

drop table if exists dyn_tab2;
create table dyn_tab2
  (id serial primary key, fk_id int references dyn_tab1(id), value text);
insert into dyn_tab2 (fk_id, value)
values (1, 'blahblah'), (1, 'blahblah3'), (1, 'foobar'), (2, 'asdf');

select *
from dyn_tab1 as t1
join dyn_tab2 as t2 on t2.fk_id = t1.id;

--I'm mixing here both format and USING (prepared statement).
--You can use format for everything tho. Or just concat strings.
do $$
declare
  l_row record;
begin
for l_row in 
execute format($query$ select *
                       from %I as t1
                       join %I as t2 on t2.fk_id = t1.id
                       where t1.id = $1;
               $query$, 'dyn_tab1', 'dyn_tab2')
using 2
loop
raise notice 'record: %', l_row;
end loop;
end;
$$;

create function dyn_test()
returns setof record
as $$
begin
return query execute format($query$ select *
                       from %I as t1
                       join %I as t2 on t2.fk_id = t1.id
                       where t1.id = $1;
               $query$, 'dyn_tab1', 'dyn_tab2')
using 2;
end;
$$ language plpgsql;

select * from dyn_test() as (id int, value text, id2 int, fk int, value2 text);

这篇关于PL/pgSQL用于多合一动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！