从DO状态中的查询中获取结果 [英] Get result from query in DO satement

问题描述

如何在plpgsql的IF条件下运行SQL语句?我不想创建或替换函数.这是我尝试过的:

How to run SQL statement within an IF condition in plpgsql? I don't want to create or replace a function. This is what I tried:

DO LANGUAGE plpgsql $$
BEGIN
IF 'Khosla' = 'Khosla' THEN
SELECT * FROM test_log limit 10;
ELSE
RAISE NOTICE 'not worked';
END IF;
END;
$$;

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "inline_code_block" line 3 at SQL statement

我也尝试过此操作，但无法获取数据:

I also tried this but was unable to get data:

DO LANGUAGE plpgsql $$
BEGIN
if 'a' = 'a' THEN
PERFORM * FROM test_log limit 10;
else
raise notice 'not worked';
end if;
end;
$$;

Output: Query returned successfully with no result in 13 ms.

正确阅读文档后，我知道PERFORM会执行SQL并丢弃结果集.

After reading document properly I came to know that PERFORM executes SQL and discards result set.

您可以帮助运行SQL语句并获取结果集吗?

Can you help in running the SQL Statement and getting the result set?

推荐答案

您不能从DO语句返回值.而是创建一个plpgsql函数.

You cannot return values from a DO statement. Create a plpgsql function instead.

有几种方法可以使用RETURNING子句或OUT参数定义返回类型.阅读有关 CREATE FUNCTION 的手册.

There are several ways to define the return type with the RETURNING clause or with OUT parameters. Read the manual about CREATE FUNCTION.

有几种方法可以从函数中返回值.阅读从函数返回.

There are several ways to return values from the function. Read the chapter Returning From a Function in the manual.

尤其是，由于您试图从表中返回整行，因此可以将表的注册类型用于函数声明:

In particular, since you are trying to return whole rows from a table, you can use the registered type of the table for the function declaration:

CREATE FUNCTION foo ()
  RETURNING SETOF test_log 
$func$
BEGIN

IF 'a' = 'a' THEN
  RETURN QUERY
  SELECT * FROM test_log LIMIT 10;
ELSE
   RAISE WARNING $$Didn't work!$$;
END IF;

END
$func$ LANGUAGE plpgsql;

致电:

SELECT * FROM foo ();

或尝试在此处搜索SO.我发布了许多相关的代码示例

Or try a search here on SO. I posted many related code examples.

如果您不能使用函数，则DO语句唯一可行的中途解决方法是使用临时表:

If you cannot use a function, the only halfway sensible workaround with a DO statement is to use a temporary table:

CREATE TEMP TABLE tbl_tmp AS
SELECT * FROM test_log LIMIT 0;

$do$
BEGIN

IF 'a' = 'a' THEN
  INSERT INTO tbl_tmp
  SELECT * FROM test_log LIMIT 10;
ELSE
   RAISE WARNING $$Didn't work!$$;
END IF;

END
$do$ LANGUAGE plpgsql;

SELECT * FROM tbl_tmp;

临时表会在会话结束时自动删除.

这篇关于从DO状态中的查询中获取结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！