Первый способ ввести составной тип - явным образом объявить его.
=> CREATE TYPE currency AS ( amount numeric, code text );
CREATE TYPE
=> \dT
List of data types
Schema | Name | Description
--------+----------+-------------
public | currency |
(1 row)
Такой тип можно использовать точно так же, как любой другой тип SQL. Мы можем создать таблицу с полем такого типа:
=> CREATE TABLE transactions( account_id integer, debit currency, credit currency, date_entered date DEFAULT current_date );
CREATE TABLE
Значения составного типа можно формировать либо в виде строки, внутри которой в скобках перечислены значения:
=> INSERT INTO transactions VALUES(1, NULL, '(100.00,"RUR")');
INSERT 0 1
Либо с помощью табличного конструктора ROW:
=> INSERT INTO transactions VALUES(2, ROW(80.00,'RUR'), NULL);
INSERT 0 1
Если составной тип содержит более одного поля, то слово ROW можно опустить:
=> INSERT INTO transactions VALUES(3, (20.00,'RUR'), NULL);
INSERT 0 1
=> SELECT * FROM transactions;
account_id | debit | credit | date_entered
------------+-------------+--------------+--------------
1 | | (100.00,RUR) | 2017-09-01
2 | (80.00,RUR) | | 2017-09-01
3 | (20.00,RUR) | | 2017-09-01
(3 rows)
Чтобы обратиться к отдельному атрибуту, составное значение необходимо взять в скобки, чтобы отличать атрибут записи от столбца таблицы:
=> SELECT (t.debit).amount, (t.credit).amount FROM transactions t;
amount | amount
--------+--------
| 100.00
80.00 |
20.00 |
(3 rows)
Дальше мы можем создать функции для работы с этим типом. Например:
=> CREATE FUNCTION multiply(factor numeric, cur currency) RETURNS currency AS $$ SELECT ROW(factor * cur.amount, cur.code)::currency; $$ IMMUTABLE LANGUAGE SQL;
CREATE FUNCTION
=> SELECT account_id, multiply(2,debit), multiply(2,credit), date_entered FROM transactions;
account_id | multiply | multiply | date_entered
------------+--------------+--------------+--------------
1 | (,) | (200.00,RUR) | 2017-09-01
2 | (160.00,RUR) | (,) | 2017-09-01
3 | (40.00,RUR) | (,) | 2017-09-01
(3 rows)
Хотелось бы, чтобы такая функция не превращала неопределенное значение в пустую запись. Для этого можно либо явно выполнить необходимую проверку, либо указать для функции свойство STRICT:
=> ALTER FUNCTION multiply(numeric, currency) STRICT;
ALTER FUNCTION
=> SELECT account_id, multiply(2,debit), multiply(2,credit), date_entered FROM transactions;
account_id | multiply | multiply | date_entered
------------+--------------+--------------+--------------
1 | | (200.00,RUR) | 2017-09-01
2 | (160.00,RUR) | | 2017-09-01
3 | (40.00,RUR) | | 2017-09-01
(3 rows)
Мы можем даже определить оператор умножения:
=> CREATE OPERATOR * ( PROCEDURE = multiply, LEFTARG = numeric, RIGHTARG = currency );
CREATE OPERATOR
И использовать его в выражениях:
=> SELECT account_id, 2 * debit, 2 * credit, date_entered FROM transactions;
account_id | ?column? | ?column? | date_entered
------------+--------------+--------------+--------------
1 | | (200.00,RUR) | 2017-09-01
2 | (160.00,RUR) | | 2017-09-01
3 | (40.00,RUR) | | 2017-09-01
(3 rows)
Нужно ли вводить новый тип и использовать его в таблицах - вопрос сложный, универсальных рецептов тут не существует. В каких-то случаях это может быть полезно; в каких-то удобнее действовать в реляционных рамках: выделить сущность, представляемую типом, в отдельную таблицу и ссылаться на нее.
В целом PostgreSQL обладает достаточно большим количеством встроенных типов данных, так что, вероятно, необходимость в создании собственного типа будет возникать не часто, если не сказать редко.
Другое, более полезное на практике применение составных типов - упрощение работы функций с таблицами.
При создании таблицы неявно создается одноименный тип (места, например, в кинотеатре):
=> CREATE TABLE seats( line text, number integer, vip boolean );
CREATE TABLE
Команда \dT "прячет" такие неявные типы, но при желании их можно увидеть непосредственно в таблице pg_type.
Напишем функцию, возвращающую табличную строку по отдельным компонентам.
Ее можно объявить как RETURNS seats:
=> CREATE FUNCTION make_seat(line text, number integer, vip boolean DEFAULT false) RETURNS seats AS $$ SELECT ROW(line, number, vip)::seats; $$ IMMUTABLE LANGUAGE SQL;
CREATE FUNCTION
=> SELECT make_seat('A',42);
make_seat ----------- (A,42,f) (1 row)
Функцию можно вызывать не только в списке выборки запроса или в условиях, как часть выражения. К функции можно обратиться и в предложении FROM, как к таблице:
=> SELECT * FROM make_seat('A',42);
line | number | vip ------+--------+----- A | 42 | f (1 row)
При этом мы получаем не один столбец составного типа, а строку. При таком варианте вызова видно, что имена столбцов выборки (и их типы) получены из описания составного типа seats.
Другой вариант, который мы уже видели в предыдущей теме - объявить функцию с выходными параметрами.
Заодно отметим, что в запросе не обязательно собирать составной тип из отдельных полей - это будет проделано автоматически:
=> DROP FUNCTION make_seat(text, integer, boolean);
DROP FUNCTION
=> CREATE FUNCTION make_seat(line INOUT text, number INOUT integer, vip INOUT boolean DEFAULT false) AS $$ SELECT line, number, vip; $$ IMMUTABLE LANGUAGE SQL;
CREATE FUNCTION
=> SELECT make_seat('A',42);
make_seat ----------- (A,42,f) (1 row)
=> SELECT * FROM make_seat('A',42);
line | number | vip ------+--------+----- A | 42 | f (1 row)
Получаем тот же результат - но имена и типы полей в данном случае получены из выходных параметров фунции, а сам составной тип остается анонимным.
И еще один вариант - объявить функцию как возвращающую псевдотип record, который обозначает составной тип "вообще", без уточнения его структуры.
=> DROP FUNCTION make_seat(text, integer, boolean);
DROP FUNCTION
=> CREATE FUNCTION make_seat(line text, number integer, vip boolean DEFAULT false) RETURNS record AS $$ SELECT line, number, vip; $$ IMMUTABLE LANGUAGE SQL;
CREATE FUNCTION
=> SELECT make_seat('A',42);
make_seat ----------- (A,42,f) (1 row)
Но вызвать такую функцию в предложении FROM уже не получится, поскольку возвращаемый составной тип не просто анонимный, но и количество и типы его полей заранее (на этапе разбора запроса) неизвестны:
=> SELECT * FROM make_seat('A',42);
ERROR: a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM make_seat('A',42);
^
В этом случае при вызове функции структуру составного типа придется уточнить:
=> SELECT * FROM make_seat('A',42) AS seats(line text, number integer, vip boolean);
line | number | vip ------+--------+----- A | 42 | f (1 row)
При написании функций допустим любой из этих трех вариантов, но лучше сразу подумать об использовании: будет ли удобен анонимный тип и уточнение структуры типа при вызове.
Определим теперь функцию, принимающую значение составного типа и возвращающую текстовый номер места.
=> CREATE FUNCTION no(seat seats) RETURNS text AS $$ SELECT seat.line || seat.number; $$ IMMUTABLE LANGUAGE SQL;
CREATE FUNCTION
=> SELECT no(ROW('A',42,false));
no ----- A42 (1 row)
Что удобно, такой функции можно передавать непосредственно строку таблицы.
=> INSERT INTO seats VALUES ('A',42,true), ('B',1,false), ('C',27,false);
INSERT 0 3
=> SELECT s.line, s.number, no(s.*) FROM seats s;
line | number | no ------+--------+----- A | 42 | A42 B | 1 | B1 C | 27 | C27 (3 rows)
Синтаксисом допускается обращение к функции как к столбцу таблицы (и наоборот, к столбцу как к функции).
=> SELECT s.line, number(s), s.no FROM seats s;
line | number | no ------+--------+----- A | 42 | A42 B | 1 | B1 C | 27 | C27 (3 rows)
Таким образом можно использовать функции как "вычислимые поля" таблиц.
Разумеется, такого же эффекта можно добиться, определив представление.
Значения составных типов можно сравнивать между собой. Это происходит поэлементно (примерно так же, так строки сравниваются посимвольно):
=> SELECT * FROM seats s WHERE s < make_seat('B',52);
line | number | vip ------+--------+----- A | 42 | t B | 1 | f (2 rows)
Осторожно: существует много тонкостей, связанных с неопределенными значениями внутри записей.
Также работает проверка на неопределенность IS [NOT] NULL и сравнение IS [NOT] DISTINCT FROM.
Составные типы можно использовать с подзапросами, что бывает очень удобно.
Добавим таблицу с билетами:
=> CREATE TABLE tickets( line text, number integer, start date );
CREATE TABLE
=> INSERT INTO tickets VALUES ('A',42,current_date), ('B',1,current_date+1);
INSERT 0 2
Теперь, например, можно написать такой запрос:
=> SELECT * FROM seats WHERE (line, number) IN ( SELECT line, number FROM tickets WHERE start = current_date );
line | number | vip ------+--------+----- A | 42 | t (1 row)
Иначе пришлось бы либо явно соединять таблицы, либо повторять подзапрос.
Мы знаем, что к функциям можно обращаться в предложении FROM, однако до сих пор результатом была одна строка. Конечно, интереснее было бы иметь функции, возвращающие множество строк.
Напишем функцию, которая вернет все места в зале заданного размера (и ближняя половина зала будет считаться vip-зоной).
=> CREATE FUNCTION make_seats(max_line integer, max_number integer) RETURNS SETOF seats AS $$ SELECT chr(line+64), number, line <= max_line/2 FROM generate_series(1,max_line) AS lines(line), generate_series(1,max_number) AS numbers(number); $$ IMMUTABLE LANGUAGE SQL;
CREATE FUNCTION
Ключевое отличие - слово SETOF. В таком случае функция возвращает не первую строку последнего запроса, как обычно, а все строки последнего запроса.
=> SELECT * FROM make_seats(max_line => 2, max_number => 3);
line | number | vip ------+--------+----- A | 1 | t A | 2 | t A | 3 | t B | 1 | f B | 2 | f B | 3 | f (6 rows)
Вместо "SETOF seats" можно использовать и "SETOF record".
=> DROP FUNCTION make_seats(integer, integer);
DROP FUNCTION
=> CREATE FUNCTION make_seats(max_line integer, max_number integer) RETURNS SETOF record AS $$ SELECT chr(line+64), number, line <= max_line/2 FROM generate_series(1,max_line) AS lines(line), generate_series(1,max_number) AS numbers(number); $$ IMMUTABLE LANGUAGE SQL;
CREATE FUNCTION
Но в этом случае, как мы видели, при вызове функции придется уточнять структуру составного типа:
=> SELECT * FROM make_seats(max_line => 2, max_number => 3) AS seats(line text, number integer, vip boolean);
line | number | vip ------+--------+----- A | 1 | t A | 2 | t A | 3 | t B | 1 | f B | 2 | f B | 3 | f (6 rows)
А можно объявить функцию с выходными параметрами. Но SETOF record все равно придется написать, чтобы показать, что функция возвращает не одну строку, а множество:
=> DROP FUNCTION make_seats(integer, integer);
DROP FUNCTION
=> CREATE FUNCTION make_seats(max_line integer, max_number integer, OUT line text, OUT number integer, OUT vip boolean) RETURNS SETOF record AS $$ SELECT chr(line+64), number, line <= max_line/2 FROM generate_series(1,max_line) AS lines(line), generate_series(1,max_number) AS numbers(number); $$ IMMUTABLE LANGUAGE SQL;
CREATE FUNCTION
=> SELECT * FROM make_seats(max_line => 2, max_number => 3);
line | number | vip ------+--------+----- A | 1 | t A | 2 | t A | 3 | t B | 1 | f B | 2 | f B | 3 | f (6 rows)
Еще один равнозначный (и к тому же описанный в стандарте SQL) способ объявить табличную функцию - указать слово TABLE:
=> DROP FUNCTION make_seats(integer, integer);
DROP FUNCTION
=> CREATE FUNCTION make_seats(max_line integer, max_number integer) RETURNS TABLE(line text, number integer, vip boolean) AS $$ SELECT chr(line+64), number, line <= max_line/2 FROM generate_series(1,max_line) AS lines(line), generate_series(1,max_number) AS numbers(number); $$ LANGUAGE SQL;
CREATE FUNCTION
=> SELECT * FROM make_seats(max_line => 2, max_number => 3);
line | number | vip ------+--------+----- A | 1 | t A | 2 | t A | 3 | t B | 1 | f B | 2 | f B | 3 | f (6 rows)
Иногда в запросах бывает полезно пронумеровать строки в том порядке, в котором они получены от функции. Для этого есть специальная конструкция:
=> SELECT * FROM make_seats(max_line => 2, max_number => 3) WITH ORDINALITY;
line | number | vip | ordinality ------+--------+-----+------------ A | 1 | t | 1 A | 2 | t | 2 A | 3 | t | 3 B | 1 | f | 4 B | 2 | f | 5 B | 3 | f | 6 (6 rows)
При использовании функции в предложении FROM, перед ней неявно подразумевается ключевое слово LATERAL, что позволяет функции обращаться к столбцам таблиц, стоящих в запросе слева от нее. Иногда это позволяет упростить формулировку запросов. Например, для каждой строки таблицы вернем столько строк, какое число содержится в этой строке:
=> WITH t(n) AS ( VALUES (1), (2), (3) ) SELECT * FROM t, generate_series(1,t.n);
n | generate_series ---+----------------- 1 | 1 2 | 1 2 | 2 3 | 1 3 | 2 3 | 3 (6 rows)
Интересно, что к функции, возвращающей множество строк, можно обратиться и в списке выборки запроса:
=> SELECT make_seats(3,4);
make_seats ------------ (A,1,t) (A,2,t) (A,3,t) (A,4,t) (B,1,f) (B,2,f) (B,3,f) (B,4,f) (C,1,f) (C,2,f) (C,3,f) (C,4,f) (12 rows)
В некоторых случаях это смотрится логично, но иногда результат может удивить. Например, сколько строк вернет такой запрос?
=> SELECT make_seats(2,3), make_seats(2,2);
make_seats | make_seats ------------+------------ (A,1,t) | (A,1,t) (A,2,t) | (A,2,t) (A,3,t) | (B,1,f) (B,1,f) | (B,2,f) (B,2,f) | (A,1,t) (B,3,f) | (A,2,t) (A,1,t) | (B,1,f) (A,2,t) | (B,2,f) (A,3,t) | (A,1,t) (B,1,f) | (A,2,t) (B,2,f) | (B,1,f) (B,3,f) | (B,2,f) (12 rows)
В результате получается наименьшее общее кратное числа строк, возвращенных каждой функцией.
Еще хуже, что запрос может вернуть меньше строк, чем ожидалось, если функция не вернет ни одной строки при каком-то значении параметров.
Поэтому такой способ вызова не стоит использовать.
Как мы видели, функцию можно использовать во фразе FROM как таблицу или представление. Но при этом мы дополнительно получаем возможность использовать параметры, что в ряде случаев бывает удобно.
Единственная сложность с таким подходом состоит в том, что при обращении к функции (Function Scan) запросы из нее сначала выполняются полностью, и только затем к результату применяются дополнительные условия из запроса.
=> EXPLAIN SELECT * FROM make_seats(3,4) WHERE line = 'A';
QUERY PLAN
-----------------------------------------------------------------
Function Scan on make_seats (cost=0.25..12.75 rows=5 width=37)
Filter: (line = 'A'::text)
(2 rows)
Если бы функция содержала сложный, долгий запрос, это могло бы стать проблемой.
В некоторых случаях тело функции может подставляться в вызывающий запрос. Для табличных функций ограничения более мягкие:
В нашем случае дело в том, что последний раз мы объявили функцию как VOLATILE, не указав категорию изменчивости явно.
=> ALTER FUNCTION make_seats(integer, integer) IMMUTABLE;
ALTER FUNCTION
=> EXPLAIN SELECT * FROM make_seats(3,4) WHERE line = 'A';
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=0.01..155.00 rows=5000 width=37)
-> Function Scan on generate_series lines (cost=0.00..17.50 rows=5 width=4)
Filter: (chr((line + 64)) = 'A'::text)
-> Function Scan on generate_series numbers (cost=0.00..10.00 rows=1000 width=4)
(4 rows)
Теперь нет вызова функции как такового, а условие подставлено внутрь запроса, что более эффективно.
Конец демонстрации.