postgresql PL/pgSQL 执行与执行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42920998/
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
PL/pgSQL perform vs execute
提问by Guy s
What are the difference between perform and execute on PL/pgSQL?
在 PL/pgSQL 上执行和执行有什么区别？
From the manual:
从手册：
Sometimes it is useful to evaluate an expression or SELECT query but discard the result, for example when calling a function that has side-effects but no useful result value. To do this in PL/pgSQL, use the PERFORM statement.
有时评估表达式或 SELECT 查询但丢弃结果很有用，例如在调用具有副作用但没有有用结果值的函数时。要在 PL/pgSQL 中执行此操作，请使用 PERFORM 语句。
But, when I'm trying something like:
但是，当我尝试以下操作时：
perform 'create table foo as (select 1)';
Nothing happens. Although this query should have side effects (creating table), and the result can be discarded.
没发生什么事。虽然这个查询应该有副作用（创建表），结果可以丢弃。
I think I get 1 thing right: in order to run functions I can use perform:
我想我做对了一件事：为了运行我可以使用的功能：
perform pg_temp.addInheritance(foo);
回答by Pavel Stehule
PERFORMis plpgsql command used for calls of void functions. PLpgSQL is careful about useless SELECTstatements - the SELECTwithout INTOclause is not allowed. But sometimes you need to call a function and you don't need to store result (or functions has not any result). The function in SQLis called with SELECTstatement. But it is not possible in PLpgSQL - so the command PERFORMwas introduced.
PERFORM是用于调用 void 函数的 plpgsql 命令。PLpgSQL 小心无用的 SELECT语句 -不允许使用SELECTwithoutINTO子句。但有时你需要调用一个函数而你不需要存储结果（或者函数没有任何结果）。函数 inSQL是用SELECT语句调用的。但是在 PLpgSQL 中这是不可能的 - 因此PERFORM引入了该命令。
CREATE OR REPLACE FUNCTION foo()
RETURNS void AS $$
BEGIN
RAISE NOTICE 'Hello from void function';
END;
$$ LANGUAGE plpgsql;
-- direct call from SQL
SELECT foo();
-- in PLpgSQL
DO $$
BEGIN
SELECT foo(); -- is not allowed
PERFORM foo(); -- is ok
END;
$$;
The PERFORMstatements execute a parameter and forgot result.
该PERFORM语句执行的参数，忘记结果。
Your example perform 'create table foo as (select 1)';
你的榜样 perform 'create table foo as (select 1)';
is same like SELECT 'create table foo as (select 1)'. It returns a string "create table foo as (select 1)" and this string is discarded.
是一样的SELECT 'create table foo as (select 1)'。它返回一个字符串“create table foo as (select 1)”并且这个字符串被丢弃。
The EXECUTEstatement evaluate a expression to get string. In next step this string is executed.
该EXECUTE语句评估表达式以获取字符串。在下一步中，执行此字符串。
So EXECUTE 'create table ' || some_var || '(a int)';has two steps
所以EXECUTE 'create table ' || some_var || '(a int)';有两个步骤
- evaluate expression
'create table ' || some_var || '(a int)' - if
some_varis mytab for example, then execute a commandcreate table mytab(a int)
- 评估表达式
'create table ' || some_var || '(a int)' some_var例如，如果是 mytab，则执行命令create table mytab(a int)
The PERFORMstatement is used for function calls, when functions are not used in assignment statement. The EXECUTEis used for evaluation of dynamic SQL - when a form of SQL command is known in runtime.
PERFORM当赋值语句中不使用函数时，该语句用于函数调用。在EXECUTE用于动态SQL的评价-当SQL命令的一种形式在运行时是已知的。
回答by Vao Tsun
Further next line in docs you quote:
您引用的文档中的下一行：
This executes query and discards the result. Write the query the same way you would write an SQL SELECT command, but replace the initial keyword SELECT with PERFORM.
这将执行查询并丢弃结果。以与编写 SQL SELECT 命令相同的方式编写查询，但将初始关键字 SELECT 替换为 PERFORM。
Emphasis mine
强调我的
executein its turn executes dynamic query (same docs above)
execute依次执行动态查询（与上面相同的文档）
