postgresql - 动态 SQL (EXECUTE) 作为 IF 语句的条件

Question

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

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

这会生成错误错误:类型“execute”不存在。

这样可以吗，还是需要执行if语句之前的sql到一个变量中，然后把这个变量作为条件来检查？

Answer 1

这种构造是不可能的:

IF EXECUTE 'EXISTS (SELECT 1 FROM mytable)' 然后 ...

你可以简化为:

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

但是您的示例可能已简化。对于使用 EXECUTE 执行的动态 SQL，read the manual here .您可以在执行任何 DML 命令后立即检查特殊变量 FOUND 以查看此处是否有任何行受到影响:

IF FOUND THEN ...

但是:

Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.

大胆强调我的。对于普通的 EXECUTE，请执行以下操作:

...
DECLARE
   i int;
BEGIN
   EXECUTE 'SELECT 1 FROM mytable';  -- something dynamic here

   GET DIAGNOSTICS i = ROW_COUNT;

   IF i > 0 THEN ...

或者 如果时机合适 - 特别是只有单行结果 - 使用 INTO clause使用 EXECUTE 直接从动态查询中获取结果。我引用手册 here :

If a row or variable list is provided, it must exactly match thestructure of the query's results (when a record variable is used, itwill configure itself to match the result structure automatically). Ifmultiple rows are returned, only the first will be assigned to theINTO variable. If no rows are returned, NULL is assigned to the INTOvariable(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 ...