Test zda-li je řetězec číslem bankovního účtu českých bank

Kontrola vychází z modulo 11 testu.

Zde ukázka použití CTE:

CREATE OR REPLACE FUNCTION is_account_nmbr(varchar, varchar)                                             
RETURNS boolean AS $$                                                                                    
WITH w(i,w) AS (VALUES(1,6),(2,3),(3,7),(4,9),(5,10),                                                    
                      (6,5),(7,8),(8,4),(9,2),(10,1))                                                    
SELECT (SELECT sum(substring('0000000000' || $1 FROM length($1) + i for 1)::int * w) % 11 = 0            
           FROM w)                                                                                       
   AND (SELECT sum(substring('0000000000' || $2 FROM length($2) + i for 1)::int * w) % 11 = 0            
           FROM w)                                                                                       
$$ LANGUAGE sql;  

CREATE DOMAIN account_nmbr AS varchar                                                                    
  CONSTRAINT known_format CHECK (value ~ '^[0-9]{2,}-[0-9]{2,}$')                                        
  CONSTRAINT not_empty CHECK (NOT value ~ '^0+-0+$')                                                     
  CONSTRAINT modulo_test CHECK (is_account_nmbr(split_part(value,'-',1),                                 
                                                split_part(value,'-',2)));

Použití:

postgres=# SELECT '204-69000025'::account_nmbr;
ERROR:  value for domain account_nmbr violates check constraint "modulo_test"

postgres=# SELECT '203-69000025'::account_nmbr;
 account_nmbr                                                                                            
--------------                                                                                           
 203-69000025                                                                                            
(1 row)     

SELECT '00-00'::account_nmbr;
ERROR:  value for domain account_nmbr violates check constraint "not_empty"

SELECT 'xxx'::account_nmbr;
ERROR:  value for domain account_nmbr violates check constraint "known_format"