Database functions

Introduction

Relevant’s data warehouse is powered by PostgreSQL. Postgres includes many built-in functions which can be used in SQL queries, as well as the ability for users to define their own functions.

We include a few user-defined functions for all instances of Relevant, described below. These functions are available in the rdm schema. Note: for historical reasons, some of these functions may also be available in the custom schema, but we recommend making use of the rdm versions as they are more reliable.

If you are creating your own functions, we recommend doing so in the custom schema. In the future, we may restrict permissions to create and update functions in rdm.

Functions provided by Relevant

To see the definition of any of these functions, run /df+ function_name in psql, or run the following command from Report Builder or any SQL query tool:

SELECT prosrc FROM pg_proc WHERE proname = 'function_name';

If you’d like to suggest additional functions for to include as part of Relevant’s standard set, let us know at support@relevant.healthcare.

is_date

This function mimics the ISDATE() function that’s available in Microsoft SQL Server. It accepts an input string, and returns true or false depending on whether the input string can be successfully cast to a date. Here’s an example usage from one of our transformers:

CREATE TABLE custom.relevant_advance_care_plannings AS
SELECT ptid          AS patient_id,
       code,
       name,
       mdate :: DATE AS performed_on
FROM ecw.pt_adv_directives
WHERE delflag = 0
  AND mdate IS NOT NULL
  AND is_date(mdate)

ndc_convert

This function is used by Relevant’s medications transformer to transform NDC codes from a variety of NDC formats to a standard 11-digit NDC format. Its working are somewhat complex; it’s best to consult the function definition itself and its usage in Relevant’s medications transformer for details.

ndc_format

This is a helper function used by ndc_convert.

safe_to_date

This function works like the built-in function to_date(), except when it fails, it returns null instead of an error. We like to use this function in combination with COALESCE to clean up dates that may be in several different formats, like the following:

SELECT
  COALESCE(
    safe_to_date(some_column_with_messy_date_data, 'MM/DD/YYYY'),
    safe_to_date(some_column_with_messy_date_data, 'MM/DD/YY'),
    safe_to_date(some_column_with_messy_date_data, 'YYYYMMDD')
  )::DATE
FROM some_table

uds_year and uds_plus_year

These functions return the year that the UDS and UDS+ modules respectively are current reporting.

compute_table_checksum

Returns an MD5 hash string that uniquely identifies query results. The unique value can be used to verify if a set of query results are identical or not without having to parse and compare the results themselves. Query results that only differ in row order will have different hash values. The function accepts a string (table name or aliased subquery) as input, as shown below:

SELECT compute_table_checksum('some_table')
 UNION ALL
SELECT compute_table_checksum('(SELECT * FROM some_table WHERE some_column = TRUE) a');