postgresql - 为什么使用存储过程而不是直接查询数据库？

Question

我的公司正在制定新政策，因为我的公司将获得一些国际标准的认证。该政策是，不允许 DBA 直接查询数据库，例如:

select * from some_table, update some_table, etc. 

我们必须使用存储过程来执行该查询。

关于我在这里的最后一个问题:Postgres pl/pgsql ERROR: column "column_name" does not exist

我想知道，我们是否必须为每个表或每个条件创建一个存储过程？有什么方法可以更有效地创建存储过程？

谢谢你之前的回答..

抱歉我的英语不好.. :D

Answer 1

使用存储过程的一些原因是:

• 他们可能已经进行了一些测试以确保他们这样做不允许破坏业务规则，以及一些优化为了表现。
• 他们确保结果的一致性。每次你被要求执行任务 X，您运行与任务 X 关联的存储过程。如果您编写查询，则可能不会每次都以相同的方式编写；也许有一天你会忘记一些愚蠢的事情，比如强制文本到比较之前的相同情况，但遗漏了一些东西。
• 他们一开始写的时间比查询要长一些，但是运行该存储过程比编写查询花费的时间更少再次。运行它足够多次，它变得更有效率编写存储过程。
• 他们减少或消除了了解以下关系的需要基础表。
• 您可以授予执行存储过程的权限(使用security definer)，但拒绝对基础表的权限。
• 可以为程序员(如果您将 DBA 和程序员分开)提供API，这就是他们需要知道的全部。只要你保持API 在更改数据库时，您可以进行任何必要的更改在不破坏软件的情况下了解底层关系；的确，您甚至不需要知道他们用您的 API 做了什么。

您最终可能会为每个原本会执行的查询创建一个存储过程。

与仅编写查询相比，我不确定您为什么认为这样做效率低下或特别耗时。如果您所做的只是将查询放在存储过程中，那么额外的工作应该是最少的。

CREATE OR REPLACE FUNCTION aSchema.aProcedure (
    IN  var1        text,
    IN  var2        text,
OUT col1        text,
OUT col2        text
)
    RETURNS setof record
    LANGUAGE plpgsql
    VOLATILE
    CALLED ON NULL INPUT
    SECURITY DEFINER
    SET search_path = aSchema, pg_temp
    AS $body$
        BEGIN
            RETURN QUERY /*the query you would have written anyway*/;
        END;
    $body$;
GRANT EXECUTE ON FUNCTION aSchema.aProcedure(text, text) TO public;

正如您在上一个问题中使用的那样，通过将列/表作为参数传递并使用 EXECUTE，该函数可以更加动态(尽管这增加了执行该函数的人需要了解该函数如何工作的程度，所以我尽量避免它)。

如果“效率较低”来自于函数中包含的额外逻辑，那么与仅使用查询的比较是不公平的，因为函数正在做额外的工作。