Ytukyg

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') ),

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.

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?

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

Try and catch:

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

Any advice much apreciated.

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;

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))

);

The following INSERT

insert into data values ('foo');

will result in:

ERROR: The only allowed values are: Marine, Terrestrial