PostgreSQL common-db-schema (plpgsql-common-library)

plpgSQL schema with many useful functions

Content

Functions to work with JSON:

• common.jsonb_diff_objects
• common.jsonb_extract_text_array

Functions to raise errors and report messages:

• common.raise_notice
• common.raise_info
• common.raise_warning
• common.raise_exception

Functions to work with numbers:

• common.trim_decimal

Functions to check conditions

• common.assert
• common.assert_equal
• common.iif

Functions that work with database data and settings

• common.get_all_roles_of

Cryptography functions

• common.pseudo_encrypt

Requirements

• postgresql
• liquibase

Database preparation and migrations

NOTE: do not use angle brackets in entity names, substitute by the actual names instead!

1. Create a liquibase user under which the migrations will be applied:

   CREATE USER <liquibase_user> CREATEROLE ENCRYPTED PASSWORD 'aPassword';

   NOTE: be sure you have the same <liquibase_user> and aPassword in the liquibase.properties.

   HINT: you can use the same liquibase user as for endpoint project for which the common schema is installing.

2. Connect to endpoint project database and create schema common (owner must be liquibase_user, use AUTHORIZATION option for the CREATE SCHEMA command):

   CREATE SCHEMA common AUTHORIZATION <liquibase_user>;

3. Now you can run liquibase to migrate initial schema (be sure you are in the project's root and the liquibase.properties file is placed here):

   $ liquibase update

   NOTE: you might have to add the classpath: /path/to/the/postgresql-jdbc-driver.jar into the liquibase.properties file unless your liquibase installation contains the postgresql JDBC driver (you also can place the driver jar file manually into the lib directory of the liquibase installation).

   NOTE: in the case of remote database you have to put the correct jdbc connection string into your liquibase.properties like this:

   url: jdbc:postgresql://<host>[:<port>]/<db_name>?ssl=true&prepareThreshold=0

   See more options here: https://jdbc.postgresql.org/documentation/head/connect.html

Further migrations

Update the project content (git pull or any other way you like) and just run:

$ liquibase update