MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

PL/pgSQL Date Function

with 2 comments

This post provides an example of using PostgreSQL’s REGEXP_MATCH function, which works very much like the REGEXP_LIKE function in Oracle and a verify_date function that converts a string data type to date data type.

Here’s a basic function to show how to use a generic REGEXP_MATCH function:

1
2
3
4
5
6
7
8
9
10
11

DO
$$
DECLARE
  lv_date_in  DATE := '2022-10-22';
BEGIN
 
  IF (REGEXP_MATCH('2022-10-02','^[0-9]{4,4}-[0-9]{2,2}-[0-9]{2,2}$') IS NOT NULL) THEN
    RAISE NOTICE '[%]', 'Truth';
  END IF;
END;
$$;

The following is a verify_date function, which takes a string with the ‘YYYY-MM-DD’ or ‘YY-MM-DD’ format and returns a BOOLEAN true or false value.

CREATE FUNCTION verify_date
  ( IN pv_date_in  VARCHAR(10)) RETURNS BOOLEAN AS
  $$
  DECLARE
    /* Local return variable. */
    lv_retval  BOOLEAN := FALSE;
  BEGIN
    /* Check for a YYYY-MM-DD or YYYY-MM-DD string. */
    IF REGEXP_MATCH(pv_date_in,'^[0-9]{2,4}-[0-9]{2,2}-[0-9]{2,2}$') IS NOT NULL THEN
 
      /* Case statement checks for 28 or 29, 30, or 31 day month. */
      CASE
        /* Valid 31 day month date value. */
        WHEN (LENGTH(pv_date_in) = 10 AND
              SUBSTRING(pv_date_in,6,2) IN ('01','03','05','07','08','10','12') AND
              TO_NUMBER(SUBSTRING(pv_date_in,9,2),'99') BETWEEN 1 AND 31) OR
             (LENGTH(pv_date_in) = 8 AND
              SUBSTRING(pv_date_in,4,2) IN ('01','03','05','07','08','10','12') AND
              TO_NUMBER(SUBSTRING(pv_date_in,7,2),'99') BETWEEN 1 AND 31) THEN 
          lv_retval := TRUE;
 
        /* Valid 30 day month date value. */
        WHEN (LENGTH(pv_date_in) = 10 AND
              SUBSTRING(pv_date_in,6,2) IN ('04','06','09','11') AND
              TO_NUMBER(SUBSTRING(pv_date_in,9,2),'99') BETWEEN 1 AND 30) OR
             (LENGTH(pv_date_in) = 8 AND
              SUBSTRING(pv_date_in,4,2) IN ('04','06','09','11') AND
              TO_NUMBER(SUBSTRING(pv_date_in,7,2),'99') BETWEEN 1 AND 30) THEN 
          lv_retval := TRUE;
 
        /* Valid 28 or 29 day month date value. */
        WHEN (LENGTH(pv_date_in) = 10 AND SUBSTRING(pv_date_in,6,2) = '02') OR
             (LENGTH(pv_date_in) =  8 AND SUBSTRING(pv_date_in,4,2) = '02') THEN
          /* Verify 4-digit year. */
          IF (LENGTH(pv_date_in) = 10 AND
              MOD(TO_NUMBER(SUBSTRING(pv_date_in,1,4),'99'),4) = 0 AND
              TO_NUMBER(SUBSTRING(pv_date_in,9,2),'99') BETWEEN 1 AND 29) OR
             (LENGTH(pv_date_in) =  8 AND
              MOD(TO_NUMBER(SUBSTRING(TO_CHAR(TO_DATE(pv_date_in,'YYYY-MM-DD'),'YYYY-MM-DD'),1,4),'99'),4) = 0 AND
              TO_NUMBER(SUBSTRING(pv_date_in,7,2),'99') BETWEEN 1 AND 29) THEN
            lv_retval := TRUE;
          ELSE /* Not a leap year. */
            IF (LENGTH(pv_date_in) = 10 AND
                TO_NUMBER(SUBSTRING(pv_date_in,9,2),'99') BETWEEN 1 AND 28) OR
               (LENGTH(pv_date_in) = 8 AND
                TO_NUMBER(SUBSTRING(pv_date_in,7,2),'99') BETWEEN 1 AND 28)THEN
              lv_retval := TRUE;
            END IF;
          END IF;
       NULL;
      END CASE;
    END IF;
 
    /* Return date. */
    RETURN lv_retval;
  END;
$$ LANGUAGE plpgsql;

The following four SQL test cases:

SELECT verify_date('2020-07-04') AS "verify_date('2020-07-04')";
SELECT verify_date('71-05-31')   AS "verify_date('71-05-31')";
SELECT verify_date('2024-02-29') AS "verify_date('2024-02-29')";
SELECT verify_date('2019-04-31') AS "verify_date('2019-04-31')";

Return the following:

 verify_date('2020-07-04')
---------------------------
 t
(1 row)
 
 
 verify_date('71-05-31')
-------------------------
 t
(1 row)
 
 
 verify_date('2024-02-29')
---------------------------
 t
(1 row)
 
 
 verify_date('2019-04-31')
---------------------------
 f
(1 row)

As always, I hope the example code fills somebody’s need.

Written by maclochlainn

May 25th, 2022 at 1:47 am

Posted in Database,DBA,Linux,PL/pgSQL,Postgres,PostgreSQL,PostgreSQL 14,Unix,Windows OS

Tagged with ,

«
»