PL/pgSQL 执行与执行 [英] PL/pgSQL perform vs execute

问题描述

在 PL/pgSQL 上执行和执行有什么区别?

What are the difference between perform and execute on PL/pgSQL?

来自手册:

有时对表达式或 SELECT 查询求值但丢弃结果很有用，例如在调用具有副作用但没有有用结果值的函数时.要在 PL/pgSQL 中执行此操作，请使用 PERFORM 语句.

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.

但是，当我尝试以下操作时:

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);

推荐答案

PERFORM 是用于调用 void 函数的 plpgsql 命令.PLpgSQL 小心无用的 SELECT 语句 - 没有 INTO 子句的 SELECT 是不允许的.但有时你需要调用一个函数而你不需要存储结果(或者函数没有任何结果).SQL 中的函数是用SELECT 语句调用的.但是在 PLpgSQL 中这是不可能的 - 所以引入了命令 PERFORM.

PERFORM is plpgsql command used for calls of void functions. PLpgSQL is careful about useless SELECT statements - the SELECT without INTO clause 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 SQL is called with SELECT statement. But it is not possible in PLpgSQL - so the command PERFORM was introduced.

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;
$$;

PERFORM 语句执行参数并忘记结果.

The PERFORM statements execute a parameter and forgot result.

你的例子执行'create table foo as (select 1)';

与 SELECT 'create table foo as (select 1)' 相同.它返回一个字符串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.

EXECUTE 语句计算表达式以获取字符串.在下一步中执行此字符串.

The EXECUTE statement evaluate a expression to get string. In next step this string is executed.

所以 EXECUTE '创建表' ||some_var ||'(a int)';有两步

1. 求值表达式 'create table ' ||some_var ||'(一个整数)'
2. 如果some_var例如是mytab，则执行命令create table mytab(a int)

1. evaluate expression 'create table ' || some_var || '(a int)'
2. if some_var is mytab for example, then execute a command create table mytab(a int)

PERFORM 语句用于函数调用，当函数未在赋值语句中使用时.EXECUTE 用于评估动态 SQL - 当 SQL 命令的形式在运行时已知时.

The PERFORM statement is used for function calls, when functions are not used in assignment statement. The EXECUTE is used for evaluation of dynamic SQL - when a form of SQL command is known in runtime.

这篇关于PL/pgSQL 执行与执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！