Макроопределения для PostgreSQL

При написании SQL кода для разворачивания БД и написании хранимых процедур порой очень нехватает простейшего макроязыка. Простыми макросами можно заменять сложные похожие выражения, последовательности операторов SQL или даже из множества файлов собирать один скрипт для разворачивания БД.

Несмотря на то, что наиболее развитым макроязыком является m4, многие разрабочики его не знают. Поэтому был выбран более простой, но многим знакомый препроцессор C - cpp из состава GCC.

Все дальнейшие примеры верны для Linux с установленными GCC и Perl. К сожалению, Windows под рукой у меня нет, но проблем с установкой там GCC и Perl возникнуть не должно.

Обходим ограничения C препроцессора

Использовать макросы хочется везде, но препроцессор C не обрабатывает то, что находится между одинарными и двойными скобками. Так же в нем не всегда удобно пользоваться конкатенацией.

Чтобы обойти эти ограничения был написан простейший скрипт на Perl

#!bin\perl
open INFILE, $ARGV[0];
if ( $#ARGV>0  ) {
  open OUTFILE, '>', $ARGV[1];
}
LINE:
    while (<INFILE>) {
      s/(`\|)|(`\[)|(`\])|(`\{)|(`\})/
        if (length($2)>0 | length($3)>0) {chr(0x22)} 
        elsif (length($4)>0 | length($5)>0) {chr(0x27)}
        else {''}/eg;
    } continue {
      if ( $#ARGV>0  ) {
        print OUTFILE;
      } else {
        print;
      }
    }

Я не знаток Perl, так что специалисты наверняка найдут в нем множество огрехов. Но свои функции он выполняет, производя в тексте замены в соответствии со следующей таблицей:

Искомая комбинация символов	Замена
`|	(пустая строка - конкатенация)
`[ или `]	" (двойные кавычки)
`[ или `]	' (одинарная кавычка)

Пример использования

Создадим директорию проекта под любым именем, а в ней несколько директорий:

Директория	Назначение
CRE	DDL файлы таблиц
INC	включаемые файлы препроцессора
PROC	файлы хранимых процедур и функций
SYS	файлы системных скриптов
TYPE	файлы типов данных
VIEW	файлы представлений

А в самой директории создадим следующий файл:

#include "INC/_macros_list.sql"      // глобальные макроопределения
#include "SYS/_before_list.sql"      // системные скрипты выполняемые в начале
#include "TYPE/_types_list.sql"      // список файлов типов данных
#include "CRE/_tables_list.sql"      // список файлов таблиц
#include "VIEW/_views_list.sql"      // список файлов представлений
#include "PROC/_procedures_list.sql" // список файлов процедур и функций
#include "SYS/_after_list.sql"       // системные скрипты выполняемые в конце

Каждый из перечисленный файлов может быть либо пустым, либо содержать строки #include, включающие необходимые файлы уже непосредственно макросов или SQL скриптов.

В качестве примера использования рассмотрим, как можно упростить журналирование исключений. Создадим в директории CRE файл SVC_ExecutionLog.sql

#define OBJECT_NAME SVC_ExecutionLog
CREATE TABLE IF NOT EXISTS SQL_DB_INSTANCE.OBJECT_NAME (
  Id              serial    PRIMARY KEY,
  LogTime         timestamp DEFAULT clock_timestamp(),
  PID             integer   DEFAULT pg_backend_pid(),
  Schema          text      DEFAULT current_schema,
  SourceName      text      DEFAULT current_query(),
  LogLevel        smallint  DEFAULT 0,  // 0 - Notice, 4 - Info, 8 - Warning, 12 - user error, 16 - user severe error, 32 - SQL error
  SessionId       integer   NOT NULL,
  IsStart         boolean   NULL,
  IsFinish        boolean   NULL,
  ParameterId     smallint  NULL,
  SQLState        text      NULL,
  SQLColumn       text      NULL,
  SQLConstraint   text      NULL,
  SQLDataType     text      NULL,
  SQLTable        text      NULL,
  SQLSchema       text      NULL,
  SQLMessage      text      NULL,
  SQLDetail       text      NULL,
  SQLHint         text      NULL,
  SQLContext      text      NULL,
  LogMessageId    integer   NULL,
  LogMessage      text      NULL,
  LogIntData      bigint    NULL,
  LogDateData     timestamp NULL,
  LogIntervalData interval  NULL,
  LogTextData     varchar   NULL,
  LogNumData      float8    NULL,
  LogBooleanData  boolean   NULL,
  LogTableContent text      NULL,
  LogIntervalms   integer   NULL
);

CREATE INDEX IF NOT EXISTS OBJECT_NAME`|_Session_Idx ON SQL_DB_INSTANCE.OBJECT_NAME (SessionId, LogTime);
CREATE UNIQUE INDEX IF NOT EXISTS OBJECT_NAME`|_Session_Parmeters_Idx ON SQL_DB_INSTANCE.OBJECT_NAME (SessionId, ParameterId)
  WHERE ParameterId IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS OBJECT_NAME`|_Session_SessionParmeters_Idx ON SQL_DB_INSTANCE.OBJECT_NAME (ParameterId, SessionId)
  WHERE ParameterId IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS OBJECT_NAME`|_Session_Initialized_Idx ON SQL_DB_INSTANCE.OBJECT_NAME (SessionId) WHERE IsStart;
CREATE UNIQUE INDEX IF NOT EXISTS OBJECT_NAME`|_Session_Finished_Idx ON SQL_DB_INSTANCE.OBJECT_NAME (SessionId) WHERE IsFinish;

ALTER TABLE SQL_DB_INSTANCE.OBJECT_NAME OWNER TO postgres;
GRANT SELECT, INSERT ON SQL_DB_INSTANCE.OBJECT_NAME TO some_user;
#undef OBJECT_NAME

и добавим в файл CRE/_tables_list.sql строку

#include "SVC_ExecutionLog.sql"

А директории INC создадим файл LogSQLException.inc

#define SVC_LOG_SQL_EXCEPTION_AND_RETURN \
  EXCEPTION WHEN OTHERS THEN \
    GET STACKED DIAGNOSTICS proc.sql_state=RETURNED_SQLSTATE, proc.sql_column=COLUMN_NAME, proc.sql_constraint=CONSTRAINT_NAME, \
      proc.sql_datatype=PG_DATATYPE_NAME, proc.sql_table=TABLE_NAME, proc.sql_schema=SCHEMA_NAME, proc.sql_message=MESSAGE_TEXT, \
      proc.sql_detail=PG_EXCEPTION_DETAIL, proc.sql_hint=PG_EXCEPTION_HINT, proc.sql_context=PG_EXCEPTION_CONTEXT; \
    INSERT INTO SQL_DB_INSTANCE.SVC_ExecutionLog (LogLevel, SessionId, LogMessage, \
      SQLState, SQLColumn, SQLConstraint, SQLDataType, SQLTable, SQLSchema, SQLMessage, SQLDetail, SQLHint, SQLContext) \
    VALUES (32, session_id, 'SQL error '||proc.sql_message, \
      proc.sql_state, proc.sql_column, proc.sql_constraint, proc.sql_datatype, proc.sql_table, proc.sql_schema, \
      proc.sql_message, proc.sql_detail, proc.sql_hint, proc.sql_context); \
    COMMIT; \
    RAISE WARNING 'SQL error %', sql_message; \
    RETURN 32; \
  END;

Так же включим его в файл INC/_macros_list.sql строкой #include, как сделали выше.

Теперь где-нибудь в хранимой процедуре, вызываемой каким-то сервисом мы можем использовать следующую конструкцию:

BEGIN
  <какие-то DDL или DML предложения>
SVC_LOG_SQL_EXCEPTION_AND_RETURN

Подразумевается, что при запуске cpp указывается параметр -D SQL_DB_INSTANCE=schema_name, а результат работы cpp обрабатывается приведенным выше Perl скриптом.

Если тема будет интересна, могу привести в следующей статье ряд более сложных и практически интересных примеров использования препроцессора.

Спасибо, если дочитали!