主页 / dba / 问题 / 52968
Accepted
A-K
Asked: 2013-11-08 11:02:28 +0800 CST

如何在不保持事务打开的情况下保持结果集？

  • 772

以下文档描述了如何查看从函数返回的 refcursor，here，如下所示：

    CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;

这对我有用。但是，如果我想在我的屏幕上保留结果，我必须保持交易开放。当我执行 COMMIT 时，我的结果集被丢弃。当我同时执行 FETCH 和 COMMIT 时，第一个结果集被丢弃。

有没有办法提交事务但保留结果集？PgAdmin 的版本是 1.18.1。

postgresql pgadmin

2 个回答

  1. Best Answer

    当使用DECLARE在 SQL 级别定义游标时，有一个选项WITH HOLD可以使其在提交当前事务后继续存在。引用文档：

    WITH HOLD 指定该游标在创建它的事务成功提交后可以继续使用

    另一方面，由 plpgsql 函数打开的 refcursor 在事务结束时关闭。引用plpgsql 文档：

    所有门户在交易结束时都隐式关闭。因此，refcursor 值仅可用于引用打开的游标，直到事务结束。

    要在 plpgsql 函数中创建可以在其“父”事务之外使用的游标，这只是语法问题。您需要游标的 SQL 实现，而不是 plpgsql 变体。为此，EXECUTE必须使用。

    例如，这里有一个类似于您的函数的框架，但使用的 SQL 级游标比事务更有效：

    CREATE FUNCTION dyncursor(name text) RETURNS VOID AS
$$
DECLARE
  query text;
BEGIN
  query='SELECT 1 as col1, 2 as col2'; -- sample query
  EXECUTE 'DECLARE ' || quote_ident(name) || ' CURSOR WITH HOLD FOR ' || query;
END
$$ language plpgsql;

    演示：

    测试=> 开始；
测试=> 选择 dyncursor('foo');
 动态光标
------------

(1 行)
测试=> 提交；
test=> 从 foo 中获取所有内容；
 col1 | col2
------+------
    1 | 2
(1 行)
测试=> 关闭 foo；
关闭光标
    • 8

  2. pgAdmin只是一个 GUI。与这个问题基本无关。碰巧一个或多个会话绑定到 SQL 编辑器窗口并在窗口关闭时结束。

    如果您想保持交易开放，请暂时不要COMMIT（或ROLLBACK）。

    如果您真的想“保留”结果集，请将其写入table- 可能是 aTEMPORARY或UNLOGGED表（如果您不需要永久保留）。默认情况下，临时表与会话一起存在和死亡。

    SQL 和 PL/pgSQL 对它们的类​​型系统相当严格，一些看起来可能的事情（还没有实现）。但是你想要做的事情可能不用游标就可以解决。如果您需要动态地为函数提供表名，请使用链接到输入参数的多态返回类型：

    CREATE OR REPLACE FUNCTION f_dynamic_select(_tbl anyelement)
 RETURNS SETOF anyelement AS
$func$
BEGIN

RETURN QUERY EXECUTE
'SELECT * FROM ' || pg_typeof(_tbl);

END
$func$ LANGUAGE plpgsql;

    称呼：

    SELECT * FROM  f_dynamic_select(NULL::my_schema.my_table)

    这种形式对 SQL 注入是自动安全的，因为pg_typeof()返回的值在字符串连接期间regtype自动转换为时会自动转义（如果需要） 。text

    就在昨天，我写了一个与多态类型密切相关的答案。它有更多解释和链接：
    将记录变量中的值插入表中

    通常，简单明了SELECT的就可以完成这项工作。必须动态提供表名的情况很少见。

    SELECT * FROM my_schema.my_table
    • 4

相关问题