Пошук уроків, статей та іншого контенту
Розглянете створення composite types, вкладені структури та роботу зі складеними значеннями в запитах.
Складений тип (composite type) — це тип даних, який складається з кількох іменованих полів. За структурою він нагадує рядок таблиці, але може використовуватися як тип окремого стовпця, параметр функції або значення в запиті.
Складені типи зручні, коли кілька пов’язаних значень потрібно об’єднати в одну логічну структуру:
адресу з містом, вулицею та поштовим індексом;
контактну особу з іменем, телефоном та адресою;
координати з широтою і довготою.
Складений тип створюється командою CREATE TYPE:
CREATE TYPE address AS (
city text,
street text,
postal_code text
);Тепер address можна використовувати як звичайний тип даних:
CREATE TABLE companies (
id integer PRIMARY KEY,
name text NOT NULL,
legal_address address
);Стовпець legal_address зберігає одне складене значення з трьома полями:
city;
street;
postal_code.
На відміну від таблиці, складений тип сам по собі не має рядків. Він лише описує структуру значення.
У визначенні складеного типу не можна безпосередньо вказати обмеження на кшталт NOT NULL, CHECK або DEFAULT для окремих полів:
CREATE TYPE address AS (
city text,
street text
);Обмеження можна застосувати до стовпця таблиці:
CREATE TABLE companies (
id integer PRIMARY KEY,
legal_address address NOT NULL
);Якщо потрібна складніша перевірка значень, її можна виконувати на рівні таблиці, домену або прикладного коду.
Для створення значення складеного типу можна використовувати конструктор типу. Його ім’я збігається з назвою типу:
SELECT address(
'Київ',
'вулиця Хрещатик, 1',
'01001'
);Результатом буде одне значення типу address.
Складене значення також можна створити за допомогою ROW і явного приведення типу:
SELECT ROW(
'Київ',
'вулиця Хрещатик, 1',
'01001'
)::address;Конструктор типу зазвичай читабельніший, коли структура має багато полів.
Одне з полів складеного типу може мати інший складений тип.
Спочатку створимо тип адреси, а потім тип контактної особи, який містить адресу:
CREATE TYPE address AS (
city text,
street text,
postal_code text
);
CREATE TYPE contact_person AS (
full_name text,
phone text,
address address
);Тепер contact_person має таку вкладену структуру:
contact_person
├── full_name
├── phone
└── address
├── city
├── street
└── postal_codeСкладені типи можна використовувати в таблиці разом із простими типами:
CREATE TABLE companies (
id integer PRIMARY KEY,
name text NOT NULL,
legal_address address NOT NULL,
contact contact_person
);Наведений приклад можна виконати в PostgreSQL як один SQL-скрипт:
DROP TABLE IF EXISTS companies;
DROP TYPE IF EXISTS contact_person;
DROP TYPE IF EXISTS address;
CREATE TYPE address AS (
city text,
street text,
postal_code text
);
CREATE TYPE contact_person AS (
full_name text,
phone text,
address address
);
CREATE TABLE companies (
id integer PRIMARY KEY,
name text NOT NULL,
legal_address address NOT NULL,
contact contact_person
);
INSERT INTO companies (
id,
name,
legal_address,
contact
)
VALUES
(
1,
'ТОВ "Дніпро"',
address(
'Дніпро',
'проспект Дмитра Яворницького, 10',
'49000'
),
contact_person(
'Олена Коваль',
'+380501112233',
address(
'Дніпро',
'вулиця Центральна, 5',
'49001'
)
)
),
(
2,
'ТОВ "Карпати"',
address(
'Львів',
'вулиця Галицька, 20',
'79000'
),
contact_person(
'Андрій Мельник',
'+380671234567',
address(
'Львів',
'вулиця Шевченка, 15',
'79005'
)
)
);
SELECT
id,
name,
(legal_address).city AS legal_city,
(contact).full_name AS contact_name,
(contact).phone AS contact_phone,
((contact).address).city AS contact_city
FROM companies
ORDER BY id;У результаті окремі поля вкладеної структури будуть доступні як звичайні колонки результату.
Щоб звернутися до поля складеного значення, використовують крапку:
SELECT (legal_address).city
FROM companies;Якщо використовується псевдонім таблиці, потрібно додати його перед складеним стовпцем:
SELECT (c.legal_address).city
FROM companies AS c;Дужки важливі. Запис:
c.legal_address.cityможе бути інтерпретований PostgreSQL не так, як очікується. Надійний варіант:
(c.legal_address).cityДля вкладених полів дужки використовують на кожному рівні:
SELECT ((c.contact).address).postal_code
FROM companies AS c;Тут:
(c.contact) — значення типу contact_person;
(c.contact).address — вкладене значення типу address;
((c.contact).address).postal_code — конкретне поле адреси.
Поля складених типів можна використовувати у WHERE, ORDER BY та інших частинах запиту:
SELECT
id,
name
FROM companies AS c
WHERE (c.legal_address).city = 'Львів';Сортування за полем складеного типу:
SELECT
name,
(legal_address).city AS city
FROM companies
ORDER BY (legal_address).city, name;Вибір компаній за містом контактної особи:
SELECT
name,
(contact).full_name AS contact_name
FROM companies AS c
WHERE ((c.contact).address).city = 'Дніпро';Якщо потрібно отримати всі поля складеного значення окремими колонками, використовують .*:
SELECT
c.id,
c.name,
(c.legal_address).*
FROM companies AS c;Запит розгортає legal_address у три колонки:
city;
street;
postal_code.
Так само можна розгорнути вкладений об’єкт:
SELECT
c.id,
(c.contact).full_name AS contact_name,
(c.contact).phone AS contact_phone,
((c.contact).address).*
FROM companies AS c;Якщо в запиті розгортаються кілька структур з однаковими назвами полів, краще задавати явні псевдоніми, щоб уникнути неоднозначності в результаті.
Складене значення можна замінити повністю:
UPDATE companies
SET legal_address = address(
'Київ',
'вулиця Велика Васильківська, 12',
'03150'
)
WHERE id = 1;Для зміни одного поля потрібно сформувати нове значення складеного типу:
UPDATE companies AS c
SET contact = contact_person(
(c.contact).full_name,
'+380931234567',
(c.contact).address
)
WHERE c.id = 1;У цьому прикладі змінюється лише телефон, а ім’я та вкладена адреса беруться з поточного значення.
Для зміни вкладеного поля потрібно реконструювати зовнішню і внутрішню структури:
UPDATE companies AS c
SET contact = contact_person(
(c.contact).full_name,
(c.contact).phone,
address(
'Київ',
'вулиця Хрещатик, 1',
'01001'
)
)
WHERE c.id = 1;Тут створюється новий address, а потім новий contact_person.
ROWКонструктори типів не є єдиним способом вставки:
INSERT INTO companies (
id,
name,
legal_address,
contact
)
VALUES (
3,
'ТОВ "Одеса"',
ROW(
'Одеса',
'вулиця Дерибасівська, 8',
'65026'
)::address,
ROW(
'Марія Бондар',
'+380631112233',
ROW(
'Одеса',
'вулиця Преображенська, 30',
'65045'
)::address
)::contact_person
);Явне приведення ::address і ::contact_person робить тип кожного ROW однозначним.
Запит може повертати весь складений стовпець:
SELECT
name,
legal_address
FROM companies;У клієнті PostgreSQL значення може відображатися у текстовому представленні на кшталт:
(Київ,"вулиця Хрещатик, 1",01001)Для прикладного коду зазвичай зручніше повертати окремі поля або розгортати значення через .*:
SELECT
name,
(legal_address).city,
(legal_address).street,
(legal_address).postal_code
FROM companies;NULL у складених типахСкладений стовпець може бути NULL повністю:
INSERT INTO companies (
id,
name,
legal_address,
contact
)
VALUES (
4,
'ТОВ "Полісся"',
address(
'Житомир',
'вулиця Київська, 2',
'10001'
),
NULL
);Окремі поля складеного значення також можуть бути NULL:
INSERT INTO companies (
id,
name,
legal_address
)
VALUES (
5,
'ТОВ "Поділля"',
address(
'Вінниця',
NULL,
'21000'
)
);Це різні ситуації:
contact IS NULL — усього складеного значення немає;
(contact).phone IS NULL — значення існує, але його поле phone не заповнене.
Перевіряти їх можна окремо:
SELECT
name,
contact IS NULL AS contact_is_missing,
(contact).phone IS NULL AS phone_is_missing
FROM companies;Кожна таблиця PostgreSQL автоматично має власний складений тип, який відповідає її рядку. Наприклад, таблиця companies може бути використана як тип companies у виразах.
Окремо оголошений тип, як-от address, варто використовувати тоді, коли однакова структура потрібна в кількох таблицях або інших об’єктах бази даних. Якщо структура потрібна лише для однієї таблиці, простіше часто залишити поля звичайними стовпцями.
Ненадійний запис:
SELECT c.legal_address.city
FROM companies AS c;Правильний запис:
SELECT (c.legal_address).city
FROM companies AS c;Для вкладених значень:
SELECT ((c.contact).address).city
FROM companies AS c;address у цьому прикладі — це назва типу, а address('Київ', ...) — виклик конструктора, який створює значення цього типу.
CREATE TYPE address AS (
city text,
street text,
postal_code text
);
SELECT address('Київ', 'вулиця Хрещатик, 1', '01001');Кількість і порядок аргументів конструктора мають відповідати визначенню типу:
CREATE TYPE address AS (
city text,
street text,
postal_code text
);Коректний виклик:
SELECT address('Київ', 'вулиця Хрещатик, 1', '01001');Якщо пропустити поле або змінити порядок аргументів, структура значення буде неправильною.
Складене значення не варто розглядати як окремий набір незалежних стовпців. Для надійного оновлення потрібно створити нове значення зовнішнього типу, передавши до нього незмінені поля та нове поле.
Якщо розгорнути кілька складених значень, вони можуть містити однакові назви полів:
SELECT
(legal_address).*,
((contact).address).*
FROM companies;У результаті двічі з’являться city, street і postal_code. Для читабельного результату краще вибирати поля явно та задавати псевдоніми.
Складений тип створюється через CREATE TYPE ... AS (...).
Він об’єднує кілька іменованих полів в одне значення.
Складені типи можна використовувати як типи стовпців таблиць.
Одне поле складеного типу може містити інший складений тип.
Значення створюють через конструктор типу або ROW(...)::тип.
Для доступу до поля використовують дужки: (column).field.
Вкладені поля читаються послідовно: ((column).nested).field.
Оператор .* розгортає складене значення в окремі колонки.
Для оновлення вкладених структур зазвичай формують нове складене значення.
Повністю NULL складене значення відрізняється від значення, окремі поля якого дорівнюють NULL.