postgresql - SELECT 使用新的 CREATE PROCEDURE 方法

Question

我正在尝试使用 PostgreSQL 11 中新的 CREATE PROCEDURE 方法存储一个简单的 SELECT 查询。我的想法是将查询存储在数据库中，因为我在我的 API 服务器中可以有一个非常简单的代码，如果我可以在具有强制类型安全的 sql 函数中使用 if/else，也许我不需要开发查询构建器。我有这个最小的例子:

首先我尝试了这个 plpgsql 函数:

CREATE OR REPLACE PROCEDURE test_proc() AS $$
    BEGIN
        SELECT * FROM my_db
        LIMIT 1;
    END;
$$ LANGUAGE plpgsql;

CALL test_proc();

但是抛出这个错误:

错误:查询没有结果数据的目的地
提示:如果您想丢弃 SELECT 的结果，请改用 PERFORM。
上下文:PL/pgSQL 函数 test_proc() 第 3 行在 SQL 语句 SQL 状态:42601

如果我尝试使用RETURN QUERY:

CREATE OR REPLACE PROCEDURE test_proc() AS $$
    BEGIN
        RETURN QUERY;
        SELECT * FROM my_db
        LIMIT 1;
    END;
$$ LANGUAGE plpgsql;

我收到这个错误:

错误:不能在非 SETOF 函数中使用 RETURN QUERY
第 17 行:返回查询； ^
SQL 状态:42804
字符:310

当我尝试使用 RETURNS void AS $$ 或 RETURNS table(...) AS $$ 时，我也遇到了错误。 CREATE PROCEDURE 似乎不支持 RETURNS？那么，是否可以用新的存储过程方法返回一个表呢？或者，如果不是，也许是 JSON？

Answer 1

PostgreSQL(Oracle、DB2)中的过程与 MS-SQL 中的过程不同。它有不同的目标，你不能使用它。通常，尽你所能，忘记所有你从 MSSQL 知道的东西。程序部分确实不同。

只有函数可以返回一些数据——所以你需要使用函数。函数可以返回标量值、复合值或数组值或表。您需要返回表的函数。

CREATE OR REPLACE FUNCTION fx()
RETURNS SETOF mytab AS $$
BEGIN
  RETURN QUERY SELECT * FROM mytab;
END
$$ LANGUAGE plpgsql;

SELECT * FROM fx();

备案:

您可以使用 SQL 函数，它可以有更好(或更差)的性能(取决于上下文)。这些功能有时被称为参数化 View 。

CREATE OR REPLACE FUNCTION fx()
RETURNS SETOF mytab AS $$
  SELECT * FROM mytab;
$$ LANGUAGE sql;

注意:这种技术是反模式的!!!不要这样做。这真的不是个好主意。这些函数不应该包装查询。如果你想隐藏一些复杂的查询，那么使用 View 。不要使用函数。函数是查询优化器的有效屏障，当您使用此反模式时，优化器无法很好地优化以这种形式评估的子查询使用的任何非平凡查询。

使用它 - 如果你想要非常非常慢的应用程序 - 或者如果你的数据模型或查询是原始的。在其他情况下，不要这样做。

不要害怕 SQL - 它是专为手动使用而设计的出色语言。最好将所有数据访问放在一个模块(模型)中，不要在代码中随处访问数据库，但在代码中隐藏 SQL 也是不好的。