Переход с MS SQL на PostgreSQL: объявление переменных

Быстрый ответ

В PostgreSQL обычно объявление локальных переменных осуществляется в анонимном блоке DO для одноразового выполнения или внутри функций PL/pgSQL. Ключевое слово DECLARE служит для указания типа переменной и её начального значения. Вот пример анонимного блока DO:

DO $$
DECLARE
    my_var int := 42;
BEGIN
    -- использование переменной 'my_var'
END $$;

Объявление переменной в функции PL/pgSQL выглядит аналогично – оно происходит в начальной части функции:

CREATE FUNCTION my_func() RETURNS void AS $$
DECLARE
    my_var int := 42;
BEGIN
    -- здесь переменная 'my_var' может быть использована
END $$ LANGUAGE plpgsql;

В этих примерах переменная my_var с типом данных int инициализируется значением 42, которое, по необходимости, может быть изменено.

Роль PL/pgSQL в PostgreSQL

В обзоре PostgreSQL нельзя обойти роль PL/pgSQL. Его синтаксис и логическое построение отличаются от аналогов в других системах, таких как MS SQL Server, поэтому им стоит уделить особое внимание.

Для объявления переменных в PL/pgSQL с установлением значений по умолчанию воспользуйтесь следующим шаблоном:

my_var data_type [ := | = | DEFAULT ] initial_value;

Синтаксис ALIAS FOR в PL/pgSQL позволяет присваивать краткие псевдонимы параметрам и сложным выражениям.

В PostgreSQL также возможно обращаться к параметрам функции по порядковым номерам:

CREATE FUNCTION compute_sum(a int, b int) RETURNS int AS $$
BEGIN
    RETURN $1 + $2;
END $$ LANGUAGE plpgsql;

Здесь $1 и $2 соответствуют параметрам a и b соответственно, что позволяет экономить время при написании кода.

Переход с MS SQL Server на PostgreSQL

Переход с MS SQL Server на PostgreSQL требует привыкания к новым особенностям:

• В MS SQL Server распространены хранимые процедуры для генерации нескольких наборов данных. Из-за особенностей протокола передачи данных в PostgreSQL преимущественно используются функции, возвращающие один набор данных или одиночное значение.
• В MS SQL Server языковые возможности управления потоком выполнения более разнообразны, что может потребовать значительной адаптации при работе с PL/pgSQL.

При планировании миграции учитывайте изменения синтаксиса и функционала, адаптируя старые скрипты под особенности PostgreSQL, в частности, для версий 8.4 и 9.0.

Визуализация

Если сравнивать переменные SQL с должностями в офисе, можно представить следующую аналогию:

На языке SQL это выглядит следующим образом:

BEGIN;
  DECLARE assistant VARCHAR; -- 📋 Помощник все записывает
  assistant := 'Задание принято'; -- 📋 Задание записано, а в перерыве помощник может принести кофе
COMMIT;

Правильно распределяйте задания в SQL, применяя переменные в соответствии с их «должностями».

Лучшие практики и потенциальные подводные камни

Следуйте рекомендуемым практикам и избегайте типичных ошибок при работе с локальными переменными в PostgreSQL:

• Избегайте конфликтов имен переменных с именами столбцов в таблицах.
• Внимательно относитесь к области видимости переменной: она, объявленная в 'DO' или функции, недоступна за пределами этих блоков.
• Учитывайте, что анонимный блок DO не предназначен для возвращения результатов. Его используют для операций, не требующих возврата значения.
• В PostgreSQL нельзя напрямую через SQL объявлять глобальные и сеансовые переменные.

Полезные материалы

1. PostgreSQL: Документация: Глава 43. PL/pgSQL — SQL процедурный язык — базовые сведения о PL/pgSQL.
2. PostgreSQL: Документация: Пункт 43.3. Объявления — информация об объявлении переменных и констант в PL/pgSQL.
3. sql – Есть ли способ определить именованную константу в запросе PostgreSQL? – Stack Overflow — советы и нюансы использования переменных.
4. PL/pgSQL – Советы по проектированию – Вики PostgreSQL — полезные рекомендации для программирования на PL/pgSQL.
5. SQL WITH: Организация сложных запросов — использование Общих Табличных Выражений (CTE) в качестве временных переменных.