Карманный справочник: сравнение синтаксиса MS SQL Server и PostgreSQL

Приветствую, уважаемые хабражители!

Я занимаюсь переводом кода из 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 и наоборот.

Начнём с сопоставления типов.

Сопоставление типов

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 лучше не использовать. Причины подробно описаны здесь.

Более подробно о типах данных:

  1. MS SQL Server

  2. PostgreSQL

Теперь перейдём к сопоставлению синтаксиса MS SQL Server и PostgreSQL.

Сопоставление синтаксиса MS SQL Server и PostgreSQL

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 данных

В MS SQL Server используется TOP:

В PostgreSQL используется LIMIT:

SELECT TOP(N) …;

SELECT …. LIMIT N;

III. Постраничная загрузка данных (скользящее окно)
Задача: извлечь 100 строк начиная с 202-й строки включительно по возрастанию даты рождения:

в MS SQL Server:

в PostgreSQL:

SELECT *
FROM tbl
ORDER BY BirthDate ASC
OFFSET 201 ROW FETCH
NEXT 100 ROWS ONLY;

select *
from tbl
order by BirthDate asc
[—offset 201 row fetch
next 100 rows only;]
LIMIT 100 OFFSET 200

Примечание. Вместо row можно использовать rows в любом месте запроса, а вместо next можно использовать first в обеих СУБД.

IV. Выборка первого непустого значения

MS SQL Server

PostgreSQL

COALESCE — рекомендуется

ISNULL — не рекомендуется

coalesce

V. Тернарный оператор IIF

MS SQL Server

PostgreSQL

IIF (<условие>,<выражение_если_условие_истинно>,<выражение_если_условие_ложно>)

case <условие> then <выражение_если_условие_истинно> else <выражение_если_условие_ложно> end

VI. Создание псевдонима

MS SQL Server

PostgreSQL

AS — рекомендуется

= — не рекомендуется

as

VII. Выражения CASE

MS SQL Server

PostgreSQL

CASE-WHEN-THEN-END

CASE-WHEN-THEN-ELSE-END

case-when-then-end

case-when-then-else-end

VIII. Работа с переменными

Объявление переменной

MS SQL Server

PostgreSQL

DECLARE @val;

declare val;

Примечание. В MS SQL Server при объявлении переменных используется знак @ перед именем, а в PostgreSQL — нет. Также, помимо PL/pgSQL, в PostgreSQL можно встраивать и другие языки, такие как PL/Python и PL/Perl.

Присвоение переменной значения

MS SQL Server

PostgreSQL

SET @переменная = значение;

переменная := значение

Примечание. В PostgreSQL используется := для PL/pgSQL и просто = для PL/Python и PL/Perl.

Вывод значения на консоль

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:

  1. В DBeaver (бобре) нужно нажать CTRL+SHIFT+O при отсутствии окна вывода, а в pgAdmin вывод происходит автоматически.

  2. В 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;

Более подробно про управление выполнением кода:

  1. Управление выполнением кода в MS SQL Server

  2. Управляющие структуры в PostgreSQL

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;

Более подробно про функции для работы со строками:

  1. MS SQL Server

  2. PostgreSQL

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’);
или
dt + interval ‘count datepart’;

Более подробно про функции для работы с датой и временем:

1. MS SQL Server

2. PostgreSQL

XII. Получение количества строк, затронутых при выполнении последней команды

XIII. Выполнение динамического 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:

  1. MS SQL Server

  2. PostgreSQL

Удаление данных

Пример в 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:

  1. MS SQL Server

  2. PostgreSQL

Получение изменённых записей

В MS SQL Server:

В PostgreSQL:

insert/update/delete таблица
Output deleted/inserted.<столбец>
into [@/#] <таблица>
Values|From <запрос>

insert/update/delete таблица
values()|from <запрос>|using <запрос>
returning *, столбец/столбцы

В update есть доступ только к inserted.

Примечание. В PostgreSQL не нужна промежуточная таблица для получения изменённых записей.

Более подробно:

1. OUTPUT в MS SQL Server

2. returning в 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;

Здесь:

  1. #<table> — локальная временная таблица, которая видна только в текущей сессии

  2. ##<table> — глобальная временная таблица, которая видна всем пока она существует

Все временные таблицы живут, либо пока активна сессия, во время которой они были созданы, либо пока их явно не удалят.

В PostgreSQL:

Для основной таблицы:

drop table if exists
<schema>.<table>;

Для временной таблицы:

drop table if exists <table>;

Более детально про удаление таблиц:

  1. MS SQL Server

  2. PostgreSQL

Создание таблицы через выборку

В MS SQL Server:

Для основной таблицы:

select ...
into <table>
from …

Для временной таблицы:

select ...
into #<table>
from …

В PostgreSQL:

Для основной таблицы:

create table <table> as
select ...

Для временной таблицы:

create temp table <table> as
select …

Более детально про создание таблиц через выборку:

  1. MS SQL Server

  2. PostgreSQL

Создание/изменение и удаление значения по умолчанию для колонки таблицы

В 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 COLUMN <поле>
<новый_тип> [NULL|NOT NULL];

alter table
<схема>.<таблица>
alter column <поле>
type <новый_тип>;

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

Перенос автоинкрементных полей

В 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 лучше сначала удалять представление, а потом заново его создавать, если набор полей меняется или меняются названия выходных полей, иначе можно получить ошибку при обращении к изменённому представлению.

Более подробно про создание и изменение представлений:

MS SQL Server

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:

  1. session_user — под каким пользователем открыта сессия

  2. 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; $$;

Получение названия экземпляра СУБД пока не реализовано.

Более подробно про системные информационные функции безопасности:

  1. MS SQL Server

  2. PostgreSQL

XX. Определение и вызов хранимой процедуры

Определение хранимой процедуры

В MS SQL Server:

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. Создание скалярной функции

В MS SQL Server:

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-триггеры:

  1. MS SQL Server

  2. PostgreSQL

И в качестве бонуса кратко рассмотрим сопоставление основных системных представлений и приведём ссылки для мониторинга.

Немного о сопоставлении системных представлений и мониторинге

Сопоставление системных представлений

Системные представления PostgreSQL:
Сборщик статистики

Изображение взято с Postgres 13 Observability Updates.

Системные представления MS SQL Server:

  1. SQL Server 2012 System Views Map

  2. Системные динамические административные представления

Мониторинг работы СУБД

Заключение

Мы рассмотрели сопоставление типов и основные конструкции синтаксиса MS SQL Server и PostgreSQL, что позволит быстрее адаптировать решения из одной СУБД под другую.

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

Благодарность

Спасибо коллегам за ценные комментарии:

Источники

Читайте так же:

  • Контент для наполнения сайтаКонтент для наполнения сайта Пользователи подключают свои компьютеры к веб - контенту через различные слои интернета Веб-контент-это текстовый, визуальныйили звуковой контент , который встречается как часть пользовательского опыта на веб-сайтах. Он может включать в себя. Среди прочего, текст, изображения, […]
  • Компанию распознавания лиц Clearview AI оштрафовали в Великобритании на $22,6 млнКомпанию распознавания лиц Clearview AI оштрафовали в Великобритании на $22,6 млн Британское Управление Комиссара по информации (Information Commissioner's Office, ICO) оштрафовало компанию по распознаванию лиц Clearview AI на $22,6 млн за несоблюдение национальных законов о защите данных. По мнению ICO, Clearview AI не проинформировала жителей страны о сборе фото с […]
  • Дептранс Москвы опубликовал статистику по шерингу — более 200 млн поездок на всех видах транспорта с 2013 годаДептранс Москвы опубликовал статистику по шерингу — более 200 млн поездок на всех видах транспорта с 2013 года Департамент транспорта Москвы опубликовал статистику по велопрокату, каршерингу и кикшерингу. Согласно представленным данным, с момента запуска шеринга в 2013 году пользователи сервисов совершили более 200 млн поездов на всех видах транспорта. Лидирует в статистике каршенинг — более 165 […]
  • Раскрутка сайта — Обман в обмене ссылкамиРаскрутка сайта — Обман в обмене ссылками На фоне стремительно растущего числа каталогов, заблокированных поисковыми системами. Веб-мастера все больше внимания стали уделять прямому и перекрёстному обмену ссылками. Еще вчера твой сайт после прогона по белым каталогам имел ИЦ равный 500 и более 1000 обратных ссылок. А сегодня […]