PostgreSQL与Oracle:“编译时"检查PL/pgSQL [英] PostgreSQL vs Oracle: "compile-time" checking of PL/pgSQL

问题描述

执行摘要: PostgreSQL很棒，但是由于将PL/pgSQL代码的许多检查推迟到运行时，我们面临许多问题. 在这方面是否有办法使其更像Oracle的PL/SQL ?

Executive summary: PostgreSQL is amazing, but we are facing many issues at work due to the fact that it postpones many checks on PL/pgSQL code until runtime. Is there a way to make it more like Oracle's PL/SQL in this respect?

例如...

尝试在任何Oracle数据库中执行此操作:

Try executing this in any Oracle DB:

create function foo return number as
begin
  select a from dual;
  return a;
end;

Oracle将立即(即在编译时！)响应:

Oracle will immediately (i.e. at compile-time!) respond with:

[Error] ORA-00904: invalid identifier

现在尝试在PostgreSQL中进行语义上等效的事情:

Now try the semantically equivalent thing in PostgreSQL:

CREATE OR REPLACE FUNCTION public.foo ()
    RETURNS integer AS
$body$
    BEGIN
        select a;
        return a;
    END;
$body$
LANGUAGE plpgsql;

很遗憾，您会看到它！ -执行正常...未报告任何错误.

You will see it - unfortunately! - execute fine ... No error is reported.

但是当您尝试调用此函数时(即在运行时)，您将得到:

But when you then try to call this function (i.e. at runtime) you will get:

ERROR:  column "a" does not exist
LINE 1: select a

是否有一种方法可以强制PostgreSQL在函数定义时(而不是在运行时)执行语法分析和检查?我们有大量的遗留PL/SQL代码在工作中，我们正在移植到PostgreSQL-但是缺少编译时检查非常痛苦，这迫使我们进行手动工作-即编写代码进行测试所有功能/过程中的所有代码路径-否则将在Oracle中自动化.

Is there a way to force PostgreSQL to perform syntax analysis and checking at function definition time - not at run-time? We have tons of legacy PL/SQL code at work, which we are porting to PostgreSQL - but the lack of compile-time checks is very painful, forcing us to do manual work - i.e. writing code to test all code paths in all functions/procedures - that was otherwise automated in Oracle.

推荐答案

是的，这是一个已知问题.

Yes, this is a known issue.

PL/pgSQL(除了SQL上的其他函数一样)是PostgreSQL的黑匣子"，因此，除了在运行时以外，实际上不可能检测到错误.

PL/pgSQL (like any other function, except on SQL) is a "black box" for the PostgreSQL, therefore it is not really possible to detect errors except in runtime.

您可以做几件事:

1. 将调用SQL查询的函数包装到BEGIN/COMMIT语句中，以便更好地控制错误；
2. 添加 EXCEPTION块捕获并跟踪错误.但是请注意，这会影响功能性能；
3. 使用由主要贡献者之一的PavelStěhule开发的 plpgsql_check扩展 PL/pgSQL开发.我想最终该扩展将使它成为PostgreSQL的核心，但这需要一些时间(现在我们处于9.4beta3状态)；
4. 您可能还会调查以下相关问题: postgresql语法检查，而无需运行查询

1. wrap your function calling SQL queries into BEGIN / COMMIT statements in order to have better control over errors;
2. add EXCEPTION blocks to your code to catch and track errors. Note, though, that this will affect function performance;
3. use plpgsql_check extension, developed by the Pavel Stěhule, who is one of the main contributors to PL/pgSQL development. I suppose eventually this extension will make it into the core of the PostgreSQL, but it'll take some time (now we're in 9.4beta3 state);
4. You might also look into this related question: postgresql syntax check without running the query

看起来您真的非常需要单元测试框架.

And it really looks like you're in a huge need of a unit testing framework.

这篇关于PostgreSQL与Oracle:“编译时"检查PL/pgSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！