postgresql 在存储函数中使用准备好的语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12707417/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using prepared statement in stored function
提问by Aleks G
I have a table in the database:
我在数据库中有一个表：
create table store (
...
n_status integer not null,
t_tag varchar(4)
t_name varchar,
t_description varchar,
dt_modified timestamp not null,
...
);
In my stored function I need to execute the same selectagainst this table multiple times:
在我的存储函数中，我需要select多次针对该表执行相同的操作：
select * from store
where n_place_id = [different values]
and t_tag is not null
and n_status > 0
and (t_name ~* t_search or t_description ~* t_search)
order by dt_modified desc
limit n_max;
Here, t_searchand n_maxare parameters into the stored function. I thought it would make sense to use a prepared statement for this, but I'm running into strange problems. Here's what I have:
在这里，t_search和n_max是存储函数的参数。我认为为此使用准备好的语句是有意义的，但我遇到了奇怪的问题。这是我所拥有的：
create or replace function fn_get_data(t_search varchar, n_max integer)
returns setof store as
$body$
declare
resulter store%rowtype;
mid integer;
begin
prepare statement prep_stmt(integer) as
select *
from store
where n_place_id =
and (t_name ~* t_search or t_description ~* t_search)
order by dt_modified
limit n_max;
for mid in
(select n_place_id from ... where ...)
loop
for resulter in
execute prep_stmt(mid)
loop
return next resulter;
end loop;
end loop;
end;$body$
language 'plpgsql' volatile;
However when I actually run the function with
但是，当我实际运行该函数时
select * from fn_get_data('', 30)
I receive this error:
我收到此错误：
ERROR: column "t_search" does not exist
LINE 3: and (t_name ~* t_search or t_description ~* t_search)
^
QUERY: prepare prep_stmt(integer) as
select * from store where n_status > 0 and t_tag is not null and n_museum =
and (t_name ~* t_search or t_description ~* t_search)
order by dt_modified desc limit maxres_free
Ok, maybe it doesn't like external variables in the prepared statement, so I changed this to be
好吧，也许它不喜欢准备好的语句中的外部变量，所以我把它改成了
prepare prep_stmt(integer, varchar, integer) as
select * from store where n_status > 0 and t_tag is not null and n_museum =
and (t_name ~* or t_description ~* )
order by dt_modified desc limit
...
for resulter in
execute prep_stmt(mid, t_search, n_max)
...
This time I get a different error:
这次我得到了一个不同的错误：
ERROR: function prep_stmt(integer, character varying, integer) does not exist
LINE 1: SELECT prep_stmt(mid, t_search, n_max)
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT prep_stmt(mid, t_search, n_max)
What am I missing here?
我在这里错过了什么？
EDITI added the relevant table structure at the top.
编辑我在顶部添加了相关的表结构。
采纳答案by Craig Ringer
Looks to me like the PL/PgSQL EXECUTEfor dynamic SQL trumps the regular SQL EXECUTEfor prepared statements.
在我看来，EXECUTE用于动态 SQL的 PL/PgSQL胜过EXECUTE用于准备语句的常规 SQL 。
Code:
代码：
create or replace function prep_test() returns void as $$
begin
PREPARE do_something AS SELECT 1;
EXECUTE do_something;
end;
$$ LANGUAGE 'plpgsql';
Test:
测试：
regress=# select prep_test(1);
ERROR: column "do_something" does not exist
LINE 1: SELECT do_something
^
QUERY: SELECT do_something
CONTEXT: PL/pgSQL function "prep_test" line 4 at EXECUTE statement
outside PL/PgSQL it works fine:
在 PL/PgSQL 之外它工作正常：
regress=# EXECUTE do_something;
?column?
----------
1
(1 row)
I'm not sure how you'd execute a prepared statement within PL/PgSQL.
我不确定您将如何在 PL/PgSQL 中执行准备好的语句。
Out of interest, whyare you trying to use prepared statements within PL/PgSQL? Plans are prepared and cached for PL/PgSQL anyway, it happens automatically.
出于兴趣，您为什么要尝试在 PL/PgSQL 中使用准备好的语句？无论如何，计划都是为 PL/PgSQL 准备和缓存的，它会自动发生。
回答by Trevor Young
There is a way to EXECUTEa prepared statement in a function, but like the accepted answer said, you typically don't wan't to do this in a function because the function already stores its plan.
有一种方法可以EXECUTE在函数中准备好语句，但就像接受的答案所说的那样，您通常不想在函数中执行此操作，因为该函数已经存储了其计划。
That being said, there are still use cases where you do need to use a prepared statement in a function. My use case for this is when using multiple schemas for different users where the schemas contain tables that are similarly named and you want to use the same function to access one of these tables based off of what the search_pathis set to. In this situation, because of the way the function stores its plan, using the same function after changing the search_pathcauses things to break. There are two solutions to this problem that I've stated. The first is to use EXECUTE '<Your query as a string here>'. But this can get very ugly for large queries, hence the reason to use the second method, which involves a PREPARE.
话虽如此，仍有一些用例需要在函数中使用准备好的语句。我的用例是为不同的用户使用多个模式，其中模式包含名称相似的表，并且您希望使用相同的函数根据设置的内容访问这些表之一search_path。在这种情况下，由于函数存储其计划的方式，更改后使用相同的函数search_path会导致事情中断。我已经说过这个问题有两种解决方案。首先是使用EXECUTE '<Your query as a string here>'. 但是对于大型查询，这可能会变得非常难看，因此使用第二种方法的原因，它涉及PREPARE.
So with the background as to 'why' you'd want to do this out of the way, here is the how:
因此，根据“为什么”你想要这样做的背景，这里是如何做的：
CREATE OR REPLACE FUNCTION prep_test()
RETURNS void AS $$
BEGIN
PREPARE do_something AS SELECT 1;
EXECUTE 'EXECUTE do_something;';
END;
$$ LANGUAGE plpgsql;
Though it will probably be in your best interests to add some protections to keep it from breaking. Something like:
尽管添加一些保护措施以防止其损坏可能符合您的最佳利益。就像是：
CREATE OR REPLACE FUNCTION prep_test()
RETURNS void AS $$
BEGIN
IF (SELECT count(*) FROM pg_prepared_statements WHERE name ilike 'do_something') > 0 THEN
DEALLOCATE do_something;
END IF;
PREPARE do_something AS SELECT 1;
EXECUTE 'EXECUTE do_something;';
DEALLOCATE do_something;
END;
$$ LANGUAGE plpgsql;
Again, those who think that they want to do this, usually probably shouldn't, but for those cases where it is needed, this is how you do it.
同样，那些认为他们想要这样做的人通常可能不应该这样做，但是对于那些需要这样做的情况，这就是您这样做的方式。
回答by Loek Bergman
You could use an EXECUTE statement like this in PLPGSQL:
您可以在 PLPGSQL 中使用这样的 EXECUTE 语句：
select magicvalue into str_execute from magicvalues where magickey = ar_requestData[2];
EXECUTE str_execute into str_label USING ar_requestData[3], ar_requestData[4]::boolean, ar_requestData[5]::int, ar_requestData[6];
This is code I use in my application. ar_requestData is an array with text values. In the table magicvalues do I store things like prepared statements. The select statement is for example:
这是我在我的应用程序中使用的代码。ar_requestData 是一个包含文本值的数组。在表 magicvalues 中，我是否存储了诸如准备好的语句之类的东西。选择语句例如：
insert into classtypes(label, usenow, ranking, description) values(,,,) returning label'
With kind regards,
亲切的问候，
Loek Bergman
洛克·伯格曼
回答by Moon_of_father
PREPARE statement is not allowed within plpgsql. You can splice all the statements inside a function and use dynamic execute after then. Here is an example.
plpgsql 中不允许使用 PREPARE 语句。您可以拼接一个函数内的所有语句，然后使用动态执行。这是一个例子。
create or replace function sp_test(f_total int) returns void as $ytt$
declare v_sql text;
declare i int;
begin
v_sql:='prepare ytt_s1 (int,timestamp) as select * from tbl1 where id = and log_time = ;';
while i < f_total
loop
v_sql:=v_sql||'execute ytt_s1('||i||',now());';
i := i + 1;
end loop;
v_sql:=v_sql||'deallocate ytt_s1;';
execute v_sql;
end;
$ytt$ language plpgsql;
