Postgresql CHECK IN列表约束的自定义错误消息 [英] Custom error message for Postgresql CHECK IN list constraint
问题描述
我想为Postgres CHECK IN违规创建更具体的错误消息。因此,例如,违反了列上的以下CHECK约束:
I would like to create a more specific error message for Postgres CHECK IN violations. So for example a violation of the following CHECK constraint on a column:
management_zone varchar(15) NOT NULL CHECK (management_zone IN ('Marine', 'Terrestrial') ),
应该返回自定义错误消息,例如: 提示:请检查拼写。仅允许输入的是:'Marine','Terrestrial'。
should return a custom error message such as ie.: "Hint: Check spelling. Only allowed inputs are: 'Marine', 'Terrestrial'.
到目前为止,我所看到的最佳解决方案是使用以下错误消息来解决此问题:检查约束的名称,即
The best solution I have seen so far solves it by using the error message as the name of the check constraint, ie
ADD CONSTRAINT c_managementzone_@Check_spelling._Only_allowed_inputs_are:_'Marine',_'Terrestrial' CHECK (management_zone IN ('Marine', 'Terrestrial') ),
,然后应用可修复的函数
and then applying a function that fixes the error message after the @ sign by replacing the underscores with space to make it more readable. The function is then called using a try and catch statement.
该代码是@符号后的错误消息,方法是使用下划线替换下划线以使其更具可读性,然后使用try和catch语句调用该函数。在我不熟悉的t-sql中,我也不知道足够的PL / pgSQL能够转换和复制它。因此,我想知道是否有人可以建议在postgres中进行类似的操作。使用功能和触发器?
This code however is in t-sql which I am unfamiliar with, and I also don't know enough PL/pgSQL to be able to convert and replicate it. I therefore wonder if anyone can suggest how something similar can be done in postgres, ex. using a function and a trigger?
t-sql代码和解释可从此处获得,我将其复制粘贴到下面:
https://social.technet。 microsoft.com/wiki/contents/articles/29187.t-sql-error-handling-for-check-constraints.aspx
The t-sql code and explanation is available from here and I copy paste it below: https://social.technet.microsoft.com/wiki/contents/articles/29187.t-sql-error-handling-for-check-constraints.aspx
CREATE FUNCTION dbo.ufnGetClearErrorMessage2()
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @Msg NVARCHAR(4000) = ERROR_MESSAGE() ;
DECLARE @ErrNum INT = ERROR_NUMBER() ;
DECLARE @ClearMessage NVARCHAR(4000) ;
IF @ErrNum = 547
BEGIN
/*--how to find @ClearMessage:
SELECT @msg ,
CHARINDEX('@', @msg) ,
RIGHT(@msg, LEN(@msg) - CHARINDEX('@', @msg)) ,
CHARINDEX('"', RIGHT(@msg, LEN(@msg) - CHARINDEX('@', @msg))) ,
LEFT(RIGHT(@msg, LEN(@msg) - CHARINDEX('@', @msg)), CHARINDEX('"', RIGHT(@msg, LEN(@msg) - CHARINDEX('@', @msg))) - 1 ) ,
REPLACE(LEFT(RIGHT(@msg, LEN(@msg) - CHARINDEX('@', @msg)), CHARINDEX('"', RIGHT(@msg, LEN(@msg) - CHARINDEX('@', @msg))) - 1 ), '_', SPACE(1)) + '.'
*/
SELECT @ClearMessage = @Msg + CHAR(13) +
REPLACE(LEFT(RIGHT(@msg, LEN(@msg) - CHARINDEX('@', @msg)), CHARINDEX('"', RIGHT(@msg, LEN(@msg) - CHARINDEX('@', @msg))) - 1 ), '_', SPACE(1)) + '.'
END
ELSE
SET @ClearMessage = @Msg ;
RETURN @ClearMessage ;
END
试一试:
BEGIN TRY
INSERT dbo.Book
( BookId, WritingDate, publishDate )
VALUES ( 1, GETDATE(), GETDATE() + 1 )
END TRY
BEGIN CATCH
DECLARE @Msg NVARCHAR(4000) = dbo.ufnGetClearErrorMessage2();
THROW 60001, @Msg, 1;
END CATCH
任何忠告都值得赞赏。
推荐答案
如果您可以使用略有不同的检查约束,则可以执行以下操作:
If you can live with a slightly different check constraint, you can do the following:
创建一个函数会检查值:
Create a function that checks the values:
create function check_zone(p_input text)
returns boolean
as
$$
declare
l_allowed text[] := array['Marine', 'Terrestrial'];
begin
if p_input = any(l_allowed) then
return true;
end if;
raise 'The only allowed values are: %', array_to_string(l_allowed, ', ');
end;
$$
language plpgsql
immutable;
然后使用该函数代替IN条件:
And then use that function instead of an IN condition:
create table data
(
management_zone text not null,
CONSTRAINT check_zone CHECK (check_zone(management_zone))
);
以下插入
insert into data values ('foo');
将导致:
错误:唯一允许的值是:海洋,陆地
ERROR: The only allowed values are: Marine, Terrestrial
这篇关于Postgresql CHECK IN列表约束的自定义错误消息的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!