从DO状态中的查询中获取结果

本文介绍了从DO状态中的查询中获取结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

如何在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;

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