title: Custom error message for Postgresql CHECK IN list constraint
body: <p>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:</p>
  
  <pre><code>management_zone varchar(15) NOT NULL CHECK (management_zone IN ('Marine', 'Terrestrial') ),
  </code></pre>
  
  <p>should return a custom error message such as ie.: "Hint: Check spelling. Only allowed inputs are: 'Marine', 'Terrestrial'.</p>
  
  <p>The best solution I have seen so far solves it by using the error message as the name of the check constraint, ie</p>
  
  <pre><code>ADD CONSTRAINT c_managementzone_@Check_spelling._Only_allowed_inputs_are:_'Marine',_'Terrestrial' CHECK (management_zone IN ('Marine', 'Terrestrial') ),
  </code></pre>
  
  <p>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. </p>
  
  <p>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?</p>
  
  <p>The t-sql code and explanation is available from here and I copy paste it below:
  <a href="https://social.technet.microsoft.com/wiki/contents/articles/29187.t-sql-error-handling-for-check-constraints.aspx" rel="noreferrer">https://social.technet.microsoft.com/wiki/contents/articles/29187.t-sql-error-handling-for-check-constraints.aspx</a></p>
  
  <pre><code>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
  </code></pre>
  
  <p>Try and catch:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Any advice much apreciated.</p>