动态SQL(EXECUTE)作为IF语句的条件 [英] Dynamic SQL (EXECUTE) as condition for IF statement

问题描述

我想执行一条动态SQL语句，其返回值是IF语句的条件:

I want to execute a dynamic SQL statement, with its returned value being the conditional for an IF statement:

IF EXECUTE 'EXISTS (SELECT 1 FROM mytable)' THEN

这会生成错误ERROR: type "execute" does not exist.

是否可以执行此操作，还是有必要在IF语句之前将SQL执行到变量中，然后将变量作为条件进行检查?

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 EXECUTE 'EXISTS (SELECT 1 FROM mytable)' THEN ...

IF EXECUTE 'EXISTS (SELECT 1 FROM mytable)' THEN ...

您可以简化为:

IF EXISTS (SELECT 1 FROM mytable) THEN ...

但是您的示例可能只是被简化了.对于使用 EXECUTE 执行的 动态SQL ，

But your example is probably just simplified. For dynamic SQL executed with EXECUTE, read the manual here. You can check for FOUND after RETURN QUERY EXECUTE:

IF FOUND THEN ...

但是:

尤其要注意，EXECUTE会更改 GET DIAGNOSTICS 的输出，但不会更改FOUND.

强调粗体.对于普通的EXECUTE，请执行以下操作:

Bold emphasis mine. For a plain EXECUTE do this instead:

...
DECLARE
   i int;
BEGIN
   EXECUTE 'SELECT 1 FROM mytable';

   GET DIAGNOSTICS i = ROW_COUNT;

   IF i > 0 THEN ...

或 (如果适当)-特别是仅具有单行结果的情况-请使用此处引用了手册:

Or if opportune - in particular with only single-row results - use the INTO clause with EXECUTE to get a result from the dynamic query directly. I quote the manual here:

如果提供了行或变量列表，则它必须与 查询结果的结构(使用记录变量时，它会 会将其配置为自动匹配结果结构).如果 返回多行，只有第一行将分配给 INTO变量.如果未返回任何行，则将INTO分配为NULL. 变量.

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 INTO variable. If no rows are returned, NULL is assigned to the INTO variable(s).

...
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 ...

这篇关于动态SQL(EXECUTE)作为IF语句的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！