postgresql 动态 SQL (EXECUTE) 作为 IF 语句的条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8449011/
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
Dynamic SQL (EXECUTE) as condition for IF statement
提问by Matt
I want to execute a dynamic SQL statement, with its returned value being the conditional for an IFstatement:
我想执行一个动态 SQL 语句，其返回值是IF语句的条件：
IF EXECUTE 'EXISTS (SELECT 1 FROM mytable)' THEN
This generates the error ERROR: type "execute" does not exist.
这会产生错误ERROR: type "execute" does not exist。
Is it possible to do this, or is it necessary to execute the SQL before the IF statement into a variable, and then check the variable as the conditional?
是否可以这样做，或者是否需要将IF语句之前的SQL执行到变量中，然后将变量作为条件进行检查？
回答by Erwin Brandstetter
This construct is not possible:
这种构造是不可能的：
IF EXECUTE 'EXISTS (SELECT 1 FROM mytable)' THEN ...
IF EXECUTE 'EXISTS (SELECT 1 FROM mytable)' THEN ...
You can simplify to:
您可以简化为：
IF EXISTS (SELECT 1 FROM mytable) THEN ...
But your example is probably just simplified. For dynamic SQLexecuted with EXECUTE, read the manual here. You can check for FOUNDafter RETURN QUERY EXECUTE:
但是您的示例可能只是简化了。对于使用执行的动态 SQLEXECUTE，请阅读此处的手册。您可以在FOUND之后检查RETURN QUERY EXECUTE：
IF FOUND THEN ...
However:
然而：
Note in particular that
EXECUTEchanges the output ofGET DIAGNOSTICS, but does not changeFOUND.
尤其要注意的是，
EXECUTE改变了 的输出GET DIAGNOSTICS，但不会改变FOUND。
Bold emphasis mine. For a plain EXECUTEdo this instead:
大胆强调我的。对于普通人，EXECUTE请改为：
...
DECLARE
i int;
BEGIN
EXECUTE 'SELECT 1 FROM mytable';
GET DIAGNOSTICS i = ROW_COUNT;
IF i > 0 THEN ...
Orif opportune - in particular with only single-row results - use the INTOclausewith EXECUTEto get a result from the dynamic query directly. I quote the manual here:
或者，如果合适 - 特别是只有单行结果 - 使用INTO子句withEXECUTE直接从动态查询中获取结果。我在这里引用手册：
If a row or variable list is provided, it must exactly match the structure of the query's results (when a record variable is used, it will configure itself to match the result structure automatically). If multiple rows are returned, only the first will be assigned to the
INTOvariable. If no rows are returned, NULL is assigned to theINTOvariable(s).
如果提供了行或变量列表，它必须与查询结果的结构完全匹配（当使用记录变量时，它会自动配置自己以匹配结果结构）。如果返回多行，则只会将第一行分配给
INTO变量。如果没有返回行，则将 NULL 分配给INTO变量。
...
DECLARE
_var1 int; -- init value is NULL unless instructed otherwise
BEGIN
EXECUTE format('SELECT var1 FROM %I WHERE x=y LIMIT 1', 'my_Table')
INTO _var1;
IF _var1 IS NOT NULL THEN ...
回答by Josh Berkus
Matt,
马特，
From the syntax above, you're writing PL/pgSQL, not SQL. On tht assumption, there are two ways to do what you want, but both will require two lines of code:
根据上面的语法，您正在编写 PL/pgSQL，而不是 SQL。根据这个假设，有两种方法可以做你想做的事，但都需要两行代码：
EXECUTE 'SELECT EXISTS (SELECT 1 FROM ' || table_variable || ' );' INTO boolean_var;
IF boolean_var THEN ...
Or:
或者：
EXECUTE 'SELECT 1 FROM ' || table_variable || ' );';
IF FOUND THEN ...
"FOUND" is a special variable which checks if the last query run returned any rows.
“FOUND”是一个特殊变量，用于检查上次查询运行是否返回任何行。
回答by Vijay Mungara
SET @SQLQUERY='SELECT 1 FROM mytable'
EXEC (@SQLQUERY)
If @@RowCount >0 THEN
