Самоучитель по SQL-сервер в Linux

Здесь http://sochi-lux.info самые красивые путаны Сочи | шлюхи Тольятти здесь


Массивы

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

Создание полей со значениями-массивами

Чтобы создать простейшее поле-массив, включите в команду CREATE TABLE или ALTER TABLE пару квадратных скобок после имени поля. Квадратные скобки показывают, что вместо одного значения в поле может храниться массив указанного типа. Например, команда для создания поля single_array типа type выглядит так:

single_array type[] -- Одномерный массив

Дополнительные квадратные скобки определя ют многомерные массивы, то есть «массивы массивов». Пример:

mu1ti_array type[][] -- Многомерный массив

Теоретически в квадратных скобках можно указать целое число, чтобы созданный массив имел фиксированный размер (то есть всегда состоял из п элементов по указанному измерению и не более). Тем не менее в PostgreSQL 7.1.x это ограничение не соблюдалось, и на практике массив фиксированной длины ничем не отличался от обычного массива.

В листинге 7.16 создается таблица с именем favorite_books, связывающая целочисленный код работника с одномерным массивом строк books.

Листинг 7.16. Создание таблицы с полем-массивом

booktown=# CREATE TABLE favorite_books

booktown-# (employeejid integer, books text[]);

CREATE

Таблица, созданная в листинге 7.16, позволяет хранить в одном поле неограниченное количество названий книг. Преимущество массива перед объединением названий в одну строку (которая, конечно, тоже может содержать несколько названий) заключается в их физической изоляции. Система знает, где начинается и где кончается каждое название, поэтому выборка может производиться по индексу элемента вместо ручного разбора длинной строки на составляющие.

Многомерные массивы создаются аналогичным образом, просто за первой парой квадратных скобок добавляются дополнительные пары. В листинге 7.17 создается таблица favorite_authors с целочисленным полем employee_id и многомерным массивом author_and_titles. Фактически мы создаем массив текстовых массивов.

Листинг 7.17. Создание таблицы с полем, содержащим многомерный массив

booktown=# CREATE TABLE favorite_authors (employee_id Integer,

booktowntf authors_and_titles text[][]);

CREATE

Вставка значений в поля-массивы

В PostgreSQL предусмотрен специальный синтаксис вставки нескольких значений в одно поле. Этот синтаксис основан на определении массивов-констант. Как упоминалось в главе 3, массив-константа (предназначенный для ссылки на массивы PostgreSQL в командах SQL) состоит из фигурных скобок, апострофов и запятых, заключенных в апострофы. Кавычки нужны только при работе с массивами строк. Таким образом, обобщенная форма массива-константы выглядит так:

'{ "текст" [. ...] }' -- массив строк

'{ число [. ...]}' -- числовой массив

В этих примерах использованы строковые и числовые массивы, но поле может определяться как массив произвольного типа (включая типы boolean, date и time). Как правило, если для описания величины в скалярном контексте должны использоваться апострофы (например, в строковых константах или данных типа timestamp), в контексте массива эта величина заключается в кавычки.

В листинге 7.18 в таблицу favorite_books вставляются две записи. Первая команда создает массив с одним элементом для работника с кодом 102, а вторая запись создает массив с двумя элементами для работника с кодом 103. В обеих командах INSERT используются массивы-константы.

Листинг 7.18. Вставка с использованием массивов-констант

booktown=# INSERT INTO favorite_books VALUES

booktown-# (102, '{"The HitchhikerVs Guide to the Galaxy"}');

INSERT 3628399 1

booktown=# INSERT INTO favorite_books VALUES

booktown-# (103, '{"The Hobbit". "Kitten, Squared"}');

INSERT 3628400 1

Как видно из листинга, даже при вставке одного элемента массив заключается в фигурные скобки. Кроме того, обратите внимание, что апостроф в названии книги (первая команда INSERT) экранируется символом \, хотя и находится внутри кавычек. Это связано с тем, что массив-константа сначала обрабатывается как одна длинная строка, а затем интерпретируется как массив по контексту целевого поля.

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

Листинг 7.19. Вставка данных в многомерный массив

booktown=# INSERT INTO favorite_authors

bOOktown-# VALUES (102,

booktown(# '{{"J.R.R. Tolkien". "The Silmarillion"},

booktown'# {"Charles Dickens", "Great Expectations"},

booktown'l {"Ariel Denham", "Attic Lives"}}');

INSERT 3727961 1

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

Выборка из полей-массивов

При выборке из поля-массива весь массив возвращается в формате константы, описанном в предыдущем разделе. В листинге 7.20 команда SELECT выбирает все элементы массивов в поле books таблицы favorite_books.

Листинг 7.20. Выборка из полей-массивов

booktown=# SELECT books FROM favorite_books;

books

{"The Hitchhiker's Guide to the Galaxy"}

{"The Hobbit"."Kitten. Squared")

(2 rows)

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

Индексы элементов

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

В отличие от таких языков программирования, как С, в PostgreSQL индексация в массивах начинается с 1, а не с 0. Так, в листинге 7.21 индекс [1] для поля books таблицы favorite_books описывает первый элемент массива. Обратите внимание: данные, возвращаемые запросом, не заключаются в кавычки или фигурные скобки. Это связано с тем, что отдельное текстовое значение должно возвращаться в виде одной текстовой константы, а не массива.

Листинг 7.21. Выборка отдельного элемента массива

booktown=# SELECT books[l] FROM favorite_books:

books

The Hitchhiker's Guide to the Galaxy The Hobbit

(2 rows)

При указании индекса несуществующего элемента массива выборка возвращает NULL. Обычно для обработки таких ситуаций используется конструкция IS NOT NULL. В листинге 7.22 приведены два запроса; первый возвращает две записи — для NULL и для названия книги. Второй запрос возвращает только название, а запись с NULL исключается из выборки в результате использования секции WHERE с проверкой условия NOT NULL.

Листинг 7.22. Предотвращение выборки NULL в массивах

booktown=# SELECT books[2] FROM favorite_books;

books

Kitten. Squared (2 rows)

booktown=# SELECT books[2] FROM favorite_books

booktown-# WHERE books[2] IS NOT NULL;

books

Kitten. Squared

(1 row)

При выборке из многомерного массива за исходным индексом перечисляются дополнительные индексы. В листинге 7.23 из таблицы favorite_authors, созданной в листинге 7.19, выбираются два элемента — имя автора и название книги.

Листинг 7.23. Выборка из многомерного массива

booktown=# SELECT authors_and_titles[l][l] AS author,

booktown-# authors_and_titles[l][2] AS title

booktown-# FROM favorite authors;

author I title

J.R.R. Tolkien | The Silmarillion

(1 row)

Срезы

В PostgreSQL также поддерживается возможность создания срезов при выборке из массива. Срез аналогичен обычному обращению к элементам по индексу, но он описывает интервал значений. Срез задается парой целочисленных индексов, разделенных двоеточием и заключенных в квадратные скобки. Например, конструкция [2:5] описывает второй, третий, четвертый и пятый элемент заданного массива. Результат среза возвращается в виде константы-массива, которая фактически описывает подмножество элементов исходного массива (впрочем, срез может содержать все элементы исходного массива).

В листинге 7.24 выбираются первые два элемента массива books в записях таблицы favorite_books. Хотя первая запись содержит только одно название книги, оно возвращается в виде массива из одного элемента.

Листинг 7.24. Выборка с использованием среза

booktown=# SELECT books[l:2] FROM favorite_books;

books

{"The Hitchhiker's Guide to the Galaxy"}

{"The Hobbit"."Kitten, Squared"}

(2 rows)

В PostgreSQL 7.1.x использование срезов в многомерных массивах иногда приводит к непредсказуемым последствиям. По этой причине, пока не будут внесены исправления, при работе с многомерными массивами рекомендуется обращаться к элементам по конкретным значениям индексов.

Определение количества элементов

Чтобы узнать количество значении, хранящихся в массиве, следует воспользоваться функцией array_dims(). В качестве параметра функции передается идентификатор — имя поля-массива, для которого вызывается функция. Результат возвращается в виде строки, содержащей описание массива в синтаксисе среза. В листинге 7.25 приведен пример вызова функции array_dims() для поля books таблицы favorite_books.

Листинг 7.25. Функция array_dims()

booktown=# SELECT array_dims(books) FROM favorite_books;

array_dims

[1:1]

[1:2]

(2 rows)

Обновление данных в полях-массивах

Существует три варианта модификации данных в полях-массивах.

  • Полная модификация. Все содержимое массива заменяется новыми данными, заданными в виде массива-константы.
  • Модификация среза. Срез (то есть интервальное подмножество элементов) заменяется новыми данными, заданными в виде массива-константы. Количествоэлементов в константе должно соответствовать количеству элементов в обновляемом срезе.
  • Модификация элемента. Отдельный элемент массива заменяется новой константой, относящейся к базовому типу данных массива. Элемент задается индексом. В первом случае количество элементов в новом массиве может не совпадать с количеством элементов в существующем массиве. Допустим, работник с кодом 1d=102 хочет добавить данные о новой книге в список, хранящийся в таблице favorite_books. Команда UPDATE, приведенная в листинге 7.26, заменяет все текущее содержимое массива.

Листинг 7.26. Полная модификация массива

booktown=# UPDATE favorite_books

booktown-# SET books='{"The HitchhikerVs Guide to the Galaxy",

booktown'# "The Restaurant at the End of the Universe"}'

booktown-# WHERE employeejd = 102;

UPDATE 1

Способ, продемонстрированный в листинге 7.26, подходит и для модификации среза массива. Для этого в конец идентификатора поля добавляется определение среза, например, books[l:3] означает первый, второй и третий элементы массива. Впрочем, на практике чаще возникает задача замены не всего массива и не среза, а отдельных элементов.

При обновлении отдельного элемента к идентификатору поля присоединяется индекс, определяющий конкретный обновляемый элемент*. В листинге 7.27 приведен пример обновления первого элемента в массиве books таблицы favorite_books.

Листинг 7.27. Модификация отдельного элемента

booktown=# SELECT books[l] FROM favorite_books;

books

The Hitchhiker's Guide to the Galaxy The Hobbit

(2 rows)

booktown=# UPDATE favorite_books

booktown-# SET books[l] = 'There and Back Again: A HobbitVs Holiday'

booktown-# WHERE books[l] = 'The Hobbit';

UPDATE 1

booktown=# SELECT books[l] FROM favorite_books;

books

The Hitchhiker's Guide to the Galaxy

There and Back Again: A Hobbit's Holiday

(2 rows)




Книжный магазин