![](https://habrastorage.org/getpro/habr/upload_files/0f0/2a3/7da/0f02a37dae365462dbb191b1c6b91bcf.jpg)
Приветствую, уважаемые хабражители!
Я занимаюсь переводом кода из MS SQL Server в PostgreSQL с начала 2019 года и сегодня продолжу сравнение этих СУБД.
В прошлой публикации мы рассматривали отличия в быстродействии MS SQL Server и PostgreSQL для «1C».
В Ozon есть решения и на MS SQL Server, и на PostgreSQL: первая используется в логистике и системах внутренних сервисов, вторая — в mission critical-подсистемах, от которых напрямую зависит бизнес компании (склад, корзина, оплата картами, платежи, информация о товарах на сайте и др.).
Периодически появляются задачи перевода решений из огромных монолитных баз из MS SQL Server в PostgreSQL. Поэтому давайте сравним основные конструкции синтаксиса этих СУБД для правильного чтения кода, а также для того, чтобы быстро изменять код из MS SQL Server для PostgreSQL и наоборот.
Начнём с сопоставления типов.
Сопоставление типов
![](https://habrastorage.org/getpro/habr/upload_files/c95/c76/4f2/c95c764f2040abfd43faff4681be6350.jpg)
MS SQL | PostgreSQL |
BIGINT | BIGINT, INT8 |
BINARY(n) | BYTEA |
VARBINARY(n) | BYTEA |
VARBINARY(max) | BYTEA |
ROWVERSION | BYTEA |
IMAGE | BYTEA |
FIELDHIERARCHYID | BYTEA, LTREE (расширение) |
BIT | BOOLEAN, BOOL |
CHAR(n), n<=8000 | TEXT |
NCHAR(n), n<=4000 | TEXT |
VARCHAR(n), n<=8000 | TEXT |
NVARCHAR(n), n<=4000 | TEXT |
VARCHAR(max) | TEXT |
NVARCHAR(max) | TEXT |
TEXT | TEXT |
NTEXT | TEXT |
FLOAT(n) | DOUBLE PRECISION, FLOAT8 |
SMALLMONEY | MONEY |
MONEY | MONEY |
INT, INTEGER | INT, INTEGER, INT4 |
SMALLINT | SMALLINT, INT2 |
NUMERIC(n,m) | NUMERIC(n,m) |
DEC(n,m), DECIMAL(n,m) | DEC(n,m), DECIMAL(n,m) |
TINYINT | SMALLINT, INT2 |
REAL | REAL, FLOAT4 |
UNIQUEIDENTIFIER | CHAR(16), UUID |
DATE | DATE |
TIME(n) | TIME(n) |
DATETIME | TIMESTAMP(3) |
DATETIME2(n) | TIMESTAMP(m) |
DATETIMEOFFSET(n) | TIMESTAMP(n) WITH TIME ZONE, TIMESTAMPTZ |
SMALLDATETIME | TIMESTAMP(0) |
XML | XML |
Примечание. Типы CHAR и VARCHAR лучше не использовать. Причины подробно описаны здесь.
Более подробно о типах данных:
Теперь перейдём к сопоставлению синтаксиса MS SQL Server и PostgreSQL.
Сопоставление синтаксиса MS SQL Server и PostgreSQL
![](https://habrastorage.org/getpro/habr/upload_files/720/ada/ab6/720adaab69811b26cf288f482f308f62.jpg)
I. Регистрозависимое обращение к схемам, таблицам (представлениям) и их полям и другим объектам базы данных
В MS SQL Server при обращениях к объектам можно использовать квадратные скобки (они обязательны, только если в названии объекта или его поля присутствуют недопустимые символы):
[schema]
[table]
[view]
[object]
[table].[field]
[view].[field]
[schema].[table]
[schema].[view]
[schema].[object]
[schema].[table].[field]
[schema].[view].[field]
В PostgreSQL для этого используются двойные кавычки (они обязательны, только если в названии объекта присутствуют заглавные буквы или есть недопустимые символы в названии объекта или его поля):
"schema"
"table"
"view"
"table"."field"
"view"."field"
"schema"."table"
"schema"."view"
"schema"."table"."field"
"schema"."view"."field"
II. Выборка заданных N данных
III. Постраничная загрузка данных (скользящее окно)
Задача: извлечь 100 строк начиная с 202-й строки включительно по возрастанию даты рождения:
в PostgreSQL: | |
SELECT * | select * |
Примечание. Вместо row можно использовать rows в любом месте запроса, а вместо next можно использовать first в обеих СУБД. |
IV. Выборка первого непустого значения
V. Тернарный оператор IIF
VI. Создание псевдонима
MS SQL Server | PostgreSQL |
AS — рекомендуется = — не рекомендуется | as |
VII. Выражения CASE
VIII. Работа с переменными
Объявление переменной
Присвоение переменной значения
Вывод значения на консоль
MS SQL Server | PostgreSQL |
print 'строка'; print @переменная; RAISERROR(@переменная, 1, 1) WITH NOWAIT; | RAISE NOTICE '%', 'строка'; RAISE NOTICE '%', <переменная>; |
IX. Управление выполнением кода
Выполнение скрипта
В MS SQL Server:
declare @_query int;
set @_query=777;
set @query=1+8;
RAISERROR(@_query, 1, 1) WITH NOWAIT; --PRINT @_query;
В PostgreSQL:
Шаблон:
do $$
<объявление переменных>
begin
<код>
end;
$$;
Пример (вывод информации):
do $$
declare _query int;
begin
_query:=777;
_query:=1+8;
RAISE NOTICE '%', _query;
end;
$$;
Пример (передача значения клиенту):
do $$
declare _query int;
begin
_query:=777;
_query:=1+8;
PERFORM
set_config('my._query',
_query::text, FALSE);
end;
$$;
SELECT current_setting
('my._query');
Для PostgreSQL:
В DBeaver (бобре) нужно нажать CTRL+SHIFT+O при отсутствии окна вывода, а в pgAdmin вывод происходит автоматически.
В psql и так всё работает.
Цикл WHILE
MS SQL Server | PostgreSQL |
WHILE <условие_при_котором_цикл_работает> BEGIN ... END | while <условие_при_котором_цикл_работает> loop ... end loop |
Логическое ветвление
MS SQL Server | PostgreSQL |
IF-BEGIN-END IF-BEGIN-END-ELSE-BEGIN-END | if-then-else-end if; if-then-elseif-then-else-end if; |
Более подробно про управление выполнением кода:
X. Функции для работы со строками
Определение длины строки (количество символов в строке)
MS SQL Server | PostgreSQL |
LEN (<строка>) | length (<строка>) |
Примечание. В MS SQL Server исключаются конечные пробелы. Если нужно учитывать и их, то необходимо воспользоваться функцией DATALENGTH (<строка>), которая возвращает суммарное количество байтов в символах строки. |
Возвращение символа по его коду:
MS SQL Server | PostgreSQL |
char(n) | chr(n) |
Конкатенация строк
MS SQL Server | PostgreSQL |
+ | || |
Нахождение позиции вхождения подстроки
В MS SQL Server: | В PostgreSQL: |
CHARINDEX(<что_ищем>, <где_ищем>,<с_какой_позиции_ищем_начиная_с_1>) | strpos(<где_ищем>, <что_ищем>) strpos(substring(<где_ищем>, <с_какой_позиции_ищем_начиная_с_1>, length(<где_ищем>)- <с_какой_позиции_ищем_начиная_с_1>+1), <что_ищем>) |
Примечание. Точного соответствия не будет, если производить поиск не с начала строки. |
Регистронезависимое сравнение и поиск данных
В MS SQL Server: | В PostgreSQL: |
1. LIKE 2. a = b 3. <> 4. a in (b1, ...) | 1. ilike 2. lower(a) = lower(b) или upper(a)=upper(b) 3. lower(a) <> lower(b) или upper(a)<>upper(b) 4. lower(a) in (lower(b1), ...) или upper(a) in (upper(b1), ...) |
Примечание. В PostgreSQL рекомендуется произвести оптимизацию через создание функционального индекса:
create [concurrently] index idx_lower_<field>
on <schema>.<table> (lower(<field>));
--После создания concurrently-индекса,
--его необходимо проверить на наличие битых индексов следующим запросом:
SELECT indexrelid::regclass FROM pg_index where not indisvalid;
--Далее для обновления статистики по нужной таблице
--необходимо выполнить команду ANALYZE:
ANALYZE <table>;
Более подробно про команду ANALYZE.
Слияние строк по запросу в одну строку по заданному разделителю
В MS SQL Server можно использовать функцию STUFF следующим образом:
STUFF(( SELECT DISTINCT
', ' + CONVERT(varchar, tbl.<поле>)
FROM
<схема>.<таблица> tbl
[WHERE
<условия>]
FOR XML PATH(''))
, 1
, 1
, '') AS STUFF_tbl;
Также начиная с версии 2017 доступна функция STRING_AGG.
В PostgreSQL для этого можно использовать функцию string_agg таким образом:
string_agg((SELECT
distinct ', ' ||
cast(tbl.<поле> as VARCHAR) FROM
<схема>.<таблица> tbl,
[WHERE
<условия>]
), 1, 1,
'') AS
string_agg_field;
Более подробно про функции для работы со строками:
XI. Функции для работы с датой и временем
Получение текущей даты и времени (локальное время)
MS SQL Server | PostgreSQL |
GetDate() SysDateTime() current_timestamp | clock_timestamp now() |
Получение текущей даты
MS SQL Server | PostgreSQL |
CAST(GetDate() as DATE) | current_date |
Пример преобразования формата даты и времени из строки public_date:
В MS SQL Server:
FORMAT(public_date, 'dd.MM.yyyy HH:mm:ss', 'ru-RU') — предпочтительный способ
convert(varchar(32),convert(datetime,public_date,104),120)
В PostgreSQL:
to_char(to_timestamp(public_date, 'dd.MM.yyyy hh24.mi'), 'yyyy-mm-dd hh24:mi:ss')
Приращение даты/времени
В MS SQL Server: | В PostgreSQL: |
DateAdd(datepart, count, dt); | dt + (count * interval '1 datepart'); |
Более подробно про функции для работы с датой и временем: 2. PostgreSQL |
XII. Получение количества строк, затронутых при выполнении последней команды
MS SQL Server | PostgreSQL |
get diagnostics <переменная>:=row_count; |
XIII. Выполнение динамического SQL-кода
MS SQL Server | PostgreSQL |
execute sp_executesql @sql; | execute _sql; |
XIV. Проверка и приведение типов
Проверка строки на то, что она является числом
В MS SQL Server:
встроенная функция isnumeric(val)
В PostgreSQL:
CREATE OR REPLACE
FUNCTION
dbo.isnumeric(_input varchar(255) DEFAULT NULL::varchar(255))
RETURNS bit
LANGUAGE plpgsql
AS $function$
/*
Проверяет,
является ли входная строка
числом
*/
declare _result bit;
begin
begin
perform
_input::numeric;
_result:=1::bit;
exception
when others THEN
_result:=0::bit;
end;
return
_result;
end;
$function$
;
Безопасное приведение типа
В MS SQL Server:
try_cast(val as <type>)
Примечание. try_cast в MS SQL Server возвращает NULL, если значение невозможно привести к заданному типу, в других случаях — работает как оператор CAST.
В PostgreSQL есть два способа:
1) через обработку ошибок:
declare _result оператор CAST <type>;
...
BEGIN
_result :=
cast(val as <type>);
exception
when others then
_result :=null;
end;
2) через реализацию функции:
CREATE OR REPLACE
FUNCTION
dbo.try_cast(value character varying, typename CHARACTER varying)
returns text
LANGUAGE plpgsql
AS $function$
declare _sql_command text;
DECLARE _result text;
begin
_result=value;
_sql_command :=
'select
cast('||''''||
value||''''||' as '||
typename||');';
BEGIN
execute _sql_command;
exception
when others then
_result :=null;
end;
return
_result;
end;
$function$
;
Функция в итоге не возвращает преобразованное в заданный тип значение.
Функция на вход принимает текст и возвращает текст.
Если значение невозможно привести к заданному типу, то возвращается NULL.
Пример использования (чтобы было как в MS SQL Server):
cast(dbo.try_cast(val::text, '<type>') as <type>)
XV. DML-команды
Обновление данных
Пример в MS SQL Server:
Обновление поля Name в таблице Production.ScrapReason для тех строк, для которых есть соответствующие записи в таблице Production.WorkOrder по равенству ScrapReasonID и у которых значение ScrappedQty больше 300:
UPDATE
sr
SET
sr.Name = 'Name'
OUTPUT
deleted.*
, inserted.*
FROM Production.ScrapReas sr
JOIN Production.WorkOrder wo ON (sr.ScrapReasonID = wo.ScrapReasonID)
AND (wo.ScrappedQty > 300);
Ключевое слово OUTPUT позволяет получить данные об обновлении.
Пример в PostgreSQL:
Обновление поля Name в таблице production.scrapreason для тех строк, для которых есть соответствующие записи в таблице production.workorder по равенству scrapreasonid и у которых значение scrappedqty больше 300:
update
production.scrapreason as sr
set sr.Name = 'Name'
from production.workorder as wo
where (sr.scrapreasoid = wo.scrapreasonoid)
and (wo.scrappedqty > 300)
returning *;
Ключевое слово returning позволяет получить данные об обновлении.
Более подробно о команде UPDATE:
Удаление данных
Пример в MS SQL Server:
Удаление из таблицы Sales.SalesPersonQuotaHistory тех записей, для которых есть соответствующие записи в таблице Sales.SalesPerson по равенству BusinessEntityID и у которых значение SalesYTD больше 2500000.00:
DELETE FROM
spqh
OUTPUT deleted.*
FROM Sales.SalesPersonQuotaHistory spqh
INNER JOIN Sales.SalesPerson sp ON (spqh.BusinessEntityID = sp.BusinessEntityID)
WHERE
(sp.SalesYTD > 2500000.00);
Ключевое слово OUTPUT позволяет получить данные об удалении.
Пример в PostgreSQL:
Удаление из таблицы sales.salespersonquotahistory тех записей, для которых есть соответствующие записи в таблице sales.salesperson по равенству businessentitid и у которых значение salesytd больше 2500000.00:
delete from
sales.salespersonquotahistory AS spqh
using
sales.salesperson AS sp
where
(spqh.businessentityid = sp.businessentitid)
and (sp.salesytd > 2500000.00)
returning *;
Ключевое слово returning позволяет получить данные об удалении.
Более подробно о команде DELETE:
Получение изменённых записей
В MS SQL Server: | В PostgreSQL: |
insert/update/delete таблица | insert/update/delete таблица |
В update есть доступ только к inserted. Примечание. В PostgreSQL не нужна промежуточная таблица для получения изменённых записей. Более подробно: |
Удаление дубликатов (дублирующих строк):
В MS SQL Server:
with dbl_in_stage as (
select
row_number() over
(partition by
<field_1>, ...,
<field_N> order by
1) as rn
from
<схема>.<таблица>
as stg
)
delete from
dbl_in_stage where
rn > 1;
В PostgreSQL:
with x as (
select a, ctid, row_number() over(partition by a order by ctid) rn
from t
)
delete from t
using x
where t.a = x.a
and t.ctid = x.ctid
and x.rn > 1;
или более сложный вариант:
delete from <схема>.<таблица>
where ctid=any(
array(select unnest(ctids[2:])
from (
select array_agg(
ctid order by string_to_array(
regexp_replace(ctid::text, E'\\(|\\)','','g'),',')::bigint[]) ctids
FROM <схема>.<таблица> as T group by T::text) as T)::tid[]);
Примечание. Оптимальный вариант — внести в таблицу уникальный ключ, так как работа с метаданными увеличивает нагрузку на систему.
При наличии уникального ключа удалять дубликаты в PostgreSQL можно следующим образом:
delete from <схема>.<таблица> where <уникальный ключ> in
(select <уникальный ключ>
from (
select *, row_number() over (partition by <field_1>, ..., <field_N> order by 1) as rn
from <схема>.<таблица>
) as tbl where rn > 1);
XVI. DDL-команды для работы с таблицами
Удаление таблицы с предварительной проверкой
В MS SQL Server:
Для основной таблицы:
DROP TABLE IF EXISTS <schema>.<table>;
Для локальной временной таблицы:
IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#<table>%')
BEGIN
DROP TABLE #<table>;
END;
Для глобальной временной таблицы:
IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '##<table>%')
BEGIN
DROP TABLE ##<table>;
END;
Здесь:
#<table> — локальная временная таблица, которая видна только в текущей сессии
##<table> — глобальная временная таблица, которая видна всем пока она существует
Все временные таблицы живут, либо пока активна сессия, во время которой они были созданы, либо пока их явно не удалят.
В PostgreSQL:
Для основной таблицы:
drop table if exists
<schema>.<table>;
Для временной таблицы:
drop table if exists <table>;
Более детально про удаление таблиц:
Создание таблицы через выборку
В MS SQL Server:
Для основной таблицы:
select ...
into <table>
from …
Для временной таблицы:
select ...
into #<table>
from …
В PostgreSQL:
Для основной таблицы:
create table <table> as
select ...
Для временной таблицы:
create temp table <table> as
select …
Более детально про создание таблиц через выборку:
Создание/изменение и удаление значения по умолчанию для колонки таблицы
В MS SQL Server:
Добавление:
ALTER TABLE
<схема>.<таблица>
ADD CONSTRAINT
<название_правила>
DEFAULT
<значение_по_умолчанию> FOR <поле>;
Выборка всех значений по умолчанию:
SELECT SCHEMA_NAME(t.[schema_id]) AS sch
, t.name AS tbl
, col.name AS colname
, dc.definition AS def
FROM sys.default_constraints dc
INNER JOIN sys.columns col ON dc.parent_object_id = col.[object_id]
INNER JOIN sys.tables t ON t.[object_id] = col.[object_id];
Удаление:
DROP DEFAULT IF EXISTS <название_правила>;
Изменение происходит через удаление и добавление.
В PostgreSQL:
Создание и изменение:
alter table
<схема>.<таблица>
alter column <поле>
set default
<значение_по_умолчанию>;
Выборка всех значений по умолчанию:
select
col.table_schema,
col.table_name,
col.column_name,
col.column_default
from
information_schema.columns as col;
Удаление:
alter table <схема>.<таблица>
alter column <поле> drop default;
Изменение типа колонки таблицы
В MS SQL Server: | В PostgreSQL: |
ALTER TABLE | alter table |
Примечание. Если у изменяемого столбца есть какие-либо констрейнты или значения по умолчанию, то сначала нужно их сохранить, а затем удалить. Вернуть их можно будет после изменения типа. |
Перенос автоинкрементных полей
В MS SQL Server делаем запрос вида:
SELECT
'do $$
declare
start_with_val
bigint;
declare
sql_statement
varchar;
begin
start_with_val :=
coalesce((select
max(' + c.[name] + ') from
' + s.[name] + '.' + o.[name] + '),0)+1;
sql_statement :=
''alter table
' + s.[name] + '.' + o.[name] + ' alter ' + c.[name] + '
add generated by
default as identity
(start with ''
||
cast(start_with_val
as varchar)||'');'';
execute
sql_statement;
end;
$$;' AS plsql_statement
--select distinct s.name
FROM
sys.all_columns c
INNER JOIN sys.all_objects o ON o.[object_id] = c.[object_id]
INNER JOIN sys.schemas s ON s.[schema_id] = o.[schema_id]
WHERE
is_identity <> 0
AND SCHEMA_NAME(o.[schema_id]) <> 'sys'
AND o.[type] = 'U';
Пример:
do $$
declare start_with_val bigint;
declare sql_statement varchar;
begin
start_with_val := coalesce((select max(ID) from dbo.ExchangeQueue),0)+1;
sql_statement := 'alter table dbo.ExchangeQueue alter ID add generated by default as identity (start with ' || cast(start_with_val as varchar)||');';
EXECUTE sql_statement;
end;
$$;
Полученные скрипты применяем на стороне PostgreSQL.
Создание автоинкрементных полей
В MS SQL Server:
ALTER TABLE [схема].[таблица]
ADD
<IDENTITY-поле> bigint IDENTITY(1, 1) NOT NULL;
В PostgreSQL:
do $$
DECLARE start_with_val bigint;
DECLARE sql_statement varchar;
BEGIN start_with_val := coalesce((select max(<IDENTITY-поле>) from <схема>.<таблица>),0)+1;
sql_statement := 'alter table <схема>.<таблица> alter <IDENTITY-поле> add generated by default as identity (start with ' || cast(start_with_val as varchar)||');';
EXECUTE sql_statement;
END;
$$;
Более детально про создание таблиц: | |
Более детально про изменение таблиц: | |
XVII. Создание и изменение представления
В MS SQL Server: | В PostgreSQL: |
CREATE OR ALTER VIEW [схема].[название_представления] AS | create or replace view <схема>.<название_представления> as |
Примечание. В PostgreSQL лучше сначала удалять представление, а потом заново его создавать, если набор полей меняется или меняются названия выходных полей, иначе можно получить ошибку при обращении к изменённому представлению. | |
Более подробно про создание и изменение представлений: | |
XVIII. Построчная обработка строк в наборе
В MS SQL Server:
--объявление переменных @field_1, ...@field_N
DECLARE <курсор>
CURSOR LOCAL FOR
<SELECT>;
OPEN <курсор>;
FETCH NEXT FROM
<курсор> INTO
@field_1, ...@field_N;
WHILE
(@@FETCH_STATUS = 0)
BEGIN
--оперируем значениями переменных @field_1, ...@field_N
...
FETCH NEXT
FROM <курсор> INTO @field_1, ...@field_N;
END
CLOSE <курсор>;
DEALLOCATE <курсор>;
В PostgreSQL:
do $$
declare _val
record;
begin
drop table if
exists _tmp_tbl;
create temp
table _tmp_tbl as
<select>
for _val in
(select field_1, ..., field_n from_tmp_tbl)
loop
--можно обратиться к любому выбранному ранее полю через _val.<поле>. Например, _val.<field_1>
end loop;
end
$$
XIX. Системные информационные функции безопасности
Текущий пользователь
В MS SQL Server используется функция CURRENT_USER().
В PostgreSQL:
session_user — под каким пользователем открыта сессия
current_user (или просто user) — под каким контекстом (ролью) идёт выполнение (session_user переключается для выполнения — здесь важно, под каким правом делается переключение)
Получение имени экземпляра и IP-адреса сервера СУБД
В MS SQL Server:
Получить информацию об IP-адресе сервера СУБД:
SELECT
CONNECTIONPROPERTY('
net_transport') AS net_transport
, CONNECTIONPROPERTY('
protocol_type') AS protocol_type
, CONNECTIONPROPERTY('
auth_scheme') AS auth_scheme
, CONNECTIONPROPERTY('
local_net_address') AS local_net_address
, CONNECTIONPROPERTY('
local_tcp_port') AS local_tcp_port
, CONNECTIONPROPERTY('
client_net_address') AS client_net_address;
Получить название экземпляра СУБД:
SELECT @@SERVERNAME;
В PostgreSQL:
Получить IP-адрес сервера СУБД:
do $$
declare
title varchar(100) :=host(inet_server_addr());
begin
raise notice '%',
title;
end; $$;
Получение названия экземпляра СУБД пока не реализовано.
Более подробно про системные информационные функции безопасности:
XX. Определение и вызов хранимой процедуры
Определение хранимой процедуры
CREATE OR ALTER PROCEDURE
[схема].[назание_процедуры]
<переменная_1> <тип_1>[=<значение_по_умолчанию_1>],
...
AS
BEGIN
...
END
В PostgreSQL:
CREATE OR REPLACE PROCEDURE
<схема>.<название_процедуры>
(
[INOUT] <переменная_1> <тип_1>[=<значение_по_умолчанию1>],
...
)
LANGUAGE plpgsql
AS $body$
[<Объявление переменных>]
BEGIN
...
END;
$body$
;
Вызов хранимой процедуры
В MS SQL Server:
EXEC <схема>.<процедура>
<переменная_1>=<значение_1>, ..., <переменная_OUTPUT> OUT[PUT];
В PostgreSQL:
call <схема>.<процедура> (
<переменная_1>=<значение_1>, ..., <переменная_OUTPUT>);
XXI. Создание скалярной функции
CREATE OR ALTER
FUNCTION [схема].[название_функции]
(<параметр_1> <тип_1>[=<значение_по_умолчанию_1>], ...)
RETURNS <возвращаемый_тип> AS
BEGIN
... RETURN ...
END
В PostgreSQL:
CREATE OR REPLACE FUNCTION
<схема>.<название_функции>
(<параметр_1> <тип_1>[=<значение_по_умолчанию_1>], ...)
RETURNS <возвращаемый_тип>
LANGUAGE plpgsql
AS $body$
[<Объявление переменных>]
begin
... return (
select ...
);
end;
$body$
;
XXII. Передача табличного значения (вывод таблицы)
В MS SQL Server:
CREATE OR ALTER
PROCEDURE
[схема].[название_хранимой_процедуры]
<параметр_1> <тип_1>,
...,
<параметр_N> <тип_N>
AS
BEGIN
...
SELECT ...
END
В PostgreSQL:
create or replace function
<схема>.<название_функции>
(<параметр_1> <тип_1>, ..., <параметр_N> <тип_N>)
return table (<поле_1> <тип_1>, ..., <поле_N> <тип_N>)
language 'plpgsql'
as $body$
[<Объявление переменных>]
begin
return query (select ....);
end;
$body$;
XXIII. DML-триггеры
Пример в MS SQL Server:
CREATE TRIGGER [info].[tr_isupoll_question_text_last_update_trigger]
ON [info].[isupoll_question_text]
FOR UPDATE
AS
UPDATE
info.isupoll_question_text
SET
last_update_date = GETDATE()
, last_update_user = SUSER_NAME()
FROM
info.isupoll_question_text ds
INNER JOIN INSERTED i ON
ds.isupoll_question_text_id = i.isupoll_question_text_id;
Здесь создаётся триггер tr_isupoll_question_text_last_update_trigger для таблицы info.isupoll_question_text после обновления данных, который для обновляемых строк проставляет текущие дату, время и пользователя соответственно.
DROP TRIGGER IF EXISTS [tr_isupoll_question_text_last_update_trigger]
on [info].[isupoll_question_text];
Здесь удаляется триггер tr_isupoll_question_text_last_update_trigger для таблицы info.isupoll_question_text
Пример в PostgreSQL:
CREATE OR REPLACE
FUNCTION dbo.update_mod()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
begin
new.last_update_date=now();
new.last_update_user=session_user;
return new;
end;
$function$
;
Здесь создаётся функция dbo.update_mod(), которая заполняет два поля текущими датой, временем и пользователем соответственно.
create trigger tr_isupoll_question_text_last_update_trigger before update
on info.isupoll_question_text for each row execute function dbo.update_mod();
Здесь создаётся триггер tr_isupoll_question_text_last_update_trigger для таблицы info.isupoll_question_text до обновления данных, который для каждой строки вызывает выполнение функции dbo.update_mod().
drop trigger if exists tr_isupoll_question_text_last_update_trigger
on info.isupoll_question_text;
Здесь удаляется триггер tr_isupoll_question_text_last_update_trigger для таблицы info.isupoll_question_text.
Важно! В триггере используйте ключевое слово before, когда хотите нашкодничать в той же таблице, для которой создаётся триггер, и after — для логирования в другую таблицу.
Более подробно про DML-триггеры:
И в качестве бонуса кратко рассмотрим сопоставление основных системных представлений и приведём ссылки для мониторинга.
Немного о сопоставлении системных представлений и мониторинге
Сопоставление системных представлений
MS SQL Server | PostgreSQL | Описание |
Предоставляет статистику по выполненным запросам. В MS SQL Server содержит только то, что в кеше, а в PostgreSQL — всю статистику. Для PostgreSQL: CREATE EXTENSION pg_stat_statements; на каждую БД. | ||
Предоставляет статистику по вызовам пользовательских функций. | ||
Предоставляет статистику по использованию всех пользовательских индексов. | ||
Предоставляет статистические данные по каждой БД. |
Системные представления PostgreSQL:
Сборщик статистики
Изображение взято с Postgres 13 Observability Updates.
Системные представления MS SQL Server:
Мониторинг работы СУБД
Заключение
Мы рассмотрели сопоставление типов и основные конструкции синтаксиса MS SQL Server и PostgreSQL, что позволит быстрее адаптировать решения из одной СУБД под другую.
Также мы пробежались по сопоставлению системных представлений и ссылкам на документацию, в том числе о мониторинге, что позволит анализировать производительность реализованного или перенесённого кода.
Благодарность
Спасибо коллегам за ценные комментарии: