使用Postgres域简化功能输入验证 [英] Using Postgres domains to simplify function input validation

问题描述

从昨天开始，我一直使用Postgres 11.5来查看 CREATE DOMAIN ，并想阐明它们如何/不能帮助函数参数.理想情况下，我想使用一个域来轻松筛选参数输入，但具有有用的错误响应.举例来说，我使用的是一个简单的第一种情况，该域会阻止null和空字符串:

 创建域text_not_empty AS文本非空CHECK(值<>''); 

我将其作为表的字段类型进行了尝试，这很棒.当我们不允许使用空字符串时，这似乎是一种在没有单独规则或触发器的情况下实现约束的简单方法.我希望在函数参数上获得类似的好处.但是对于功能，我们确实需要明确的错误消息，因为调用者可能来自Node或其他环境.

作为一个例子，下面是一个虚拟函数，它需要一个字符串，对输入进行显式检查:

 创建或替换功能api.test_this_function(输入文字)返回int语言plpgsqlAS $功能$开始如果in_text =''THEN使用筹码message ='必须提供输入文字'，detail ='Deets'，hint ='提供搜索字符串'，errcode ='KC123';-自定义代码万一;返回1;结尾;$功能$ 

这很好用，但是我希望一个域可以简化这种情况.据我所知，域不会会有所改善，因为该功能永远不会抓住错误.如果我已阅读文档并正确理解了我的实验，则只能在 BEGIN ... END 块内 RAISE .如果是这样，人们如何建议审核输入?我是否错过了域名的机会?

要充实我基于印象的内容，以下功能确实使用了基于域的检查以及(愚蠢的)自定义检查:

 创建或替换功能api.domain_test(in_text text_not_empty)返回时间戳AS $ BODY $开始如果in_text ='foo'然后使用筹码message ='无效的搜索字符串'，hint ='提供"foo"以外的搜索字符串.'，errcode ='KC123';-自定义代码万一;立即返回();结尾;$ BODY $语言plpgsql; 

因此，它应该在没有参数，空参数，空字符串或字符串'foo'的情况下失败.否则，它应该返回一个时间戳.我尝试了这五种情况，如下所示:

  select * from domain_test();-1:无法达到方法.选择* from domain_test(null);-2:在输入方法之前失败.选择* from domain_test('');-3:在输入方法之前失败.选择* from domain_test('foo');-4:自定义异常失败.选择* from domain_test('a');-5:成功. 

我已经弄清楚了每个语句通过函数实现的程度.该图比代码还清晰，但是有时候我发现尝试制作一个图很有帮助，只是看看我是否掌握了所有内容.

我不是在问一个特定的代码问题，但是如果有人可以确认我的错误被捕获和处理的模型是正确且完整的，那将是一个很大的帮助.一旦我了解了Postgres如何思考"这些东西，对我来说，也将更容易进行推理.

null 和空字符串大小写永远不会进入 BEGIN 块，因此似乎没有使用 RAISE 的方法来自定义 message ， hint 等.是否有一个全局错误处理程序，或者我正在忽略的更广泛的 catch 系统?

关于ORM

wildplasser提供了有关ORM和策略的一些评论，这清楚地表明了我在这里没有解释背景.我不想详细讨论这个问题，但我想我会作一些澄清.

我们不打算使用ORM.似乎添加了另一个模型/抽象层来帮助习惯于其他某种语言的人们.对我来说，在这种情况下，我从中得不到任何好处只是更加复杂.我宁愿直接编写SQL查询，而无需过多的脚手架.这个想法是将查询逻辑/SQL 推送到Postgres 中.那么逻辑就有一个地方.

计划是使我们的PG查询API具有一系列具有定义的输入/输出的功能.我可以使用 pg_proc ， information_schema.parameters 和用于定义参数规则(允许/排除的值，系列或范围)的自定义表格来捕获或存储这些详细信息.这么多的脚手架应该会有所帮助，因为它很容易机械化.利用输入/输出数据，我可以自动生成输入/输出声明，检查代码(我在这里进行的工作)，文档和测试用例.实际的查询主体?我会亲手写的.编写一个智能查询生成器来弄清楚我所有的 joins 等.现在，我将成为……巨大的任务，我会做些废话，而Postgres在这方面做得更好.因此，我可以手写查询主体，将其提供给PG计划器/优化器，并根据需要进行调整.它在一个黑盒子中，因此外部客户不会受到内部修改的伤害.

HTTP API层将以两种语言编写，以可能更多的语言开始使用可能的更多方言.然后，Node等工具可以按照自己的惯用法处理路由和函数调用.我要做的 last 是将查询逻辑实现推向使用不同语言的冗余实现.噩梦是如此之多.为了便于记录，这些函数主要是 RETURN TABLE ，它们是内联或通过 CREATE TYPE 定义的.

解决方案

您的见解是准确的，是错误的

  select domain_test('');错误:域text_not_empty的值违反检查约束"text_not_empty_check" 

在解决函数参数类型的阶段提出

，因此该函数从不执行.如果您的目的是自定义错误消息，则自定义域无法为您提供帮助.

没有全局错误处理程序.唯一的选择是在另一个代码块中调用该函数

 执行$$开始选择domain_test('');当别人那么例外引发异常我的错误消息"；结束$$;错误:我的错误消息上下文:RAISE上的PL/pgSQL函数inline_code_block第5行 

似乎，您的原始方法没有自定义域会更有意义.

Using Postgres 11.5, I've been looking at CREATE DOMAIN since yesterday, and would like to clarify how they can/can't help with function parameters. Ideally, I'd like to use a domain to screen parameter inputs easily, but with a helpful error response. As an example, I'm using a simple first-case, a domain that blocks null and empty strings:

CREATE DOMAIN text_not_empty AS
    text
    NOT NULL
    CHECK (value <> '');

I tried this out as a field type for a table, and it's great. When we don't allow empty strings, this seems like a simple way to implement a constraint without an individual rule or a trigger. I'm hoping to get similar benefits on function parameters. However with functions, we really need clear error messages as the caller may be from Node, or some other environment.

As an example, here's a dummy function that requires a string, using an explicit check on the input:

CREATE OR REPLACE FUNCTION api.test_this_function(in_text text)
 RETURNS int
 LANGUAGE plpgsql
AS $function$

BEGIN

IF in_text = '' THEN
    RAISE EXCEPTION USING
        message = 'Input text must be supplied',
        detail  = 'Deets',
        hint    = 'Supply a search string',
        errcode = 'KC123'; -- Custom code
END IF;

    RETURN 1;

END;
$function$

This works fine, but I'm hoping a domain could simplify such cases. As far as I can tell, a domain won't improve things as the function never gets a chance to grab the error. If I've read the docs and understood my experiments correctly, you can only RAISE within a BEGIN...END block. If so, how do people recommend vetting inputs? And am I missing an opportunity with domains?

To flesh out what I'm basing my impressions on, here's a function that does use a domain-based checked, as well as a (silly) custom check:

CREATE OR REPLACE FUNCTION api.domain_test(in_text text_not_empty)
     RETURNS timestamptz

AS $BODY$

BEGIN

IF in_text = 'foo' THEN
    RAISE EXCEPTION USING
        message = 'Invalid search string',
        hint = 'Supply a search string other than ''foo''.',
        errcode = 'KC123'; -- Custom code
END IF;

    RETURN now();

END;

$BODY$
 LANGUAGE plpgsql;

So, it should fail on no parameter, a null parameter, an empty string, or a string of 'foo'. Otherwise, it should return a timestamp. I tried out these five cases, shown here:

select * from domain_test();      -- 1 : Fails to reach the method.
select * from domain_test(null);  -- 2 : Fails before entering method.
select * from domain_test('');    -- 3 : Fails before entering method.
select * from domain_test('foo'); -- 4 : Fails on custom exception.
select * from domain_test('a');   -- 5 : Succeeds.

I've diagrammed out how far each of these statements make it through the function. The diagram is no clearer than the code, but sometimes I find it helpful to try and make a diagram, just to see if I've got all of the pieces.

I'm not asking a specific code question, but it would be a big help if someone could confirm that my model of how the errors are being caught and handled is correct and complete. Once I've understood how Postgres "thinks" about this stuff, it will be easier for me to reason about it too.

The null and empty string cases never get to the BEGIN block, so there doesn't seem to be a way to use RAISE to customize the message, hint, etc. Is there a global error handler, or a broader catch system that I'm overlooking?

Regarding ORMs

wildplasser offered some comments about ORMs and strategies, which make it clear I didn't explain the background here. I didn't want to bog the question down with more detail, but I figure I'll add in some clarification.

We're not going with an ORM. It seems like that adds an other model/abstraction layer to help people used to some other language. For me, it's just more complexity I don't gain anything from, in this case. I'd prefer to write the queries in straight SQL without a lot of scaffolding. The idea is to push the query logic/SQL into Postgres. Then there's one place for the logic.

The plan is to make our PG query API a series of functions with defined inputs/outputs. I can capture or store those details that up using pg_proc, information_schema.parameters, and a custom table to define parameter rules (allowed/excluded values, series, or ranges.) That much scaffolding should help as it's pretty easy to mechanize. With the input/output data, I can automatically generate input/output declarations, check code (what I'm working on here), documentation, and test cases. The actual query body? I'll write that by hand. Writing a smart query builder that figures out all of my joins etc.? Postgres is better at that now that I'll ever be...huge task, I'd do a crap job. So, I can hand-write the query body, give it to the PG planner/optimizer, and tweak as needed. It's in a black box, so outside clients aren't harmed by internal modifications.

The HTTP API layer will be written in at two languages to start with, with possibly more languages, an likely more dialects to follow. Then the Node, etc. tools can handle the routing and function calls in their own idiom. The last thing I want to do is push out the query logic implementation to redundant implementations in different languages. Nightmare on so many levels. For the record, the functions will mostly RETURN TABLE, defined inline or via CREATE TYPE.

解决方案

Your insights are accurate, the error

select domain_test('');

ERROR:  value for domain text_not_empty violates check constraint "text_not_empty_check"

is raised on the stage of resolving the function argument types hence the function is never executed. If your aim is to customize the error message, the custom domain does not help you.

There is no global error handler. The only option is to call the function inside another code block

do $$
begin
    select domain_test('');
exception when others then
    raise exception 'my error message';
end $$;

ERROR:  my error message
CONTEXT:  PL/pgSQL function inline_code_block line 5 at RAISE

It seems though that your original approach without a custom domain makes more sense.

这篇关于使用Postgres域简化功能输入验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！