# Занятие 09: SQL / Процедуры

> Исходная страница в нативном Markdown: SQL / Процедуры

---

Индекс LLMS: [llms.txt](/ru/llms.txt)

---

<!-- Generated by scripts/generate_skeleton.py. -->

> [!NOTE]
> Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета.
> **HTML source:** [dev1_09_sql_proc.html](https://edu.postgrespro.ru/16/dev1-16/dev1_09_sql_proc.html)
> **Archive:** [DEV1-handouts-16.zip](https://edu.postgrespro.ru/16/DEV1-handouts-16.zip)
> **Страниц PDF:** 19

## Page 1: SQL / Процедуры {#source-page-001}

### Авторские права

© Postgres Professional, 2017–2024 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
### Использование материалов курса

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.
### Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru
### Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения.

## Page 2: Темы {#source-page-002}

- Процедуры и их отличие от функций
- Входные и выходные параметры
- Перегрузка и полиморфизм

## Page 3: Подпрограммы {#source-page-003}

**Функции**
  - вызываются в контексте выражения
  - не могут управлять транзакциями
  - возвращают результат
- Процедуры
  - вызываются оператором CALL
  - могут управлять транзакциями
  - могут возвращать результат

### Комментарии преподавателя

Процедуры были введены в PostgreSQL 11. Основная причина их появления состоит в том, что функции не могут управлять транзакциями. Функции вызываются в контексте какого-либо выражения, которое вычисляется как часть уже начатого оператора (например, SELECT) в уже начатой транзакции. Нельзя завершить транзакцию и начать новую «посередине» выполнения оператора.

Процедуры всегда вызываются специальным оператором CALL. Если этот оператор сам начинает новую транзакцию (а не вызывается из уже начатой), то в процедуре можно использовать команды управления транзакциями.

К сожалению, процедуры, написанные на языке SQL, лишены сейчас возможности использовать команды COMMIT и ROLLBACK, хотя те из них, что оформлены в новом стиле стандарта SQL, вероятно смогут это делать в будущем. Поэтому пример процедуры, управляющей транзакциями, придется отложить до темы «PL/pgSQL. Выполнение запросов».

Иногда можно услышать, что процедура отличается от функции тем, что не возвращает результат. Но это не так — процедуры тоже могут возвращать результат, если необходимо.

Функции и процедуры имеют общее пространство имен и вместе называются подпрограммами (routine).

<https://postgrespro.ru/docs/postgresql/16/sql-createprocedure>

<https://postgrespro.ru/docs/postgresql/16/sql-call>

## Page 4: Процедуры без параметров {#source-page-004}

Начнем с примера простой процедуры без параметров.
```sql
=> CREATE TABLE t(a float);
```
```sql
CREATE TABLE
```
```sql
=> CREATE PROCEDURE fill()
AS $$
    TRUNCATE t;
    INSERT INTO t SELECT random() FROM generate_series(1,3);
$$ LANGUAGE sql;
```
```sql
CREATE PROCEDURE
```
Чтобы вызвать процедуру, необходимо использовать специальный оператор:
```text
=> CALL fill();
```
```text
CALL
```
Результат работы виден в таблице:
```sql
=> SELECT * FROM t;
```
```text
          a
---------------------
  0.6555830623802215
 0.08906944464482747
  0.8696380776099704
(3 rows)
```
А теперь переопределим нашу процедуру в стиле стандарта SQL:
```sql
=> CREATE OR REPLACE PROCEDURE fill()
LANGUAGE sql
BEGIN ATOMIC
    DELETE FROM t;  -- команда TRUNCATE пока что не поддерживается в таких подпрограммах
    INSERT INTO t SELECT random() FROM generate_series(1,3);
END;
```
```sql
CREATE PROCEDURE
```
Убедимся в ее работоспособности:
```text
=> CALL fill();
```
```text
CALL
```
```sql
=> SELECT * FROM t;
```
```text
         a
--------------------
 0.8232510652064371
 0.7274001897871394
 0.8499914745554373
(3 rows)
```
И попробуем в процедуре выполнить фиксацию транзакции:
```sql
=> CREATE OR REPLACE PROCEDURE fill()
LANGUAGE sql
BEGIN ATOMIC
    DELETE FROM t;
    INSERT INTO t SELECT random() FROM generate_series(1,3);
    COMMIT;
END;
```
```text
ERROR:  COMMIT is not yet supported in unquoted SQL function body
```
Обратите внимание, что мы получили ошибку о недопустимой команде еще на этапе определения подпрограммы.
Переименуем таблицу, с которой работает наша процедура:
```sql
=> ALTER TABLE t RENAME TO ta;
```
```sql
ALTER TABLE
```

## Page 5: Page 5 {#source-page-005}

Вызов ниже не приведет к ошибке — таблица в определении процедуры в системном каталоге теперь представлена не по имени, а по идентификатору, который был получен еще на этапе создания подпрограммы.
```text
=> CALL fill();
```
```text
CALL
```
Ту же самую задачу, что выполняла процедура, можно решить и с помощью функции, возвращаемое значение которой определяется последним оператором. Можно объявить тип результата void, если фактически функция ничего не возвращает, или вернуть что-то осмысленное.
Возвратим таблице прежнее имя и определим функцию:
```sql
=> ALTER TABLE ta RENAME TO t;
```
```sql
ALTER TABLE
```
```sql
=> CREATE FUNCTION fill_avg() RETURNS float
LANGUAGE sql
BEGIN ATOMIC
    DELETE FROM t;
    INSERT INTO t SELECT random() FROM generate_series(1, 3);
    SELECT avg(a) FROM t;
END;
```
```sql
CREATE FUNCTION
```
В любом случае функция вызывается в контексте какого-либо выражения:
```sql
=> SELECT fill_avg();
```
```text
      fill_avg
--------------------
 0.7256557081193605
(1 row)
```
```sql
=> SELECT * FROM t;
```
```text
         a
--------------------
 0.7228244239143955
 0.9129684235340032
 0.5411742769096826
(3 rows)
```
Чего нельзя достичь с помощью функции — это управления транзакциями. Но и в процедурах на языке SQL, как мы видели, это пока не поддерживается (зато поддерживается при использовании других языков).
### Процедуры с параметрами
Добавим в процедуру входной параметр — число строк:
```sql
=> DROP PROCEDURE fill();
```
```sql
DROP PROCEDURE
```
```sql
=> CREATE PROCEDURE fill(nrows integer)
LANGUAGE sql
BEGIN ATOMIC
    DELETE FROM t;
    INSERT INTO t SELECT random() FROM generate_series(1, nrows);
END;
```
```sql
CREATE PROCEDURE
```
Точно так же, как и в случае функций, при вызове процедур фактические параметры можно передавать позиционным способом или по имени:
```text
=> CALL fill(nrows => 5);
```
```text
CALL
```
```sql
=> SELECT * FROM t;
```

## Page 6: Page 6 {#source-page-006}

```text
          a
---------------------
  0.6157560832219215
  0.8004849398627427
 0.37948868752520437
  0.6064821790562178
 0.23810577112893738
(5 rows)
```
Процедуры могут также иметь OUT-и INOUT-параметры, с помощью которых можно возвращать значения:
```sql
=> DROP PROCEDURE fill(integer);
```
```sql
DROP PROCEDURE
```
```sql
=> CREATE PROCEDURE fill(IN nrows integer, OUT average float)
LANGUAGE sql
BEGIN ATOMIC
    DELETE FROM t;
    INSERT INTO t SELECT random() FROM generate_series(1, nrows);
    SELECT avg(a) FROM t; -- как в функции
END;
```
```sql
CREATE PROCEDURE
```
Попробуем:
```text
=> CALL fill(5, NULL /* значение не используется, но его необходимо указать*/);
```
```text
      average
--------------------
 0.5911483639915046
(1 row)
```

## Page 7: Перегрузка {#source-page-007}

**Несколько подпрограмм с одним и тем же именем**
  - подпрограмма однозначно определяется сигнатурой —
  - именем и входными параметрами
  - тип возвращаемого значения и выходные параметры игнорируются
  - подходящая подпрограмма выбирается во время выполнения
  - в зависимости от фактических параметров
- Команда CREATE OR REPLACE
  - при несовпадении типов входных параметров создаст новую
  - перегруженную подпрограмму
  - при совпадении — изменит существующую подпрограмму,
  - но нельзя поменять тип подпрограммы, тип возвращаемого значения,
  - типы OUT-параметров

### Комментарии преподавателя

Перегрузка — это возможность использования одного и того же имени для нескольких подпрограмм, отличающихся типами параметров IN и INOUT.

Сигнатура подпрограммы — ее имя и типы входных параметров. При вызове PostgreSQL находит подпрограмму, соответствующую сигнатуре. Возможны ситуации, когда подходящую подпрограмму невозможно определить однозначно; в таком случае во время выполнения возникнет ошибка.

Однако в сигнатуру, в частности, не входят:

- тип подпрограммы (процедура или функция);

- типы параметров OUT;

- тип возвращаемого значения.

Перегрузку надо учитывать, выполняя команду CREATE OR REPLACE (FUNCTION или PROCEDURE). Если сигнатура не совпадает ни с одной из существующих, будет создана новая перегруженная подпрограмма, а при совпадении — изменена существующая. В последнем случае не разрешается изменять тип подпрограммы, тип OUT-параметров или тип возвращаемого значения, однако можно изменить язык и другие свойства. Поэтому иногда приходится удалять подпрограмму и создавать ее заново, в этом случае потребуется также удалить зависящие от нее объекты: другие подпрограммы, представления, триггеры и т. п. (DROP ROUTINE ... CASCADE).

<https://postgrespro.ru/docs/postgresql/16/xfunc-overload>

## Page 8: Полиморфизм {#source-page-008}

**Подпрограмма, принимающая параметры разных типов**
  - формальные параметры используют полиморфные псевдотипы
  - (например, anyelement или anycompatible)
  - конкретный тип данных выбирается во время выполнения
  - по типу фактических параметров

### Комментарии преподавателя

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

Для этого в качестве типа формального параметра указывается специальный полиморфный псевдотип. Пока мы рассмотрим два из них — anyelement и anycompatible — но позже встретимся и с другими.

Полиморфные псевдотипы в определении позволяют использовать в качестве параметров любые типы, при этом конкретный тип, с которым будет работать подпрограмма, выбирается во время выполнения по типу фактического параметра.

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

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

<https://postgrespro.ru/docs/postgresql/16/extend-type-system#EXTEND-TY> PES-POLYMORPHIC

<https://postgrespro.ru/docs/postgresql/16/xfunc-sql#XFUNC-SQL-POLYMO> RPHIC-FUNCTIONS

## Page 9: Перегруженные подпрограммы {#source-page-009}

Перегрузка работает одинаково и для функций, и для процедур. Они имеют общее пространство имен.
В качестве примера напишем функцию, возвращающую большее из двух целых чисел. (Похожее выражение есть в SQL и называется greatest, но мы напишем собственную функцию.)
```sql
=> CREATE FUNCTION maximum(a integer, b integer) RETURNS integer
LANGUAGE sql
RETURN CASE WHEN a > b THEN a ELSE b END;
```
```sql
CREATE FUNCTION
```
Проверим:
```sql
=> SELECT maximum(10, 20);
```
```text
 maximum
---------
      20
(1 row)
```
Допустим, мы решили сделать аналогичную функцию для трех чисел. Благодаря перегрузке, не надо придумывать для нее какое-то новое название:
```sql
=> CREATE FUNCTION maximum(a integer, b integer, c integer)
RETURNS integer
LANGUAGE sql
RETURN CASE
         WHEN a > b THEN maximum(a, c)
         ELSE maximum(b, c)
       END;
```
```sql
CREATE FUNCTION
```
Теперь у нас две функции с одним именем, но разным числом параметров:
```text
=> \df maximum
```
```text
                              List of functions
 Schema |  Name   | Result data type |       Argument data types       | Type
--------+---------+------------------+---------------------------------+------
 public | maximum | integer          | a integer, b integer            | func
 public | maximum | integer          | a integer, b integer, c integer | func
(2 rows)
```
И обе работают:
```sql
=> SELECT maximum(10, 20), maximum(10, 20, 30);
```
```text
 maximum | maximum
---------+---------
      20 |      30
(1 row)
```
Команда CREATE OR REPLACE позволяет создать подпрограмму или заменить существующую, не удаляя ее. Поскольку в данном случае функция с такой сигнатурой уже существует, она будет заменена:
```sql
=> CREATE OR REPLACE FUNCTION maximum(a integer, b integer, c integer)
RETURNS integer
LANGUAGE sql
RETURN CASE
         WHEN a > b THEN
           CASE WHEN a > c THEN a ELSE c END
         ELSE
           CASE WHEN b > c THEN b ELSE c END
         END;
```
```sql
CREATE FUNCTION
```
Пусть наша функция работает не только для целых чисел, но и для вещественных. Как этого добиться? Можно определить еще такую функцию:
```sql
=> CREATE FUNCTION maximum(a real, b real) RETURNS real
LANGUAGE sql
RETURN CASE WHEN a > b THEN a ELSE b END;
```

## Page 10: Page 10 {#source-page-010}

```sql
CREATE FUNCTION
```
Теперь у нас три функции с одинаковым именем:
```text
=> \df maximum
```
```text
                              List of functions
 Schema |  Name   | Result data type |       Argument data types       | Type
--------+---------+------------------+---------------------------------+------
 public | maximum | integer          | a integer, b integer            | func
 public | maximum | integer          | a integer, b integer, c integer | func
 public | maximum | real             | a real, b real                  | func
(3 rows)
```
Две из них имеют одинаковое количество параметров, но отличаются их типами:
```sql
=> SELECT maximum(10, 20), maximum(1.1, 2.2);
```
```text
 maximum | maximum
---------+---------
      20 |     2.2
(1 row)
```
Если подпрограмма перегружена несколько раз, то для получения информации только о некоторых из них можно указать в команде \\df типы интересующих параметров:
```text
=> \df maximum real
```
```text
                        List of functions
 Schema |  Name   | Result data type | Argument data types | Type
--------+---------+------------------+---------------------+------
 public | maximum | real             | a real, b real      | func
(1 row)
```
Дальше нам придется определить функции для всех остальных типов данных и повторить все то же самое для трех параметров. Притом что операторы в теле этих функций будут одни и те же.
### Полиморфные функции
Здесь нам помогут полиморфные типы anyelement и anycompatible. Это псевдотипы, взамен которых при вызове и интерпретации функции будет подставлен тип фактического параметра. Разумеется, в случае определения подпрограммы в стиле стандарта SQL ее код будет разобран еще на этапе создания, и воспользоваться полиморфными псевдотипами не удастся.
Удалим все три наши функции...
```sql
=> DROP FUNCTION maximum(integer, integer);
```
```sql
DROP FUNCTION
```
```sql
=> DROP FUNCTION maximum(integer, integer, integer);
```
```sql
DROP FUNCTION
```
```sql
=> DROP FUNCTION maximum(real, real);
```
```sql
DROP FUNCTION
```
...и затем создадим новую:
```sql
=> CREATE FUNCTION maximum(a anyelement, b anyelement)
RETURNS anyelement
AS $$
    SELECT CASE WHEN a > b THEN a ELSE b END;
$$ LANGUAGE sql;
```
```sql
CREATE FUNCTION
```
Такая функция должна принимать любой тип данных (а работать будет с любым типом, для которого определен оператор «больше»).
Получится?
```sql
=> SELECT maximum('A', 'B');
```
```text
ERROR:  could not determine polymorphic type because input has type unknown
```
Увы, нет. В данном случае строковые литералы могут быть типа char, varchar, text — конкретный тип нам неизвестен. Но можно применить явное приведение типов:

## Page 11: Page 11 {#source-page-011}

```sql
=> SELECT maximum('A'::text, 'B'::text);
```
```text
 maximum
---------
 B
(1 row)
```
Еще пример с другим типом:
```sql
=> SELECT maximum(now(), now() + interval '1 day');
```
```text
            maximum
-------------------------------
 2026-04-11 15:36:52.135967+03
(1 row)
```
Тип результата функции всегда будет тот же, что и тип параметров.
Но можно продвинуться еще дальше — сделать так, чтобы можно было использовать в полиморфных подпрограммах не абсолютно одинаковые типы, а совместимые, то есть те, что могут быть приведены друг к другу неявно. Для этого нужно использовать полиморфный псевдотип anycompatible.
Удалим нашу функцию и взамен нее создадим другую:
```sql
=> DROP FUNCTION maximum;
```
```sql
DROP FUNCTION
```
```sql
=> CREATE FUNCTION maximum(a anycompatible, b anycompatible)
RETURNS anycompatible
AS $$
    SELECT CASE WHEN a > b THEN a ELSE b END;
$$ LANGUAGE sql;
```
```sql
CREATE FUNCTION
```
Повторим наш опыт с параметрами-литералами:
```sql
=> SELECT maximum('A', 'B');
```
```text
 maximum
---------
 B
(1 row)
```
Получилось!
Но если типы параметров не совпадают и не могут быть неявно приведены к некоторому общему типу, то будет ошибка:
```sql
=> SELECT maximum(1, 'A');
```
```sql
ERROR:  invalid input syntax for type integer: "A"
LINE 1: SELECT maximum(1, 'A');
                          ^
```
В этом примере такое ограничение выглядит естественно, хотя в некоторых случаях оно может оказаться и неудобным.
Определим теперь функцию с тремя параметрами, но так, чтобы третий можно было не указывать:
```sql
=> CREATE FUNCTION maximum(
    a anycompatible,
    b anycompatible,
    c anycompatible DEFAULT NULL
) RETURNS anycompatible
AS $$
SELECT CASE
         WHEN c IS NULL THEN
             x
         ELSE
             CASE WHEN x > c THEN x ELSE c END
       END
FROM (
    SELECT CASE WHEN a > b THEN a ELSE b END
) max2(x);
$$ LANGUAGE sql;
```
```sql
CREATE FUNCTION
```
```sql
=> SELECT maximum(10, 11.21, 3e3);
```

## Page 12: Page 12 {#source-page-012}

```text
 maximum
---------
    3000
(1 row)
```
Так работает. А так?
```sql
=> SELECT maximum(10, 11.21);
```
```sql
ERROR:  function maximum(integer, numeric) is not unique
LINE 1: SELECT maximum(10, 11.21);
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type
casts.
```
А так произошел конфликт перегруженных функций:
```text
=> \df maximum
```
```text
                                                  List of functions
 Schema |  Name   | Result data type |                           Argument data types
                     | Type
--------+---------+------------------+----------------------------------------------------
---------------------+------
 public | maximum | anycompatible    | a anycompatible, b anycompatible
                     | func
 public | maximum | anycompatible    | a anycompatible, b anycompatible, c anycompatible
DEFAULT NULL::unknown | func
(2 rows)
```
Невозможно понять, имеем ли мы в виду функцию с двумя параметрами, или с тремя (но просто не указали последний).
Мы решим этот конфликт просто — удалим первую функцию за ненадобностью.
```sql
=> DROP FUNCTION maximum(anycompatible, anycompatible);
```
```sql
DROP FUNCTION
```
```sql
=> SELECT maximum(10, 11.21), maximum(10, 11.21, 3e3);
```
```text
 maximum | maximum
---------+---------
   11.21 |    3000
(1 row)
```
Теперь все работает. А в теме «PL/pgSQL. Массивы» мы узнаем, как определять подпрограммы с произвольным числом параметров.

## Page 13: Итоги {#source-page-013}

**Можно создавать и использовать собственные процедуры**
- В отличие от функций, процедуры вызываются оператором CALL и могут управлять транзакциями
- Для процедур и функций поддерживаются перегрузка и полиморфизм

## Page 14: Практика {#source-page-014}

1. В таблице authors имена, фамилии и отчества авторов по смыслу должны быть уникальны, но это условие никак не проверяется. Напишите процедуру, удаляющую возможные дубликаты авторов. 2. Чтобы необходимость в подобной процедуре не возникала, создайте ограничение целостности, которое не позволит появляться дубликатам в будущем.

### Комментарии преподавателя

1. В приложении возможность добавлять авторов появится в теме «PL/pgSQL. Выполнение запросов». А пока для проверки можно добавить дубликаты в таблицу вручную.

## Page 15: 1. Устранение дубликатов {#source-page-015}

В целях проверки добавим второго Пушкина:
```sql
=> INSERT INTO authors(last_name, first_name, middle_name)
    VALUES ('Пушкин', 'Александр', 'Сергеевич');
```
```sql
INSERT 0 1
```
```sql
=> SELECT last_name, first_name, middle_name, count(*)
FROM authors
GROUP BY last_name, first_name, middle_name;
```
```text
 last_name  | first_name | middle_name | count
------------+------------+-------------+-------
 Свифт      | Джонатан   |             |     1
 Стругацкий | Борис      | Натанович   |     1
 Пушкин     | Александр  | Сергеевич   |     2
 Стругацкий | Аркадий    | Натанович   |     1
 Толстой    | Лев        | Николаевич  |     1
 Тургенев   | Иван       | Сергеевич   |     1
(6 rows)
```
Задачу устранения дубликатов можно решить разными способами. Например, так:
```sql
=> CREATE PROCEDURE authors_dedup()
LANGUAGE sql
BEGIN ATOMIC
DELETE FROM authors
WHERE author_id IN (
    SELECT author_id
    FROM (
        SELECT author_id,
               row_number() OVER (
                   PARTITION BY first_name, last_name, middle_name
                   ORDER BY author_id
               ) AS rn
        FROM authors
    ) t
    WHERE t.rn > 1
);
END;
```
```sql
CREATE PROCEDURE
```
```text
=> CALL authors_dedup();
```
```text
CALL
```
```sql
=> SELECT last_name, first_name, middle_name, count(*)
FROM authors
GROUP BY last_name, first_name, middle_name;
```
```text
 last_name  | first_name | middle_name | count
------------+------------+-------------+-------
 Свифт      | Джонатан   |             |     1
 Стругацкий | Борис      | Натанович   |     1
 Пушкин     | Александр  | Сергеевич   |     1
 Стругацкий | Аркадий    | Натанович   |     1
 Толстой    | Лев        | Николаевич  |     1
 Тургенев   | Иван       | Сергеевич   |     1
(6 rows)
```
### 2. Ограничение целостности
Создать подходящее ограничение целостности мешает тот факт, что отчество может быть неопределенным (NULL). Неопределенные значения считаются различными, поэтому ограничение
```text
UNIQUE(first_name, last_name, middle_name)
```
не помешает добавить второго Джонатана Свифта без отчества.
Задачу можно решить, создав уникальный индекс:

## Page 16: Page 16 {#source-page-016}

```sql
=> CREATE UNIQUE INDEX authors_full_name_idx ON authors(
```
```text
    last_name, first_name, coalesce(middle_name,'')
);
```
```sql
CREATE INDEX
```
Проверим:
```sql
=> INSERT INTO authors(last_name, first_name)
    VALUES ('Свифт', 'Джонатан');
```
```text
ERROR:  duplicate key value violates unique constraint "authors_full_name_idx"
DETAIL:  Key (last_name, first_name, COALESCE(middle_name, ''::text))=(Свифт,
Джонатан, ) already exists.
```
```sql
=> INSERT INTO authors(last_name, first_name, middle_name)
    VALUES ('Пушкин', 'Александр', 'Сергеевич');
```
```text
ERROR:  duplicate key value violates unique constraint "authors_full_name_idx"
DETAIL:  Key (last_name, first_name, COALESCE(middle_name, ''::text))=(Пушкин,
Александр, Сергеевич) already exists.
```

## Page 17: Практика+ {#source-page-017}

1. Получится ли создать в одной и той же схеме и имеющие одно и то же имя: а) процедуру с одним входным параметром, б) функцию с одним входным параметром того же типа, возвращающую некоторое значение? в) процедуру с одним входным и одним выходным параметром? Проверьте. 2. В таблице хранятся вещественные числа (например, результаты каких-либо измерений). Напишите процедуру нормализации данных, которая умножает все числа на определенный коэффициент так, чтобы все значения попали в интервал от −1 до 1. Процедура должна возвращать выбранный коэффициент.

### Комментарии преподавателя

2. В качестве коэффициента возьмите максимальное абсолютное значение из таблицы.

## Page 18: 1. Перегрузка процедур и функций {#source-page-018}

```sql
=> CREATE DATABASE sql_proc;
```
```sql
CREATE DATABASE
```
```sql
=> \c sql_proc
```
```text
You are now connected to database "sql_proc" as user "student".
```
Не получится, так как в сигнатуру подпрограммы входит только имя и тип входных параметров (возвращаемое значение игнорируется), и при этом процедуры и функции имеют общее пространство имен.
```sql
=> CREATE PROCEDURE test(IN x integer)
LANGUAGE sql
RETURN 1;
```
```sql
CREATE PROCEDURE
```
```sql
=> CREATE FUNCTION test(IN x integer) RETURNS integer
LANGUAGE sql
RETURN 1;
```
```text
ERROR:  function "test" already exists with same argument types
```
В некоторых сообщениях, как и в этом, вместо слова «процедура» используется «функция», поскольку во многом они устроены одинаково.
```sql
=> CREATE OR REPLACE PROCEDURE test(IN x integer, OUT y integer)
LANGUAGE sql
RETURN x;
```
```sql
ERROR:  cannot change whether a procedure has output parameters
HINT:  Use DROP PROCEDURE test(integer) first.
```
Такую процедуру тоже создать нельзя, так как уже имеется процедура с такой же сигнатурой, а изменять выходные параметры (и факт их наличия) для имеющейся подпрограммы также запрещено. Нам предлагают удалить подпрограмму, чтобы затем создать ее заново.
### 2. Нормализация данных
Таблица с тестовыми данными:
```sql
=> CREATE TABLE samples(a float);
```
```sql
CREATE TABLE
```
```sql
=> INSERT INTO samples(a)
    SELECT (0.5 - random())*100 FROM generate_series(1,10);
```
```sql
INSERT 0 10
```
Процедуру можно написать, используя один SQL-оператор:
```sql
=> CREATE PROCEDURE normalize_samples(INOUT coeff float)
LANGUAGE sql
BEGIN ATOMIC
   WITH c(coeff) AS (
       SELECT 1/max(abs(a))
       FROM samples
   ),
   upd AS (
       UPDATE samples
       SET a = a * c.coeff
       FROM c
   )
   SELECT coeff FROM c;
END;
```
```sql
CREATE PROCEDURE
```
```text
=> CALL normalize_samples(NULL);
```
```text
        coeff
---------------------
 0.02017240688722836
(1 row)
```

## Page 19: Page 19 {#source-page-019}

```sql
=> SELECT * FROM samples;
```
```text
          a
----------------------
 -0.25509621714380126
                   -1
   0.7473568093452344
  -0.8855333339633462
   0.1247964388514916
 -0.06446930490906046
  0.42323723595948254
   0.5662755975385148
   0.2672450033337049
  -0.5659556144691733
(10 rows)
```
```sql
=> \c postgres
```
```text
You are now connected to database "postgres" as user "student".
```
```sql
=> DROP DATABASE sql_proc;
```
```sql
DROP DATABASE
```