Команды PostgreSQL
В данной главе приведена сводная информация по всем основным командам SQL, поддерживаемым в PostgreSQL. В этот справочник включены как стандартные команды SQL (например, INSERT и SELECT), так и специфические команды PostgreSQL (такие, как CREATE OPERATOR и CREATE TYPE).
Описание каждой команды за редким исключением делится па три части: синтаксис, описание и примергл. В первой части приводится общий синтаксис команды, объясняется смысл параметров и возможные результаты выполнения команды. Во второй части приводится краткий обзор основных применений команды. В последней части дается хотя бы один практический пример ее использования.
ABORT
Отмена изменений, внесенных в транзакционном блоке.
Синтаксис
ABORT [ WORK | TRANSACTION ]
Параметры
Необязательные ключевые слова, делающие команду SQL более наглядной.
Результаты
ROLLBACK. Сообщение выдается при успешном выполнении команды ABORT. NOTICE: ROLLBACK: no transaction in progress. Сообщение выдается при отсутствии незавершенных транзакций.Пример
Пример использования команды ABORT для отмены случайно введенной команды
DELETE:
booktown=# BEGIN WORK;
BEGIN
booktown=# DELETE FROM publishers WHERE id < 100;
DELETE 6
booktown=# ABORT WORK;
ROLLBACK
ALTER GROUP
Модификация структуры группы пользователей.
Синтаксис
ALTER GROUP имя ADD USER
пользователь [. ...] ALTER GROUP имя DROP USER
пользователь [. ...]
Параметры
имя. Имя группы, в которую вносятся изменения. пользователь. Имена пользователей, включаемых в группу или удаляемых из нее. При перечислении имена пользователей разделяются запятыми.Описание
Команда ALTER GROUP применяется суперпользователем базы данных для включения заданных пользователей в группу и удаления их из группы. Поскольку привилегии могут устанавливаться на уровне групп, включение пользователя в группу автоматически наделяет его соответствующими привилегиями. В группу включаются только уже созданные пользователи. Удаление пользователя из группы не приводит к его удалению из системы.
Группы создаются командой CREATE GROUP и удаляются командой DROP GROUP. Для предоставления и отзыва привилегий в группах используются команды GRANT и REVOKE.
Примеры
Следующая команда включает пользователей PostgreSQL с именами Jessica и wiП1 am в группу sales:
booktown=# ALTER GROUP sales ADD USER Jessica, william:
ALTER
Удаление пользователя Jessica из группы sales:
booktown=# ALTER GROUP sales DROP USER Jessica:
ALTER
ALTER TABLE
Модификация таблиц и атрибутов нолей.
Синтаксис
ALTER TABLE таблица [ * ]
ADD [ COLUMN ] поле тип
ALTER TABLE таблица [ * ]
ALTER [ COLUMN ] поле { SET DEFAULT noj/нолчант \ DROP DEFAULT }
ALTER TABLE таблица [ * ]
RENAME [ COLUMN ] попе ТО новое_попе
ALTER TABLE таблица
RENAME TO новое_имя
ALTER TABLE таблица
ADD CONSTRAINT новое_ограничение определение
ALTER TABLE таблица
OWNER TO новый_владелец
Параметры
таблица. Имя существующей таблицы, в которую вносятся изменения. поле. Имя нового или существующего поля, в которое вносятся изменения. тип. Тип данных нового поля (используется только при создании новых полей). по _умолчанию. Новое значение но умолчанию для заданного поля. новое_поле. Новое имя поля. новая_таблица. Новое имя таблицы. новое_ограничение определение. Имя и определение нового ограничения, добавляемого в существующую таблицу. За дополнительной информацией о создании ограничений обращайтесь к главе 7. новый_владелец. Новый владелец таблицы.Результаты
ALTER. Сообщение выдается при успешной модификации поля или таблицы. ERROR. Ошибка — модификация поля или таблицы невозможна (по возможности с разъяснением причин).Описание
Команда ALTER TABLE предназначена для внесения разнообразных изменений в структуру существующих таблиц баз данных. Переименование полей или таблиц производится ключевым словом RENAME. Содержимое поля или таблицы при переименовании не изменяется. Новые ограничения включаются в таблицу секцией ADD CONSTRAINT, при этом ограничения определяются с таким же синтаксисом, как при создании таблицы командой CREATE TABLE (см. описание команды CREATE TABLE).
В последней на момент издания книги версии PostgreSQL (7.1.x) секция ADD CONSTRAINT поддерживает добавление только ограничений внешнего ключа (FOREIGN KEY) и проверки (CHECK). Ограничение уникальности (UNIQUE) можно установить косвенно — построением уникального индекса командой CREATE INDEX (см. описание команды CREATE INDEX). Чтобы создать любое другое ограничение, необходимо создать таблицу заново и заполнить ее данными.
Новые поля создаются секцией ADD COLUMN с таким же синтаксисом, как при создании таблицы командой CREATE TABLE (см. описание команды CREATE TABLE). Чтобы изменить или удалить значение по умолчанию для поля, воспользуйтесь секцией ALTER COLUMN с подсекциями SET DEFAULT или DROP DEFAULT (помните, что значения по умолчанию применяются только к вновь созданным полям и не распространяются на существующие поля).
PostgreSQL 7.1.x не позволяет задать значение по умолчанию или ограничение для поля одновременно с его созданием в секции ADD COLUMN. Тем не менее секция SET DEFAULT команды ALTER TABLE может использоваться для определения значений по умолчанию после создания поля. Если значение по умолчанию задается после того, как таблица использовалась в течение некоторого времени, не забудьте обновить содержимое поля командой UPDATE.
ПРИМЕЧАНИЕ
Чтобы модифицировать таблицу, необходимо быть ее владельцем или суперпользователем.
Примеры
Следующая команда включает в таблицу employees новое поле address типа text:
booktown=# ALTER TABLE employees ADD COLUMN address text;
ALTER
Затем созданное поле address переименовывается в mailing_address:
booktown=# ALTER TABLE employees RENAME COLUMN address TO mailing_address;
ALTER
Переименование таблицы employees в personnel:
booktown=# ALTER TABLE employees RENAME TO personnel;
ALTER
Следующая команда назначает новым владельцем таблицы personnel пользователя PostgreSQL с именем Jonathan:
booktown=# ALTER TABLE personnel OWNER TO Jonathan:
ALTER
Наконец, следующая команда устанавливает для таблицы schedules ограничение внешнего ключа с именем valid_employee, проверяющее присутствие поля id в таблице personnel:
booktown=# ALTER TABLE schedules ADD CONSTRAINT valid_employee
booktown-# FOREIGN KEY (employeejd)
booktown-# REFERENCES personnel (id) MATCH FULL;
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for
FOREIGN KEY check(s)
CREATE
ALTER USER
Модификация атрибутов и прав пользователя.
Синтаксис
ALTER USER пользователь
[ WITH PASSWORD 'пароль' ]
[ CREATEDB I NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ]
[ VALID UNTIL 'время' ]
Параметры
пользователь. Имя пользователя PostgreSQL, данные которого изменяются командой ALTER USER. пароль. Новый пароль, назначаемый пользователю PostgreSQL. CREATEDB | NOCREATEDB. Привилегия создания новых баз данных. Значение CREATEDB разрешает, а значение NOCREATEDB запрещает создание новых баз (используется по умолчанию). CREATEUSER | NOCREATEUSER. Привилегия суперпользователя. Значение CREATEUSER разрешает выполнение команд CREATE USER и DROP USER, а также предоставляет привилегию суперпользователя (с правами выполнения любых операций с любыми базами данных). По умолчанию используется значение NOCREATEUSER.ВНИМАНИЕ
Разрешая пользователю PostgreSQL создавать других пользователей, вы автоматически предоставляете ему привилегии суперпользователя, а это создает определенный риск для системы безопасности, поскольку на суперпользователя не распространяются никакие ограничения доступа.
время. Продолжительность действия пароля. При наступлении заданной даты и времени пароль автоматически становится недействительным. Если параметр не задан, срок действия пароля не ограничивается.Результаты
ALTER USER. Сообщение выдается при успешной модификации атрибутов пользователя. ERROR: ALTER USER: user "пользователь" does not exist. Ошибка — пользователь с заданным именем не существует.Описание
Команда ALTER USER предназначена для изменения атрибутов и прав пользователей PostgreSQL.
ПРИМЕЧАНИЕ
Использование команды ALTER USER для изменения привилегий и срока действия паролей разрешено только суперпользователю. Обычные пользователи могут только изменять свой пароль.
Учетные записи пользователей PostgreSQL создаются и удаляются командами CREATE USER и DROP USER соответственно.
Примеры
Следующая команда изменяет пароль пользователя mark:
booktown=# ALTER USER mark WITH PASSWORD 'т10215еm';
ALTER USER
Изменение срока действия пароля для пользователя mark:
booktown=# ALTER USER mark VALID UNTIL 'Dec 24 2012';
ALTER USER
BEGIN
Начало отложенного транзакционного блока.
Синтаксис
BEGIN [ WORK | TRANSACTION ]
Параметры
Необязательные ключевые слова, делающие команду SQL более наглядной.
Результаты
BEGIN. Сообщение выдается в начале транзакции. NOTICE: BEGIN: already transaction in progress. Сообщение выдается в том случае, если в сеансе PostgreSQL уже имеется незавершенная транзакция (вложение транзакций в PostgreSQL не допускается). Состояние текущей незавершенной транзакции при этом не изменяется.Описание
По умолчанию транзакции PostgreSQL выполняются в непосредственном режиме (также называемом режимом с автоматической фиксацией). В этом режиме для каждой команды пользователя автоматически создается отдельная транзакция, которая либо автоматически фиксируется в базе данных, либо откатывается в зависимости от результата выполнения команды.
Команда BEGIN сообщает серверу о создании транзакцнопного блока в отложенном режиме. В этом режиме команды, передаваемые серверу, сохраняются в очереди до получения сервером команды COMMIT. Также возможен откат транзакции по команде ROLLBACK или при неожиданном отключении пользователя от базы данных.
Отложенный режим часто используется при работе с несколькими взаимосвязанными таблицами, а также для повышения общей эффективности операций с базой данных. Выполнение команд в отложенном режиме снижает затраты ресурсов процессора и дискового пространства, поскольку на каждый блок команд используется только одна операция его фиксации.
При фиксации транзакции сервер пытается выполнить все обновления, входящие в нее. При отсутствии ошибок обновление проходит успешно, в противном случае весь транзакционный блок откатывается.
Пример
Следующий код начинает транзакцию и создает таблицу, после чего транзакция фиксируется в базе данных:
booktown=# BEGIN WORK;
BEGIN
booktown=# CREATE TABLE test (id integer, name text);
CREATE
booktown=# COMMIT WORK;
COMMIT
CLOSE
Закрытие объекта курсора.
Синтаксис
CLOSE курсор
Параметры
Имя открытого курсора.
Результаты
CLOSE. Сообщение выдается при успешном закрытии курсора. NOTICE: PerformPortalClose: portal "курсор" not found. Сообщение выдается в том случае, если заданный курсор не был объявлен или открыт.Описание
Команда CLOSE закрывает открытый курсор и освобождает все используемые им ресурсы. Когда надобность в курсоре отпадает, его следует закрывать. После закрытия курсора дальнейшие операции с ним невозможны.
ПРИМЕЧАНИЕ
Команды COMMIT и ROLLBACK завершают текущие транзакции и закрывают все открытые курсоры.
Пример
Следующий код открывает транзакцию, объявляет курсор cur_publishers и закрывает его.
booktown=# BEGIN;
BEGIN
booktown=# DECLARE cur_publishers CURSOR FOR SELECT * FROM publishers;
SELECT
booktown=# CLOSE cur_publishers;
CLOSE
CLUSTER
Кластеризация таблицы по заданному индексу.
Синтаксис
CLUSTER индекс ON таблица
Параметры
индекс. Имя индекса, используемого при кластеризации. таблица. Имя таблицы, для которой производится кластеризация.Результаты
CLUSTER. Сообщение выдается при успешной кластеризации таблицы. ERROR: CLUSTER: "индекс" is not an index for table "таблица". Ошибка — попытка кластеризации индекса, не относящегося к заданной таблице. ERROR: Index "индекс" does not exist. Ошибка — индексе заданным именем не найден в текущей базе данных. ERROR: Relation "таблица" does not exist. Ошибка — таблица с заданным именем не найдена в текущей базе данных. ERROR: Relation "производная_таблица" Inherits from "таблица". Ошибка — у заданной таблицы имеется производная таблица.Описание
Команда CLUSTER производит кластеризацию таблицы по заданному индексу. Таблица должна существовать, индекс также должен существовать и относиться к указанной таблице.
В процессе кластеризации содержимое таблицы упорядочивается так, чтобы повысить эффективность поиска по конкретному индексу. Кластеризация является «разовой» операцией; чтобы провести повторную кластеризацию, команду CLUSTER необходимо выполнить заново.
ВНИМАНИЕ
Во время кластеризации записи таблицы копируются во временную таблицу в порядке, определяемом заданным индексом, после чего временная таблица переименовывается в исходную. Таким образом, кластеризация приводит к уничтожению остальных индексов.
Пример
Следующая команда производит кластеризацию таблицы subjects на базе индекса
subjects_pkey:
booktown=# CLUSTER subjects_pkey ON subjects;
CLUSTER
COMMENT
Определение комментария для объекта базы данных.
Синтаксис
COMMENT ON
[
[ DATABASE | INDEX | RULE | SEQUENCE | TABLE | TYPE | VIEW ] объект
COLUMN таблица.поле|
FUNCTION функция ( аргумент [. ...] ) |
AGGREGATE агрвгатная_функция агрегатный_тип |
OPERATOR оператор ( левый_тип , правый_тип ) |
TRIGGER триггер ON таблица
] IS 'текст'
Параметры
DATABASE INDEX | RULE | SEQUENCE | TABLE | TYPE VIEW. Тип объекта базы данных, для которого создается комментарий. объект. Имя объекта заданного типа, для которого создается комментарий. COLUMN таблица.поле. Уточненное имя поля, для которого создается комментарий. FUNCTION функция ( аргумент [, ...] ). Имя функции, для которой создается комментарий, с указанием типов данных аргументов. AGGREGATE агрегатная_функция агрегатный_тип. Имя агрегатной функции (и агрегатный тип данных), для которой создается комментарий. OPERATOR оператор ( левый_тип, правый_тип). Имя оператора, для которого создается комментарий, с указанием типов левого и правого операндов, разделенных запятыми и заключенных в круглые скобки. Если какой-либо из типов отсутствует, он заменяется ключевым словом NONE. TRIGGER триггер ON таблица. Имя триггера, для которого создается комментарий, и имя таблицы, к которой относится триггер. текст. Текст комментария.Результаты
COMMENT. Это сообщение выдается при успешном создании комментария для объекта.
Описание
Команда COMMENT относится к числу специфических команд PostgreSQL. Она предназначена для комментирования различных объектов и самой базы данных. В клиенте psql комментарии выводятся следующими командами:
\д+ — вывод списка всех доступных баз данных с комментариями; \dd — вывод списка объектов всех баз данных с комментариями; \d+ — вывод списка объектов текущей базы данных с комментариями; \dt+ — вывод списка всех таблиц в текущей базе данных с комментариями; \ds+ — вывод списка всех последовательностей в текущей базе данных с комментариями; \dv+ — вывод списка всех представлений в текущей базе данных с комментариями; \df+ — вывод списка всех функций в текущей базе данных с комментариями; \da+ — вывод списка всех агрегатных функций в текущей базе данных с комментариями; \do+ — вывод списка всех операторов в текущей базе данных с комментариями; \dT+ — вывод списка всех типов в текущей базе данных с комментариями. Чтобы удалить комментарий, укажите вместо текста NULL.ПРИМЕЧАНИЕ
При удалении объектов из системы удаляются и связанные с ними комментарии.
Примеры
Следующая команда создает комментарий для таблицы customers:
booktown=# COMMENT ON TABLE customers IS 'For customer names.';
COMMENT
Удаление комментария к таблице customers:
booktown=# COMMENT ON TABLE customers IS NULL;
COMMIT
Завершение транзакционного блока и фиксация изменений в базе данных.
Синтаксис
COMMIT [ WORK | TRANSACTION ]
Параметры
Необязательные ключевые слова, делающие команду SQL более наглядной.
Результаты
COMMIT. Сообщение выдается при успешной фиксации изменений в базе данных. NOTICE: COMMIT: no transaction in progress. Сообщение выдается при отсутствии незавершенных транзакций.Описание
Команда COMMIT предназначена для фиксации текущей транзакции, то есть внесения всех изменений в базу данных.
ПРИМЕЧАНИЕ
Если в транзакционном блоке происходит нечто непредвиденное, текущую транзакцию можно откатить командой ABORT. При этом отменяются последствия всех команд SQL, выполненных после последней команды BEGIN.
Пример
Следующий код начинает транзакцию и создает в таблице новую запись, после чего транзакция фиксируется в базе данных:
booktown=# BEGIN WORK;
BEGIN
booktown=# INSERT INTO employees VALUES (106. 'HalV. 'Timothy');
INSERT 3574402 1
booktown=# COMMIT WORK;
COMMIT
COPY
Копирование данных между файлами и таблицами.
Синтаксис
COPY [ BINARY ] таблица [ WITH OIDS ]
FROM { 'файл' | stdin }
[ [ USING ] DELIMITERS 'разделитель' ]
[ WITH NULL AS ' строка _null' ] COPY [BINARY ] table [ WITH OIDS ]
TO { 'файл' | stdout }
[ [ USING ] DELIMITERS 'разделитель' ]
[ WITH NULL AS 'строка_null' ]
Параметры
BINARY. Ключевое слово BINARY означает, что при сохранении и чтении данных командой COPY должен использоваться внутренний двоичный формат PostgreSQL (вместо текстового). При использовании двоичного формата ключевые слова WITH NULL и DELIMITERS неприменимы. таблица. Имя существующей таблицы, в которую (или из которой) копируются данные. FROM. Ключевое слово FROM означает, что команда COPY копирует данные из файла или стандартного ввода в таблицу. ТO. Ключевое слово ТO означает, что команда COPY копирует данные из таблицы в файл или стандартный вывод. WITH OIDS. Необязательный признак копирования идентификатора объекта (OID). Означает, что при выборке или вставке (в зависимости от разновидности команды, COPY FROM или COPY TO) записи сохраняют исходное значение OID. файл. Абсолютный путь к файлу, используемому для ввода пли вывода (например, /usr/local/pgsql/data/employeetable). stdin. Если вместо имени файла указано ключевое слово stdin, данные не загружаются из файла, а передаются клиентским приложением. Если ввод данных осуществляется в клиенте psql, то при вызове команды COPY FROM с ключевым словом stdin вам будет предложено ввести нужный текст. stdout. Направление данных в стандартный вывод. Если вместо имени файла указано ключевое слово stdout, данные не направляются в файл, а передаются непосредственно клиентской программе (например, psql). разделитель. Символ, разделяющий значения полей. При выполнении команды COPY FROM PostgreSQL предполагает, что этот символ разделяет значения полей во входных данных. При выполнении команды COPY TO символ разделяет значения полей в выходных данных. Если разделитель не задан, по умолчанию используется символ табуляции (\t). Разделитель должен состоять из одного символа. Если заданное значение состоит из нескольких символов, PostgreSQL использует только первый символ. строка_null. Последовательность символов, идентифицирующая псевдозначение NULL. По умолчанию используется \N, но вы можете выбрать другое представление, в большей степени соответствующее вашим целям. При копировании данных в таблицу все строки, совпадающие с этой строкой, интерпретируются как NULL, поэтому очень важно, чтобы строковое представление NULL при импортировании и экспортировании данных совпадало и никогда не использовалось в других целях.Результаты
COPY. Сообщение выдается при успешном выполнении команды COPY. NOTICE: ERROR. Ошибка — процедура копирования завершилась неудачей (с объяснением причины).Описание
Команда COPY используется для обмена данных между таблицами баз PostgreSQL и файлами в файловой системе. Существует два варианта команды: COPY TO и COPY FROM.
Команда COPY TO направляет все содержимое таблицы в файл или в стандартный вывод (то есть клиенту, подключенному к базе данных). Команда COPY FROM импортирует данные из стандартного файла или из стандартного ввода (то есть от клиента).
ПРИМЕЧАНИЕ
He путайте команду SQL COPY с командой psql \copy. Команда \copy выполняет операцию COPY FROM stdin или COPY TO stdout, при этом данные хранятся в файле, доступном для psql. Следовательно, права доступа к файлу определяются клиентом, а не серверным процессом postmaster.
За дополнительной информацией об этой команде обращайтесь к разделу «Добавление данных командами INSERT и COPY» в главе 4.
Обмен данными может производиться как в виде простого текста, так и в двоичном формате (ключевое слово BINARY). Двоичный формат значительно ускоряет копирование данных, но вследствие низкоуровневых различий в порядке следования байтов ухудшается адаптируемость приложения.
Ограничения
Использование команды COPY подчиняется нескольким ограничениям. Чтобы команда COPY могла читать данные из указанных таблиц, пользователю PostgreSQL должно быть предоставлено право выборки. Если команда COPY применяется для заполнения таблицы, пользователь PostgreSQL также должен обладать правами вставки или обновления.
С другой стороны, при записи или чтении данных из системного файла пользователь, запустивший сервер PostgreSQL (postmaster), должен обладать необходимыми правами доступа к заданному файлу. Чтобы обойти это ограничение, в psql можно воспользоваться командой \copy (см. главу 6).
При выполнении команды COPY TO проверяются ограничения и приводятся в действие триггеры, но не вызываются правила и не применяются значения полей по умолчанию.
При первой ошибке выполнение команды COPY прекращается. В случае команды COPY FROM это не должно вызывать проблем, но при использовании команды COPY ТО таблица может оказаться частично измененной. Последствия сбоя команды COPY ликвидируются командой VACUUM.
Формат файлов
Техническое описание формата двоичных выходных файлов PostgreSQL приведено в приложении В. Если вместо двоичного формата используется обычный текстовый формат, файл, созданный командой COPY, форматируется следующим образом: каждая запись выводится в отдельной строке, а поля ограничиваются заданным символом-разделителем. Все внутренние вхождения этого символа экранируются символом \, а значения полей выводятся в виде простых текстовых строк.
Если в команду COPY была включена секция WITH 01DS, в начале каждой записи выводится идентификатор объекта. Если выходной файл создавался с секцией WITH OIDS, то команда импортирования данных обратно в PostgreSQL также должна содержать секцию WITH OIDS. В противном случае идентификатор будет интерпретирован как обычное поле.
Передача результатов в стандартный вывод завершается специальной комбинацией: обратная косая черта (\), точка (.) и символ новой строки. При преждевременном достижении признака конца файла (EOF) вывод автоматически завершается.
Поскольку символ \ имеет несколько значений, для представления его в качестве литерала необходимо экранирование, поэтому в текстовых представлениях полей этот символ удваивается (\\). Перед импортированием внешних текстовых данных в PostgreSQL необходимо выполнить предварительное форматирование и заменить одиночные символы \ двойными.
Примеры
Следующая команда копирует содержимое таблицы employees в файл emp_table с разделением полей символом |:
booktown=# COPY employees TO '/tmp/employee_data' USING DELIMITERS '|';
COPY
Копирование данных из файла в таблицу publishers:
booktown=# COPY publishers FROM Vtmp/publisher_data';
COPY
CREATE AGGREGATE
Определение новой агрегатной функции в базе данных.
Синтаксис
CREATE AGGREGATE имя ( BASETYPE = входной_тип
[ , SFUNC = функция. STYPE = переходный_тип ]
[ , FINALFUNC = завершающая_функция ]
[ , INITCOND = начальное_состояние ] )
Параметры
имя. Имя создаваемой агрегатной функции. входной_тип. Тип входных данных, с которыми работает создаваемая функция. Если агрегатная функция игнорирует входные данные (как, например, функция count()), вместо типа данных указывается строковая константа ANY. функция. Имя функции, вызываемой для обработки всех входных данных, отличных от NULL. Обычно такая функция получает два аргумента: первый аргумент относится к типу данных переходный_тип, а второй — к типу данных входной_тип. Если агрегатная функция не анализирует входные данные, она получает только один аргумент типа переходный_тип. Так или иначе, функция должна возвращать значение типа переходный_тип. переходный_тип. Промежуточный тип данных агрегатной функции. завершающая_функция. Имя итоговой функции, вызываемой для вычисления результата агрегатной функции после обработки всех входных данных. Функция должна получать один аргумент типа переходный_тип. Выходной тип данных агрегатной функции определяется типом возвращаемого значения этой функции. Если параметр FINALFUNC не указан, то последнее переходное значение передается в качестве результата агрегатной функции, а выходной тип данных определяется типом переходный_тип. начальное_состояние. Начальное состояние промежуточного значения агрегатной функции. Задается литералом типа переходный_тип. Если параметр начальное_состояние не задан, промежуточное значение инициализируется псевдозначением NULL.Результаты
CREATE. Сообщение выдается при успешном создании агрегатной функции. ERROR: AggregateCreate: function "функция(переходпый_тип. входной_тип)" does not exist. Ошибка — заданная функция с двумя параметрами типов переходный_тип и входной_тип не существует. ERROR: AggregateCreate: function "функция(переходпый_тип)" does not exist. Ошибка — заданная функция с одним параметром типа переходный_тип не существует. Сообщение выдается только в том случае, если параметр входной_ тип равен ANY.Описание
Команда CREATE AGGREGATE предназначена для определения новых агрегатных функций в PostgreSQL Самые распространенные агрегатные функции (min(), avg(), max() и т. д.) принадлежат к числу стандартных функций PostgreSQL. За дополнительной информацией о стандартных функциях PostgreSQL обращайтесь к главе 5.
Агрегатные функции характеризуются в первую очередь типом входных данных. Допускается существование двух и более агрегатных функций с одинаковыми именами, вызываемых с разными типами данных (это называется перегрузкой функций).
ВНИМАНИЕ
Во избежание недоразумений не пытайтесь создавать обычные функции с такими же именами и типами входных данных, что и у агрегатных функций. При возникновении конфликта агрегатные функции обладают более высоким приоритетом.
Агрегатная функция состоит из одной или двух обычных функций: обязательной переходной функции (функция) и необязательной завершающей функции (завершающая _функция).
PostgreSQL использует временную переменную типа переходный_тип, которая обновляется переходной функцией для каждой входной записи. Если в команде CREATE AGGREGATE определена завершающая функция, то после обработки всех данных она будет вызвана для вычисления окончательного результата. В противном случае возвращается итоговое значение переходной переменной без дополнительной обработки.
Команда CREATE AGGREGATE также может задать исходное состояние внутренней переходной переменной; для этой цели используется ключевое слово INITCOND. PostgreSQL хранит это значение в базе данных в виде типа text, однако оно должно представлять константу переходного типа, заданного с ключевым словом STYPE. Если параметр не задан, он инициализируется псевдозначением NULL.
Если переходная функция была создана с атрибутом isstrict (см. описание команды CREATE FUNCTION), она не может вызываться с параметром NULL. Если переходная функция объявлена подобным образом, выполнение агрегатной функции отличается от обычного — все входные параметры NULL игнорируются, и функция для них не вызывается. При этом сохраняется предыдущее переходное значение, а агрегатная функция продолжает обработку входных данных.
Кроме того, если промежуточная переменная была инициализирована псевдозначением NULL, она заменяется первым входным значением, отличным от NULL, а переходная функция вызывается для второго входного значения, отличного от NULL. Эта особенность может пригодиться для создания агрегатных функций, аналогичных функции max(). Обратите внимание: это происходит только в том случае, если входпой_тип и переходный_гпип совпадают. Если типы различаются, вы должны либо передать начальное значение, отличное от NULL, либо использовать переходную функцию без атрибута isstrict.
Если переходная функция не объявлена с атрибутом isstrict, она вызывается для каждого входного значения без исключений. При этом у программиста появляется возможность самостоятельно организовать обработку псевдозначеннй NULL во входных данных.
Если завершающая функция объявлена с атрибутом isstrict, она не будет вызываться в том случае, если итоговое переходное состояние равно NULL; вместо этого автоматически выводится результат NULL.
Пример
В следующем примере определяется агрегатная функция sum О, работающая с текстовыми данными. Она вызывает встроенную функцию PostgreSQL textcat (text, text) для конкатенации всего текста во входных данных.
booktown=# CREATE AGGREGATE sum ( BASETYPE = text,
booktown(# SFUNC = textcat,
booktown(# STYPE = text,
booktown(# INITCOND = " );
CREATE
booktown=# SELECT sum(title || ' ') FROM books WHERE title - '^L';
sum
-----------------------------------
Little Women Learning Python
(1 row)
CREATE DATABASE
Создание новой базы данных в PostgreSQL.
Синтаксис
CREATE DATABASE база_данных
[ WITH [ LOCATION = { 'каталог' | DEFAULT } ]
[ TEMPLATE = шаблон DEFAULT ]
[ ENCODING = имя_нодировки | номер_кодировки | DEFAULT ] ]
Параметры
база_данных. Имя создаваемой базы данных. каталог. Каталог, в котором хранится база данных. С ключевым словом DEFAULT база сохраняется в каталоге по умолчанию, задаваемом переменной среды PGDATA (или ключом -D, переданным postmaster).ВНИМАНИЕ
В параметре каталог обычно передается имя системной переменной среды, значение которой описывает инициализированный каталог базы данных. Тем не менее, если при компиляции PostgreSQL использовалась команда gmake с ключом CPPFLAGS=-DALLOW_ABSOLUTE_DBPATHS, можно ввести полный, абсолютный путь.
шаблон. Имя шаблона, на основании которого создается новая база данных. Ключевое слово DEFAULT означает шаблон по умолчанию (обычно templatel). имя_кодировки | номер_кодировки. Расширенная кодировка, используемая базой данных. Задается в виде строкового литерала или в виде целочисленного номера, определяющего тип кодировки. Список типов расширенных кодировок в PostgreSQL приведен в приложении А. DEFAULT. Ключевое слово DEFAULT явно задает кодировку по умолчанию (используемую при отсутствии параметра ENCODING).Результаты
CREATE DATABASE. Сообщение выдается при успешном создании новой базы данных. ERROR: user "пользователь" is not allowed to create/drop database. Ошибка — пользователь PostgreSQL не обладает привилегией createdb, необходимой для создания базы данных. Администратор PostgreSQL предоставляет право создания баз данных пользователям при помощи команды ALTER USER. ERROR: Absolute paths are not allowed as database locations. Ошибка —в параметре LOCATION был указан абсолютный путь, а при компиляции PostgreSQL команда gmake вызывалась без ключа CPPFLAGS=-DALLOW_ABSOLUTE_DBPATHS. ERROR: Relative paths are not allowed as database locations. Ошибка — в параметре LOCATION указан относительный путь, а в PostgreSQL 7.1.x данная возможность не поддерживается. В этом параметре необходимо передать имя переменной среды, содержащей информацию о требуемом местонахождении базы данных. ERROR: createdb: database "база_данных" already exists. Ошибка — база данных с указанным именем уже существует в системном каталоге. ERROR: database path may not contain single quotes. Ошибка —в параметре каталог (местонахождение базы данных в файловой системе) используются апострофы ('), несовместимые с системной программой создания каталогов. ERROR: CREATE DATABASE: unable to create database directory '/каталог'. Ошибка — сохранение базы данных в заданном каталоге невозможно. Причины могут быть разными: переполнение диска, отсутствие прав доступа к заданному каталогу, отсутствие предварительной инициализации командами initdb или initlocation. Заданный каталог должен быть доступен для пользователя, под именем которого работает процесс postmaster. ERROR: CREATE DATABASE: May not be called in a transaction block. Ошибка-попытка вызова CREATE DATABASE в транзакционном блоке. Команда CREATE DATABASE не может использоваться в открытых блоках транзакций.Описание
Команда CREATE DATABASE создает в системе новую базу данных. Пользователь PostgreSQL, под именем которого вы зарегистрированы, автоматически становится владельцем новой базы данных, поэтому перед вызовом команды следует убедиться в том, что вы работаете под правильным именем.
ПРИМЕЧАНИЕ
При крайней необходимости владельца базы данных можно изменить, для чего с полем datdba системной таблицы pg_database выполняется команда UPDATE с системным идентификатором другого пользователя PostgreSQL.
Параметр LOCATION обычно описывает переменную среды, содержащую путь к каталогу, в котором создается новая база данных. Переменная должна существовать в рабочей среде пользователя, запустившего процесс postmaster. Таким образом администраторы могут лучше управлять тем, в каких каталогах создаются новые базы данных. За дополнительной информацией по этой теме обращайтесь к главе 9.
Каталог, выбранный для хранения базы данных, должен быть предварительно инициализирован командой initlocation (или initdb). Команды описаны в главе 9.
ПРИМЕЧАНИЕ
Если при компиляции PostgreSQL был установлен ключ ALLOW_ABSOLUTE_PATH (посредством передачи ключа CPPFLAGS=-DALLOW_ABSOLL)TE_PATH утилите groake после конфигурации), при создании базы данных можно указывать абсолютные пути. По умолчанию этот режим запрещен, что связано с проблемами безопасности и целостности данных, возникающими при создании баз данных в произвольных каталогах.
При создании новой базы данных PostgreSQL создает дубликат указанного шаблона (по умолчанию— tempiatel). Чтобы использовать а качестве шаблона другую базу данных, укажите ее имя в секции TEMPLATE. Чтобы создать пустую базу данных, не содержащую копию объектов шаблона, выберите шаблон template0.
Примеры
Следующая команда создает базу данных с именем booktown:
tempiatel=# CREATE DATABASE booktown;
CREATE DATABASE
В следующем фрагменте та же база данных создается в другом каталоге:
tempiatelHf CREATE DATABASE booktown WITH LOCATION = '/usr/local/pgsql/booktown';
CREATE DATABASE
CREATE FUNCTION
Определение новой функции в базе данных.
Синтаксис
CREATE FUNCTION имя ( [ тип_аргумента [. ...] ] )
RETURNS тип_возвращаемого_значения
AS 'определение'
LANGUAGE 'язык'
[ WITH ( атрибут [. ...] ) ]
CREATE FUNCTION имя ( [ тип_аргумента [. ...] ] )
RETURNS тип_возвращаемого_значения
AS 'объектный_файл' [ , 'иия_в_обьектном_файпе' ]
LANGUAGE 'язык'
[ WITH ( атрибут [. ..;] ) ]
Параметры
имя. Имя создаваемой функции. тип_аргумента. Тип данных аргумента (или аргументов), получаемого функцией. Существуют три категории типов данных: базовые типы, сложные типы и специальный тип opaque. Тип opaque разрешает передачу аргументов, не относящихся к допустимым типам SQL. Обычно он используется внутренними функциями, а также функциями, написанными на языках С, PL/pgSQL и т. д., в которых возвращаемое значение не относится к стандартным типам данных SQL. тип_возвращаемого_значения. Тип данных возвращаемого значения (или значений) функции. Может относиться к базовому типу, сложному типу, типу setof (обычный тип данных с префиксом setof) или типу opaque. Модификатор setof указывает на то, что функция возвращает несколько записей данных (по умолчанию возвращается только одна запись). Например, тип возвращаемого значения setof i nteger создает функцию, которая может возвращать несколько записей типа integer. атрибут. Необязательный атрибут функции. В PostgreSQL 7.1.x поддерживаются два атрибута, isstrict и iscacheable. определение. Определение создаваемой функции. Задается в виде строки, заключенной в апострофы, хотя ее содержимое в значительной мере зависит от языка программирования. В строке может передаваться внутреннее имя функции, команда SQL или процедурный код (в таких языках, как PL/pgSQL). объектный_файл [ , имя_в_объектном_файле ]. Файл, содержащий динамически загружаемый объектный код, и имя функции в исходном коде на языке С. Второй параметр обязателен лишь в том случае, если имя исходной функции на языке С отличается от имени функции SQL. язык. Название языка, на котором написана функция. Допустимыми значениями являются С, SQL, Internal или имя любого процедурного языка, поддержка которого была установлена командой CREATE LANGUAGE (например, plpgsql). За дополнительной информацией обращайтесь к описанию команды CREATE LANGUAGE.Результаты
CREATE. Это сообщение возвращается при успешном создании функции.
Описание
Команда CREATE FUNCTION создает новую функцию в текущей базе данных. Владельцем функции становится создавший ее пользователь PostgreSQL.
Атрибуты функции
iscachable. Атрибут означает, что функция всегда возвращает постоянный результат при одинаковых значениях аргументов (то есть вычисленные результаты могут кэшироваться). Такие функции не берут информацию из базы данных и не используют информацию, не включенную в список параметров. По значению атрибута оптимизатор узнает о том, что вместо повторного выполнения функции можно воспользоваться результатом предыдущего вызова с теми же аргументами. isstrict. Атрибут Isstrict означает, что в функции реализована жесткая проверка псевдозначений NULL. В этом случае при вызове с аргументом NULL функция не выполняет никаких действий и сразу возвращает результат NULL.Перегрузка функций
В PostgreSQL поддерживается перегрузка функций. Вероятно, этот термин хорошо знаком всем программистам, работающим с объектно-ориентированными языками — под ним понимается определение нескольких функций с одинаковыми именами при условии, что каждая из них получает уникальный набор аргументов.
Перегрузка создает видимость того, что одна функция работает с разными типами входных данных. С точки зрения пользователя серия функций превращается в универсальное и удобное средство для выполнения конкретной задачи.
В отличие от функций PostgreSQL, перегружаемых по типам аргументов, откомпилированные функции С, находящиеся в одном объектном файле, не могут обладать одинаковыми именами. Чтобы справиться с этой проблемой, присвойте второй функции С, которую вы собираетесь перегрузить в PostgreSQL, любое уникальное имя в исходном файле С, откомпилируйте и затем передайте это имя в параметре имя_в_объектном_файле при создании перегруженной функции.
Примеры
В следующем примере создается простая функция SQL, которая возвращает название книги по ее коду, переданному функции:
booktown=# CREATE FUNCTION title(integer) RETURNS text
booktown-# AS 'SELECT title from books where id - $1'
booktown-# LANGUAGE 'sql':
CREATE
Теперь функция title() может использоваться в базе данных booktown для выборки записей, у которых поле id совпадает с переданным числовым аргументом:
booktown=# SELECT title(41472) AS book_title;
book title
Practical PostgreSQL
(1 row)
CREATE GROUP
Создание новой группы PostgreSQL в базе данных.
Синтаксис
CREATE GROUP группа
[ WITH [ SYSID идентификатор_группы ]
[ USER пользователь [. ...]]]
Параметры
группа. Имя создаваемой группы. идентификатор_группы. Системный идентификатор, присваиваемый новой группе. Если идентификатор не задан, он вычисляется автоматически, для чего наибольший из существующих идентификаторов групп увеличивается на 1. пользователь. Пользователь (или разделенный запятыми список пользователей), автоматически включаемый в группу при создании. Пользователи должны быть заранее созданы в базе данных.Результаты
CREATE GROUP. Сообщение возвращается при успешном создании группы PostgreSQL. ERROR: CREATE GROUP: group name "группа" already exists. Ошибка—группа PostgreSQL с указанным именем уже существует в системе. ERROR: CREATE GROUP: permission denied. Ошибка — пользователь, пытающийся создать группу, не обладает правами суперпользователя. ERROR: CREATE GROUP: user "пользователь" does not exist. Ошибка — пользователь с именем, указанным в секции WITH USER, не существует.Описание
Суперпользователь базы данных создает новые группы PostgreSQL при помощи команды CREATE GROUP. Группа представляет собой объект базы данных системного уровня, которому можно назначать привилегии (команда GRANT) и включать в него пользователей. Привилегии, назначенные группе, автоматически распространяются на ее членов.
Модификация группы (а также списка пользователей, входящих в нее) осуществляется командой ALTER GROUP. Группы удаляются командой DROP GROUP.
Примеры
Следующая команда создает пустую группу с именем management:
booktown=# CREATE GROUP management;
CREATE GROUP
В следующем примере создается группа accounting, состоящая из двух пользователей, имена которых указаны в секции WITH USER:
booktown=# CREATE GROUP accounting WITH USER vincent, alien;
CREATE GROUP
CREATE INDEX
Создает индекс для таблицы.
Синтаксис
CREATE [ UNIQUE ] INDEX индекс ON таблица
[ USING тип ] ( поле [ класс ] [, ...] )
CREATE [ UNIQUE ] INDEX индекс ON таблица
[ USING тип ] ( функция ( поле [....])[ класс ] )
Параметры
UNIQUE. Необязательное ключевое слово UNIQUE. При его присутствии база данных автоматически проверяет наличие повторяющихся значений в поле (или группе полей), для которых создается индекс. Проверка происходит как при создании индекса, так и при каждом включении данных в таблицу. В дальнейшем PostgreSQL выдает ошибку при выполнении команд INSERT или UPDATE, в результате которых в индексе появляются повторяющиеся значения и команда завершается неудачей. индекс. Имя создаваемого индекса. таблица. Имя таблицы, в которой создается индекс. тип. Алгоритм, используемый при построении индекса. Допустимы три значения: btree — реализация на базе В-деревьев Лемана-Яо с высокой степенью параллельности; rtree — реализация па базе стандартных R-деревьев с использованием квадратичного разбиения по алгоритму Гуттмана; hash — реализация на базе алгоритмов линейного хэширования. поле. Поле (или разделенный запятыми список полей), по которому строится индекс. класс. Необязательный операторный класс. Для большинства пользователей этот параметр не важен. функция. Имя функции, вызываемой для заданных полей (вместо прямого индексирования данных). Указанная функция должна возвращать отдельное значение (не группу значений!), по которому и строится индекс.Результаты
CREATE. Сообщение возвращается при успешном создании индекса. ERROR: Cannot create Index: 'индекс' already exists. Ошибка— индекс с указанным именем уже существует. ERROR: DefineIndex: attribute "поле" not found. Ошибка — заданное иоле не существует в индексируемой таблице. ERROR: DefineIndex: relation "таблица" not found. Ошибка — заданная таблица не существует в подключенной базе данных.Описание
Команда CREATE INDEX строит оптимизирующий индекс по значениям одного или нескольких полей заданной таблицы. Следует помнить, что индексы предназначены для повышения эффективности и быстродействия операций с базами данных, однако в некоторых таблицах затраты на сопровождение индекса перевешивают выгоду от его использования. В этом случае индекс снижает общее быстродействие.
Построение индексов по полям
При создании «традиционного» индекса указывается список из одного или нескольких полей таблицы. Эта разновидность индексов используется запросами, напрямую работающими с индексируемыми полями в секции WHERE. Учтите, что индексы типов rtree и hash индексируют только одно поле, а индекс типа btree позволяет индексировать до 16 полей.
Функциональный индекс
К другой категории относятся функциональные индексы. Функциональный индекс строится по возвращаемому значению функции, примененной к одному пли нескольким полям. Такие индексы обычно используются запросами, часто вызывающими одну и ту же функцию в секции WHERE.
Например, если в секции WHERE некоторого запроса часто вызывается функция upper(last_name), этот запрос можно оптимизировать построением функционального индекса по результату функции upper(last_name).
Операторы и операторные классы
Оптимизатор запросов PostgreSQL при сравнении использует разные индексы для разных операторов. Тип индекса выбирается по списку, приведенному в табл. 14.1.
Таблица 14.1. Соответствие «оператор-индекс»
| Индекс | Оператор |
| В-дерево |
<, <=, >=, > |
| R-дерево |
«, &<, &>, », @, — , && |
| Хэш |
= |
Для каждого индексируемого поля можно задать операторный класс, который должен использоваться при индексации. Данная возможность существует только потому, что в некоторых обстоятельствах имеется несколько вариантов осмысленного упорядочения данных. Впрочем, типа индексации, принятого по умолчанию, обычно бывает достаточно, если только вы не занимаетесь разработкой собственных нестандартных типов и операторов.
Примеры
Следующая команда строит уникальный индекс по полю Id таблицы employees:
booktown=# CREATE UNIQUE INDEX employee_id_idx
booktown-# ON employees (id);
CREATE
В другом фрагменте по полю last_name таблицы employees строится функциональный индекс с использованием функции upper():
booktown=# CREATE INDEX employee_upper_name_idx
booktown-# ON employees (upper(last_name));
CREATE
CREATE LANGUAGE
Определение нового языка, используемого при создании функций.
Синтаксис
CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE 'язык'
HANDLER обработчик
LANCOMPILER 'комментарий'
Параметры
TRUSTED. Ключевое слово TRUSTED означает, что PostgreSQL разрешает непривилегированным пользователям обходить ограничения, связанные с наличием прав доступа к языку'. Если в процессе определения языка этот параметр не указывался, то использование языка для создания новых функций будет разрешено лишь суперпользователям. PROCEDURAL. Необязательное ключевое слово PROCEDURAL. Делает команду CREATE LANGUAGE более наглядной, но не влияет на ее работу. язык. Имя нового процедурного языка (без учета регистра символов). Переопределение имен существующих, встроенных языков PostgreSQL не допускается. HANDLER обработчик. Имя заранее определенной функции, вызываемой при выполнении процедур PL. комментарий. Строка, сохраняемая в поле lancompiler системной таблицы pg_language. Секция LANCOMPILER оставлена для обеспечения совместимости, не имеет практического смысла и может быть удалена в следующих версиях PostgreSQL. Тем не менее в версии 7.1.x ее присутствие остается обязательным.Результаты
CREATE. Сообщение выдается при успешном определении нового языка. ERROR: PL handler function обработчик() doesn't exist. Ошибка—функция, указанная в параметре HANDLER, не существует.Описание
Команда CREATE LANGUAGE загружает новый процедурный язык в подключенную базу данных. Она используется с процедурными языками, указанными с ключом - -with-язык при первоначальной установке PostgreSQL. Например, для включения поддержки pltcl следует передать ключ - -with-tcl в процессе конфигурирования PostgreSQL.
После выполнения этой команды вы сможете создавать новые функции на добавленном языке. Команда CREATE LANGUAGE может выполняться только суперпользователем.
В PostgreSQL 7.1.x (последней версии на момент написания книги) модификация созданных языков не поддерживается. Чтобы изменить определение процедурного языка, необходимо удалить его из базы данных командой DROP LANGUAGE и создать заново.
ПРИМЕЧАНИЕ
Если команда CREATE LANGUAGE создает язык в базе данных templatel, поддержка этого языка будет автоматически включаться во все базы данных, созданные на основе этого шаблона (используемого по умолчанию).
Чтобы процедурный язык мог использоваться в PostgreSQL, для него должен быть написан специальный обработчик вызовов, откомпилированный в двоичный формат. Следовательно, для написания обработчиков требуются только компилируемые языки (такие, как С и C++).
Обработчик вызовов также должен быть отдельно определен в PostgreSQL как функция, вызываемая без аргументов и возвращающая тип opaque. Подобное определение обработчика предотвращает непосредственный вызов функции в командах SQL.
Примеры
При выполнении команды CREATE LANGUAGE обработчик для соответствующего языка должен быть создан заранее. Регистрация процедурных языков начинается с создания функции, задающей местонахождение объектного кода обработчика. В следующем фрагменте создается обработчик, объектный код которого находится в файле /usr/local/pgsql/lib/libexample.so:
booktown=# CREATE FUNCTION example_call_handler ( ) RETURNS opaque
booktown-# AS '/usr/local/pgsql/lib/libexample.so'
booktown-# LANGUAGE 'C';
CREATE
На втором этапе командой CREATE LANGUAGE существующий язык загружается в подключенную базу данных. В следующем примере создается язык plexample, использующий обработчик из предыдущего примера:
booktown=# CREATE PROCEDURAL LANGUAGE 'piexample'
booktown-# HANDLER example_call_handler
booktown-# LANCOMPILER 'My Example':
CREATE
CREATE OPERATOR
Определение нового оператора в базе данных.
Синтаксис
CREATE OPERATOR оператор ( PROCEDURE = функция
[, LEFTARG = тип1 ]
[, RIGHTARG = тип2 ]
[, COMMUTATOR = коммутатор ]
[, NEGATOR = инвертор ]
[, RESTRICT = функция_ограничения ]
[, JOIN = функция_объединения ]
[, HASHES ]
[, SORT1 = левая_сортировка ]
[. SORT2 = правая_сортировка ] )
Параметры
оператор. Последовательность символов — идентификатор нового оператора. Список допустимых символов приведен ниже. функция. Функция, реализующая новый оператор. тип1. Тип левого аргумента. Не используется с унарными операторами, которые всегда располагаются слева от своего операнда. тип2. Тип правого аргумента. Не используется с унарными операторами, которые всегда располагаются справа от своего операнда. коммутатор. Оператор коммутации, связываемый с новым оператором. Коммутатором называется другой существующий оператор, который выполняет коммутацию в процедуре, определяемой для нового оператора (например, интерпретацию левого аргумента как правого или наоборот). инвертор. Оператор отрицания, связываемый с новым оператором. Инвертором называется другой существующий оператор, который выполняет логическую инверсию нового оператора. Инвертор определяется только в том случае, если результат применения ключевого слова NOT к новому оператору остается постоянным при одинаковых исходных данных. фупкция_ограничения. Имя оценочной функции размера. Функция должна существовать и возвращать вещественное значение, а тип данных ее аргументов должен соответствовать определению оператора. функция_одьединепия. Имя оценочной функции объединения. Функция должна существовать и возвращать вещественное значение, а тип данных ее аргументов должен соответствовать определению оператора. HASHES. Ключевое слово HASHES означает, что новый оператор поддерживает хэ-ширующие объединения. левая_сортировка. Оператор левосторонней сортировки (если новый оператор поддерживает слияние). правая_сортировка. Оператор правосторонней сортировки (если новый оператор поддерживает слияние).Результаты
CREATE. Это сообщение выдается при успешном создании нового оператора.
Описание
Команда CREATE OPERATOR предназначена для определения новых операторов. Пользователь PostgreSQL, создавший оператор, становится владельцем этого оператора в случае успешного создания.
Оператор может содержать до 31 символа из следующего списка:
+ - * / < > = ~ ! (а # % - & | - ? $
Кроме того, для символов оператора устанавливается ряд дополнительных ограничений:
знак доллара ($) может использоваться только в том случае, если оператор состоит из нескольких символов (оператор $ не разрешен); комбинации - - и /* не могут присутствовать в операторе, поскольку они интерпретируются как начало комментария; операторы, состоящие из нескольких символов, могут оканчиваться знаком + (плюс) или - (дефис) только в том случае, если содержат по крайней мере один из следующих символов:- ! @ # % Л & | ? ? $
Благодаря наличию этих ограничений PostgreSQL может обрабатывать синтаксически правильные запросы, в которых лексемы не нужно разделять дополнительными пробелами.
При создании оператора обязательно должен быть задан по крайней мере один из параметров LEFTARG и RIGHTARG (поскольку при вызове оператора передается хотя бы один аргумент). Если определяется бинарный оператор (с левым и правым операндами), должны быть заданы оба параметра, LEFTARG и RIGHTARG. При определении правого унарного оператора задается только параметр LEFTARG, а при определении левого унарного оператора — только параметр RIGHTARG.
ПРИМЕЧАНИЕ
Количество аргументов функции, указываемой в параметре PROCEDURE, должно соответствовать количеству операндов у создаваемого оператора.
Чтобы оптимизатор запросов правильно изменял порядок следования операндов, он должен знать, какой оператор следует использовать для коммутации. Для некоторых операторов коммутатор должен существовать (или по крайней мере, это было бы вполне логично). Например, коммутатором оператора > является оператор <, и взаимосвязь этих операторов логически оправдана. При наличии таких данных порядок следования операндов легко изменить и превратить конструкцию х<у в у>х, если оптимизатор запросов сочтет новый вариант более эффективным.
В процессе оптимизации запросов может использоваться не только коммутатор, но и инвертор. Так, для оператора = инвертором является оператор !=. Наличие инвертора позволяет оптимизатору запросов упрощать команды вида
booktown=# SELECT * FROM employees WHERE NOT name = 'John';
В упрощенном виде эта команда выглядит так:
booktown=# SELECT * FROM employees WHERE name != 'John';
Строго говоря, коммутаторы и инверторы могут задаваться еще до их фактического определения. Таким образом, появляется возможность определения взаимных ссылок в операторах. Будьте внимательны и не забудьте своевременно создать оператор, соответствующий определению коммутатора или инвертора.
Параметр HASHES сообщает оптимизатору запросов, что оператор поддерживает алгоритм хэширующего объединения (используемый только в том случае, если оператор представляет проверку равенства, при которой равенство подразумевает совпадение двоичных представлений). Еще два параметра, SORT1 и SORT2, сообщают оптимизатору о том, что соответственно левые и правые операторы поддерживают сортировку слиянием. Операторы сортировки задаются только для оператора равенства и представляются знаками < и > для левого и правого типа данных соответственно.
Параметры RESTRICT и JOIN определяют функции, при помощи которых оптимизатор запросов предварительно оценивает размер итоговых наборов. При определенных условиях PostgreSQL требуется определить количество результатов, получаемых в результате выполнения запроса; в таких случаях оптимизатор запросов вызывает функцию функция_ограничения с аргументами, переданными оператору; возвращаемое значение и является предварительной оценкой. Функция ограничения должна быть заранее определена командой CREATE FUNCTION с правильной сигнатурой.
Функция, заданная параметром JOIN, предназначена для оценки размеров объединения в том случае, если операнды не являются постоянными величинами. Вещественное число, возвращаемое функцией, определяет примерный объем результата.
Пример
В следующем фрагменте определяется логический оператор, который выясняет, равен ли нулю хотя бы один из двух операндов типа Integer. Проверка осуществляется функцией 1 s_zero (определение этой функции и дополнительная информация приведены в главе 7).
booktown=# CREATE OPERATOR !# (PROCEDURE = is_zero,
booktown(# LEFTARG = integer,
booktown(# RIGHTARG = integer);
CREATE
СREATE RULE
Определение нового правила в таблице.
Синтаксис
CREATE RULE правило AS ON событие ТО объект
[ WHERE условие ] DO [ INSTEAD ] операция
операция ::= NOTHING | query | ( query [; ...] ) | [ query [: ...] ]
Параметры
правило. Имя создаваемого правила. событие. Событие, при наступлении которого проверяется правило. Допустимыми значениями параметра являются SELECT, UPDATE, DELETE и INSERT. объект. Имя таблицы или уточненное имя поля (в формате таблица. поле). условие. Логическое выражение SQL, определяющее критерий применения правила. В условии не должны содержаться ссылки на другие таблицы, однако в нем могут присутствовать ссылки на специальные отношения new и old, которые соответственно представляют новые и существующие записи. INSTEAD. Ключевое слово INSTEAD означает, что заданная операция выполняется вместо события. Обычно операция выполняется перед наступлением события. операция. Запрос (или запросы), который определяет действия, выполняемые при наступлении указанного события. Возможно указание любых синтаксически правильных команд SELECT, UPDATE, DELETE и INSERT. Перечисляемые запросы заключаются в круглые скобки.В параметрах условие и операция могут использоваться специальные отношения new и old, при помощи которых можно получить доступ к новым и старым значениям полей объекта, указанного в определении правила.
Отношение new доступно в правилах INSERT и UPDATE; в нем содержатся значения вставляемых или обновляемых полей. Отношение old доступно в правилах SELECT, UPDATE и DELETE; в нем содержатся данные существующих записей, участвующих в операции выборки, обновления или удаления.
Результаты
CREATE. Это сообщение выдается при успешном создании нового правила.
Описание
Команда CREATE RULE создает в таблицах правила, предназначенные для выполнения альтернативных действий при вставке, обновлении и удалении данных. Система правил PostgreSQL также может использоваться при реализации представлений таблиц.
При выполнении команды SELECT, INSERT, DELETE и UPDATE автоматически производится поиск правил для этого события (порядок вызова правил не гарантирован). Если правило содержит секцию WHERE, проверяется указанное в ней условие, и если условие истинно — выполняется операция, заданная в определении правила. Если в определении правила присутствует ключевое слово INSTEAD, операция выполняется вместо обрабатываемого события; в противном случае она выполняется перед наступлением события. Будьте внимательны и избегайте циклических правил, то есть правил, в которых присутствуют ссылки на другие правила, которые, в свою очередь, ссылаются на исходное правило.
ПРИМЕЧАНИЕ
При использовании правил ON SELECT ключевое слово INSTEAD является обязательным. Фактически это означает, что правила ON SELECT всегда обеспечивают правильную реализацию представлений таблиц.
Пример
В следующем фрагменте приведено определение правила sync_stock_with_editions, автоматически обновляющего поле isbn таблицы stock при модификации таблицы editions.
booktown=f CREATE RULE sync_stock_with_editions AS
booktown-# ON UPDATE TO editions
booktown-# DO UPDATE stock SET isbn = new.isbn
booktown-# WHERE isbn = old.isbn;
CREATE
CREATE SEQUENCE
Создание нового генератора числовой последовательности.
Синтаксис
CREATE SEQUENCE последовательность [ INCREMENT приращение ]
[ MINVALUE минимум ] [ MAXVALUE максимум ]
[ START начало ] [ CACHE кэш ][ CYCLE ]
Параметры
последовательность. Имя создаваемой последовательности. приращение. Числовое изменение текущего значения последовательности. При положительном приращении генерируется возрастающая, а при отрицательном — убывающая последовательности. минимум. Минимальное допустимое значение, генерируемое новой последовательностью. По умолчанию равно 1 для возрастающих последовательностей или -2 147 483 647 для убывающих последовательностей. максимум. Максимальное допустимое значение, генерируемое новой последовательностью. По умолчанию равно 2 147 483 647 для возрастающих последовательностей или -1 для убывающих последовательностей. начало. Начальное значение последовательности. По умолчанию последовательность начинается с минимума для возрастающих последовательностей или с максимума для убывающих последовательностей. кэш. Количество элементов последовательности, кэшируемых в памяти. Увеличение размера кэша до величины, большей 1, повышает быстродействие, поскольку некоторые запросы новых элементов последовательности могут предоставляться из кэша. По умолчанию размер кэша равен 1, при этом последовательность генерируется по одному элементу (то есть кэширование не используется). Чтобы включить кэширование, передайте в параметре CACHE команды CREATE SEQUENCE значение, большее 1. CYCLE. При наличии ключевого слова CACHE по достижении максимума или минимума последовательность автоматически переходит к противоположной границе интервала, от которой и генерируются следующие элементы. Направление перехода зависит от того, является последовательность возрастающей или убывающей.Результаты
CREATE. Сообщение выдается при успешном создании новой последовательности. ERROR: Relation 'последовательность' already exists. Ошибка — последовательность с заданным именем уже существует. ERROR: DefineSequence: START (начало) can't be >= MAXVALUE (максимум). Ошибка — начальное значение последовательности больше допустимого максимума. ERROR: DefineSequence: START (начало) can't be < MINVALUE (минимум). Ошибка — начальное значение последовательности меньше допустимого минимума. ERROR: DefineSequence: MINVALUE (минимум) can't be >= MAXVALUE (максимум). Ошибка — нижняя граница интервала больше верхней.Описание
Команда CREATE SEQUENCE создает новый генератор числовой последовательности в базе данных.
Примеры
Пример создания последовательности с именем shipments_ship_id_seq:
booktown=# CREATE SEQUENCE shipments_ship_id_seq
booktown-# START 2000 INCREMENT 1;
CREATE
После того как последовательность создана, для получения ее следующего элемента можно воспользоваться функцией nextval ():
booktown=# SELECT nextval ('shipments_ship_id_seq');
nextval
200
(1 row)
Последовательности часто используются в команде INSERT:
booktown=# INSERT INTO shipments VALUES
booktown-# (nextval('shipments_ship_id_seq'),
booktown-# 107. '0394800753', 'now');
CREATE TABLE
Создание новой таблицы.
Синтаксис
CREATE [ TEMPORARY | TEMP ] TABLE таблица (
{ поле тип [ограничение_поля [... ] ] | ограничение_таблицы }
[. ... ]
)
[ INHERITS (базовая_таблица [,...])]
ограничение_поля ::=
[ CONSTRAINT имя_ограничения_поля ]
{ NOT NULL | UNIQUE | PRIMARY KEY |
DEFAULT значение |
CHECK ( условие ) |
REFERENCES внешняя_таблица [ ( внешнее_поле ) ]
[ MATCH FULL | MATCH PARTIAL ]
[ ON DELETE операция ]
[ ON UPDATE операция ]
[ DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
}
ограничение_та6лицы ::=
[ CONSTRAINT имя_ограничения_таблицы ]
{ UNIQUE ( поле [. ...] ) |
PRIMARY KEY ( поле [. ...] ) |
CHECK ( условие ) |
FOREIGN KEY ( поле [. ... ] ) |
REFERENCES внешняя_таблица [ ( внешнее_поле [,...])]
[ MATCH FULL | MATCH PARTIAL ]
[ ON DELETE операция ]
[ ON UPDATE операция ]
[ DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
}
операция ::= { NO ACTION | RESTRICT | CASCADE | SET NULL | SET DEFAULT }
Параметры
TEMPORARY TEMP. Признак временной таблицы. Таблица, созданная с ключевым словом TEMPORARY или TEMP, автоматически уничтожается в конце текущего сеанса. Все конструкции уровня таблицы (например, индексы и ограничения) уничтожаются в конце сеанса вместе с таблицей. Если имя временной таблицы совпадает с именем существующей таблицы, все ссылки на таблицу с этим именем на протяжении сеанса будут относиться к временной таблице. Иногда это вызывает проблемы, поскольку временная таблица косвенно замещает существующую таблицу в контексте текущего сеанса до момента ее уничтожения. таблица. Имя создаваемой таблицы. поле. Имя поля в новой таблице. Перечисляемые имена полей указываются в круглых скобках и разделяются запятыми. тип. Сразу же после имени пользователя указывается его тип — стандартный тип или массив одного из стандартных типов. ограничение_поля. Полное определение ограничения для данного поля. Ниже перечислены параметры ограничений полей. имя_ограничения_поля. Необязательное имя, присвоенное ограничению. NULL. Ключевое слово NULL разрешает, чтобы в поле хранилось псевдозначение NULL. Действует по умолчанию. NOT NULL. Поле не может содержать псевдозначение NULL. Ограничение NOT NULL эквивалентно ограничению CHECK (поле NOT NULL). UNIQUE. Ограничение гарантирует, что поле содержит только уникальные значения (без дубликатов). Автоматически устанавливается при создании уникального индекса для поля. PRIMARY KEY. Поле назначается первичным ключом таблицы и используется другими таблицами для однозначной идентификации записей. Ограничение первичного ключа эквивалентно установке ограничений UNIQUE и NOT NULL. DEFAULT. Значение по умолчанию, используемое в том случае, если значение поля не было указано в команде INSERT. Если значение по умолчанию не задано, поле заполняется псевдозначением NULL. CHECK. Значения поля проверяются на соответствие заданному условию. Если при выполнении команды INSERT или UPDATE условие не выполняется, операция вставки/модификации завершается неудачей. условие. Произвольное выражение, результатом которого является логическая величина. Указывается после секции CHECK. REFERENCES. Входные значения ограничиваемого поля сравниваются со значениями другого поля в заданной таблице (за дополнительной информацией о создании и использовании этого ограничения обращайтесь к главе 7). внешняя_таблица. Имя таблицы, используемой для проверки в ограничении внешнего ключа. внешнее_поле. Имя поля внешней таблицы, используемого для проверки в ограничении внешнего ключа. Поле должно принадлежать существующей таблице. Если имя поля не задано, используется первичный ключ заданной таблицы. MATCH FULL | MATCH PARTIAL. Секция MATCH указывает, разрешается ли смешивание псевдозначений NULL и «обычных» значений (отличных от NULL) при вставке в таблицу, у которой внешний ключ ссылается на несколько полей. Таким образом, на практике секция MATCH приносит пользу лишь в ограничениях таблиц, хотя формально она может использоваться и при ограничении полей. Конструкция MATCH FULL запрещает вставку данных, у которых часть полей внешнего ключа содержит NULL (кроме случая, когда NULL равны все поля). В PostgreSQL 7.1.x конструкция MATCH PARTIAL не поддерживается. Если секция MATCH отсутствует, считается, что поля NULL удовлетворяют ограничению. ON DELETE. При выполнении команды DELETE для заданной таблицы с ограничиваемым полем выполняется одна из следующих операций: NO ACTION -если удаление приводит к нарушению целостности ссылок, происходит ошибка (используется по умолчанию, когда операция не указана); RESTRICT — аналогично NO ACTION; CASCADE — удаление всех записей, содержащих ссылки на удаляемую запись, поэтому при каскадном удалении необходимо действовать очень осторожно; SET NULL — поля, содержащие ссылки на удаляемую запись, заменяются псевдозначениями NULL; SET DEFAULT — полям, содержащим ссылки на удаляемую запись, присваивается значение по умолчанию. ON UPDATE. При выполнении команды UPDATE для заданной таблицы выполняется одна из операций, описанных выше. По умолчанию также используется значение NO ACTION. При выборе операции CASCADE все записи, содержащие ссылки па обновляемую запись, обновляются новым значением (вместо удаления, как в случае с ON DELETE CASCADE). DEFERRABLE | NOT DEFERRABLE. Значение DEFERRABLE позволяет отложить выполнение ограничения до конца транзакции (вместо немедленного выполнения после выполнения команды). Значение NOT DEFERRABLE означает, что ограничение всегда проверяется сразу же после выполнения очередной команды. В этом случае пользователь не может отложить проверку ограничения до конца транзакции. По умолчанию выбирается именно этот вариант. INITIALLY DEFERRED | INITIALLY IMMEDIATE. Значение INITIALLY DEFERRED откладывает проверку ограничения до конца транзакции, а при установке значения INITIALLY IMMEDIATE проверка производится после каждой команды. По умолчанию используется значение INITIALLY IMMEDIATE. ограничение_та6лицы. Полное определение ограничения для создаваемой таблицы. Ограничения таблиц могут распространяться на несколько полей, тогда как ограничение поля всегда создает ограничение только для одного поля. Ниже перечислены параметры ограничений таблиц. имя_ограничения_таблицы. Необязательное имя, присвоенное ограничению. поле [. ...]. Имя поля (или разделенный запятыми список полей), для которых устанавливается ограничение таблицы. PRIMARY KEY | UNIQUE. Ключевые слова, при наличии которых для заданных полей автоматически строится индекс. UNIQUE означает, что комбинация значений полей, перечисленных за ключевым словом UNIQUE, принимает только уникальные значения. Попытки вставки новых записей, у которых значение поля (или комбинация значений полей) не является уникальной величиной, завершаются неудачей. Ключевое слово PRIMARY KEY обеспечивает проверку уникальности и запрещает присутствие псевдозначений NULL среди значений заданного поля (или полей). CHECK ( условие ). Команда INSERT или UPDATE завершается успешно лишь при выполнении заданного условия (выражения, возвращающего логический результат). Если условие не выполняется (то есть выражение оказывается ложным), записи не добавляются и не модифицируются. FOREIGN KEY. Ограничение внешнего ключа. Входные значения ограничиваемого поля сравниваются со значениями другого поля в заданной таблице (за дополнительной информацией о создании и использовании этого ограничения обращайтесь к главе 7). Синтаксис части, следующей за секцией FOREIGN KEY, идентичен синтаксису секции REFERENCES для ограничений полей. базовая_та6лица. Имя таблицы, от которой новая таблица наследует поля. Если имена унаследованных полей совпадают с именами полей, ранее включенных в структуру таблицы, PostgreSQL выдает сообщение об ошибке и прерывает выполнение команды.Результаты
CREATE. Сообщение выдается при успешном создании новой таблицы. ERROR: Relation 'таблица' already exists. Ошибка — таблица с заданным именем уже существует. ERROR: CREATE TABLE: attribute "поле" duplicated. Ошибка — поле включено в список дважды. ERROR: Unable to locate type name 'тип' in catalog. Ошибка — заданный тип поля не существует. ERROR: Illegal class name 'таблица'. Ошибка — имя таблицы начинается с префикса pg_.Описание
Команда CREATE TABLE создает новую таблицу в текущей базе данных. Только что созданная таблица не содержит данных, а ее владельцем является пользователь, выполнивший команду CREATE TABLE.
В определении новой таблицы перечисляются все имена и типы полей (кроме полей, унаследованных от базовой таблицы). Имя таблицы может иметь длину до 31 символа и автоматически преобразуется к нижнему регистру, если оно не заключено в кавычки. Поле может относиться к стандартному типу данных (например, int4, char) или содержать массив (описывается стандартным типом данных с квадратными скобками — например, float4[]).
Для полей создаваемой таблицы могут устанавливаться разнообразные ограничения. Например, при установке ограничения NOT NULL поле не может содержать псевдозначений NULL.
ВНИМАНИЕ
Имена таблиц не могут совпадать с именами существующих типов данных и таблиц системного каталога. Более того, они даже не могут начинаться с префикса pg_, зарезервированного для системных таблиц.
Максимальное количество полей в таблице равно примерно 1600. С учетом проблем, связанных с размером записей, на практике это число оказывается несколько меньшим.
Дополнительная информация о создании таблиц приведена в главе 4. За информацией об ограничениях полей и таблиц обращайтесь к главе 7.
Пример
Следующая команда создает таблицу с именем shipments и устанавливает для поля id ограничения NOT NULL и DEFAULT.
booktown=# CREATE TABLE shipments (
booktown(# id integer NOT NULL DEFAULT nextval ('shipments_ship_id_seq').
booktown(# customer_id integer,
booktown(# isbn text,
booktown(# ship_date timestanp);
CREATE
CREATE TABLE AS
Создание новой таблицы по результатам выборки.
Синтаксис
CREATE TABLE таблица [ (поле [. ...] ) ]
AS выборка
Параметры
таблица. Имя создаваемой таблицы. поле. Имя поля в создаваемой таблице; при перечислении нескольких полей их имена разделяются запятыми. Количество полей в таблице должно совпадать с количеством полей, возвращаемых в результате выборки. выборка. Синтаксически правильная команда SELECT. Количество полей должно соответствовать количеству полей в необязательном списке, предшествующем секции AS.Результаты
SELECT. Сообщение возвращается при успешном создании и вставке данных в таблицу. ERROR. CREATE TABLE/AS SELECT has mismatched column count. Ошибка — количество полей в списке, заключенном в круглые скобки, отличается от количества полей, возвращаемых командой SELECT.Описание
Команда CREATE TABLE AS создает таблицу по записям, входящим в итоговый набор, например по результатам запроса к таблице, существующей в базе данных. Типы и содержимое полей новой таблицы определяются параметрами команды SELECT.
В PostgreSQL 7.1.x при явном перечислении полей в круглых скобках не допускается использование символа * в команде SELECT.
Пример
Следующая команда создает резервную копию всего содержимого таблицы books и сохраняет ее в таблице book_backup.
booktown=# CREATE TABLE book_backup
booktown-# AS SELECT * FROM books;
SELECT
CRЕАТЕ TRIGGER
Создание нового триггера.
Синтаксис
CREATE TRIGGER триггер { BEFORE | AFTER } { событие [ OR событие ... ] }
ON таблица
FOR EACH { ROW STATEMENT }
EXECUTE PROCEDURE функция ( аргументы )
Параметры
триггер. Имя создаваемого триггера. таблица. Имя таблицы, с которой ассоциируется триггер. событие. Событие, по которому срабатывает триггер. Допустимые значения — INSERT, DELETE и UPDATE. Триггер может быть связан с несколькими событиями. функция. Имя функции, связываемой с новым триггером. Функция вызывается при срабатывании триггера; в определении должен быть указан тип возвращаемого значения opaque — этот тип используется только внутренними функциями, которые не могут напрямую вызываться в командах SQL. аргументы. Строка аргументов, передаваемая функции при срабатывании триггера.Результаты
CREATE. Это сообщение выдается при успешном создании нового триггера.
Описание
Команда CREATE TRIGGER создает триггер в базе данных. Триггер ассоциируется с таблицей, указанной в секции ON. При срабатывании триггера автоматически выполняется заданная функция.
ПРИМЕЧАНИЕ
Триггеры могут создаваться только владельцами таблиц или суперпользователями.
При создании триггера необходимо выбрать время срабатывания (до или после попытки выполнения некоторой операции). Если триггер срабатывает до заданного события (BEFORE), он может отменить выполнение операции для текущей записи или изменить данные, заносимые в таблицу. Если триггер срабатывает после заданного события (AFTER), он будет знать обо всех изменениях, внесенных в результате события.
Примеры
Следующая команда определяет триггер, вызываемый при обновлении существующей записи в таблице authors:
booktown=# CREATE TRIGGER sync_authors_books
Dooktown-# BEFORE UPDATE
booktown-# ON authors
booktown-# FOR EACH ROW
booktown-# EXECUTE PROCEDURE sync authors and booksO;
CREATE
Функция PL/pgSQL sync_authors_and_books () обновляет значение поля author_id таблицы books при обновлении ноля id в таблице authors. Тем самым поддерживается синхронизация таблиц books и authors. Аналогичного эффекта можно было бы добиться при помощи ограничения FOREIGN KEY (см. главу 7).
CREATE TYPE
Определение нового типа данных в базе.
Синтаксис
CREATE TYPE тип ( INPUT = входная_функция, OUTPUT = входная_функция
, INTERNALLENGTH = { внутренний_размер | VARIABLE }
[ , EXTERNALLENGTH - { внешний_размер | VARIABLE } ]
[ , DEFAULT = "значение_по^умолчанию" ]
[ , ELEMENT = элемент ] [ . DELIMITER = разделитель ]
[ , SEND = функция_отправки ] [ , RECEIVE = функция_получения ]
[ , PASSEDBYVALUE ]
[ , ALIGNMENT = выравнивание ]
[ , STORAGE = хранение ] )
Параметры
тип. Имя нового типа. Максимальная длина имени равна 30 символам. Имена типов должны быть уникальными в рамках базы данных и не могут начинаться с символа подчеркивания (зарезервирован для типов косвенно определяемых массивов). внутренний_размер. Внутренний размер определяемого типа (в байтах). внешний_размер. Внешний размер определяемого тина (в байтах). входная_фунщия. Имя входной функции определяемого типа. Функция должна быть заранее определена командой CREATE FUNCTION и должна преобразовывать внешнее представление типа во внутреннее представление. выходная_фуикция. Имя выходной функции определяемого типа. Функция должна преобразовывать внутреннее представление типа во внешнее представление (форму, предназначенную для вывода). элемент. Тип данных отдельного элемента массива, адресуемого данным типом (при самостоятельной реализации типов для хранения массивов). Параметр элемент должен быть типом данных фиксированного размера. разделитель. Разделитель значений для косвенно определяемых массивов, связанных с типом (тип[]). значение_по_умолчанию. Значение по умолчанию для нового типа данных. Если значение не указано, по умолчанию поля заполняются либо величиной, указанной в ограничении DEFAULT уровня таблицы, либо псевдозначениямп NULL. функция_отправки. Имя функции отправки для определяемого типа. Функция преобразует данные в форму, подходящую для передачи на другой компьютер, однако в PostgrcSQL 7.1.x такая возможность не поддерживается, поэтому параметр не используется. функция_получения. Имя функции получения для определяемого типа. Функция получает данные в форме, сгенерированной функцией отправки, и восстанавливает но ней внутреннее представление типа. В PostgreSQL 7.1 .х такая возможность не поддерживается, поэтому параметр не используется. PASSEDBYVALUE. Необязательное ключевое слово PASSEDBYVALUE означает, что операторы п функции, использующие определяемый тип данных, должны передавать соответствующие аргументы по значению, а не по ссылке (как происходит по умолчанию). Параметр не может использоваться с типами, внутреннее представление которых занимает более 4 байт. выравнивание. Способ выравнивания определяемого типа в памяти. Допустимые значения — char, int2, int4 и doubl e. Если параметр не задан, по умолчанию выбирается значение int4. хранение. Принцип храпения значений определяемого типа. Допустимые значения — plain, external, extended и main. Если параметр не задан, по умолчанию выбирается значение plain.Результаты
CREATE. Это сообщение выдается при успешном создании типа.
Описание
Команда CREATE TYPE предназначена для регистрации новых пользовательских типов в текущей базе данных. Пользователь PostgreSQL, выполнивший команду, становится владельцем нового типа.
При создании нового типа указываются две пользовательские функции, написанные на С, — входная и выходная функции определяемого типа данных. Входная функция преобразует внешнее представление типа во внутреннее представление, которое используется системными объектами, связанными с этим типом. Выходная функция преобразует внутреннее представление во внешнее.
Обе функции, входная и выходная, вызываются с одним аргументом типа opaque. Выходная функция должна возвращать тип opaque, тогда как входная функция возвращает значение определяемого типа. Обратите внимание: функции должны быть определены до создания типа.
Тип определяется с фиксированным или с переменным размером. Если вы хотите создать тип фиксированного размера, присвоите параметру INTERNALLENGTH нужное числовое значение (в байтах). Если тип определяется с переменным размером, используйте ключевое слово VARIABLE вместо параметра INTERNALLENGTH; в этом случае размер будет определяться по тем же правилам, что и для типа text. Внешний размер (EXTERNALLENGTH) задается по тому же принципу — в виде числового значения или с ключевым словом VARIABLE.
После создания нового типа PostgreSQL автоматически определяет тип массива для нового типа данных. Во внутреннем представлении этому типу присваивается имя_тип (с начальным символом подчеркивания). Все ссылки вида тип[] автоматически преобразуются к внутреннему типу массива (_тип).
Параметр DELIMITER позволяет задать символ-разделитель для массивов. Этот символ используется для разделения элементов массивов в константах, передаваемых PostgreSQL (например, {1, 2, 3}), а также при разделении элементов во внешнем отображении массивов этого типа. По умолчанию в качестве разделителя используется запятая.
Если вы предпочитаете смоделировать массив вручную, передайте функции PostgreSQL значения array_in и array_out в качестве входной и выходной функций соответственно, затем при помощи ключевого слова ELEMENT задайте типы данных элементов массива.
Ключевое слово DEFAULT задает значение по умолчанию, действующее при вставке в новое поле, на уровне системы. Обычно при отсутствии значения и ограничений DEFAULT поля инициализируются псевдозначением NULL. Учтите, что в PostgreSQL 7.1.x задается внутреннее представление значения по умолчанию.
Параметр выравнивание определяет принцип выравнивания нового типа данных в памяти. Для типов данных с переменным внутренним размером должно быть выбрано значение int4 или double.
Параметр храпение определяет метод хранения данных. Для типов данных с фиксированным внутренним размером может задаваться только тип plain, а для типов с переменной внутренней длиной допустимы значения plain, extended, external или main.
При выборе метода plain данные хранятся в несжатом, «буквальном» представлении, максимальный размер со ставляет 8 Кбайт. Метод extended позволяет превысить порог и работать со значениями большего размера, а также хранить данные вне физической таблицы с использованием расширения PostgreSQL TOAST (The Oversized Attribute Storage Technique), разработанного Томом Лейном (Tom Lane).
Метод external обладает определенным сходством с методом extended, но не пытается сжимать данные перед тем, как использовать расширение TOAST для хранения данных вне физических таблиц. Метод mai n тоже похож на extended — он также поддерживает сжатие и расширение TOAST, но ориентирован на физическое хранение данных в главной таблице и выбирает другой способ только в том случае, если не остается иного выбора.
Пример
В следующем примере создается новый числовой тип данных с именем zero, всегда представляющий нулевое значение. Сначала мы создаем входную и выходную функции, а затем определяем тип данных путем ссылок на эти функции.
booktown=# CREATE FUNCTION zero_out(opaque) RETURNS opaque
booktown-# AS '/usr/local/pgsql/lib/zero.so' LANGUAGE 'C';
CREATE
booktown=# CREATE FUNCTION zero_in(opaque) RETURNS zero
booktown-# AS '/usr/local/pgsql/lib/zero.so' LANGUAGE 'C';
NOTICE: ProcedureCreate: type 'zero' is not yet defined
CREATE
booktown=# CREATE TYPE zero (internallength = 16,
booktown(# input = zero_in, output = zero_out);
CREATE
CREATE USER
Создание нового пользователя базы данных PostgreSQL
Синтаксис
CREATE USER пользователь
[ WITH [ SYSID uid ]
[ PASSWORD 'пароль' ] ]
[ CREATEDB | NOCREATEDB ]
[ CREATEUSER | NOCREATEUSER ]
[ IN GROUP группа [. ...] ]
[ VALID UNTIL 'срок' ]
Параметры
пользователь. Имя создаваемого пользователя. uid. Системный идентификатор создаваемого пользователя. Если команда CREATE USER выполняется без параметра SYSID, идентификатор пользователя назначается автоматически. пароль. Пароль нового пользователя PostgreSQL. Если в конфигурации базы данных установлена парольная аутентификация, подключение к пей возможно лишь при наличии пароля у пользователя. CREATEDB | NOCREATEDB. Право создания новых баз данных. Ключевое слово CREATEDB разрешает пользователю создавать новые базы данных, а ключевое слово NOCREATEDB явно указывает на отсутствие этого права (по умолчанию право создания баз данных не предоставляется). CREATEUSER NOCREATEUSER. Привилегия суперпользователя. Ключевое слово CREATEUSER разрешает пользователю выполнять команды CREATE USER и DROP USER, а также наделяет его привилегиями суперпользователя с неограниченными правами во всех базах данных. По умолчанию используется ключевое слово NOCREATEUSER. группа. Необязательное имя группы, в которую автоматически включается пользователь. срок. Срок истечения действия пароля. При достижении заданного момента пароль автоматически становится недействительным. Если параметр не задай, срок действия пароля не ограничивается.Результаты
CREATE USER. Это сообщение выдается при успешном создании пользователя.
Описание
Команда CREATE USER включает новых пользователей в базу данных PostgreSQL и может использоваться только суперпользователями базы данных. За дополнительной информацией об аутентификации и управлении пользователями обращайтесь к главе 10.
ПРИМЕЧАНИЕ
Сценарий createuser предназначен для создания новых пользователей базы данных в командной строке операционной системы. Он работает практически по тем же правилам, что и команда CREATE USER.
Пример
В следующем примере создается пользователь PostgreSQL с именем david, входящий в группу accounting. Учетная запись пользователя определяется с паролем jw8s0F4, который остается действительным до 1 января 2005 года.
booktown=# CREATE USER david
booktown-* WITH PASSWORD 'jw8s0F4' CREATEDB
booktown-# IN GROUP accounting VALID UNTIL 'Jan 1 2005';
CREATE USER
Самоучитель по SQL-сервер в Linux
CURRENT_TIMEПолучение текущего времени. Синтаксис CURRENT_TIME Параметры Функция вызывается без параметров. Результаты Текущее время в виде значения типа time. Описание Функция CURRENT_TIME возвращает текущее системное время в виде типа time. Пример Следующая команда выводит текущее время: testdb=# SELECT CURRENTJIME AS thejtime; the_time 19:44:35 (1 row) |
||||
CURRENT_TIMESTAMP
Получение текущих даты и времени.
Синтаксис
CURRENT_TIMESTAMP
Параметры
Функция вызывается без параметров.
Результаты
Текущая дата и текущее время.
Описание
Функция CURRENT_TIME возвращает текущие дату и время в виде значения типа timestamp.
Пример
Следующая команда выводит результат вызова CURRENT_TIMESTAMP:
testdb=# SELECT CURRENT_TIMESTAMP AS date_and_time;
date_and_time
-------------------
2001-09-04 19:48:21-08
(1 row)
CURRENT_USER
Имя текущего пользователя базы данных.
Синтаксис
CURRENT_USER
Параметры
Функция вызывается без параметров.
Результаты
Имя текущего пользователя базы данных.
Описание
Функция CURRENTJJSER возвращает имя текущего пользователя в виде строки типа name (нестандартный строковый тип с длиной 31 символ, предназначенный для хранения системных идентификаторов).
Пример
Следующая команда выводит имя текущего пользователя, подключенного к базе данных testdb:
testdb=# SELECT CURRENTJJSER AS myself;
myself
(1 row)
DECLARE
Определение нового курсора.
Синтаксис
DECLARE курсор
[ BINARY ] [ INSENSITIVE ] [ SCROLL ]
CURSOR FOR запрос
[ FOR { READ ONLY | UPDATE [ OF поле [....]]}]
Параметры
курсор. Имя нового курсора. BINARY. Выборка данных производится в двоичном формате вместо принятого по умолчанию текстового формата. INSENSITIVE. Ключевое слово INSENSITIVE означает, что данные, полученные с использованием курсора, не подвержены изменениям со стороны других процессов (и других курсоров). В PostgreSQL это требование выполняется автоматически, поскольку все операции с курсорами заведомо инкапсулируются в транзакциях. Параметр существует для совместимости с другими системами. SCROLL. Ключевое слово SCROLL разрешает выборку нескольких записей при каждой операции FETCH. Режим поддерживается по умолчанию, поэтому наличие ключевого слова SCROLL ни па что не влияет. запрос. Запрос SQL, по которому курсор производит выборку записей. Структура запроса приведена в описании команды SELECT. READ ONLY. Курсор используется только для чтения данных. Наличие параметра READ ONLY ни на что не влияет, поскольку в PostgreSQL курсорам представляется доступ только для чтения. UPDATE. Курсор используется для обновления таблиц. В PostgreSQL версии 7.1.x (последней на момент написания книги) обновление через курсоры не поддерживается. поле. Список полей, обновляемых курсором. В PostgreSQL версии 7.1.x (последней на момент написания книги) обновление через курсоры не поддерживается.Результаты
SELECT. Сообщение выдается при успешном выполнении команды SELECT. NOTICE: Closing pre-existing portal "курсор". Сообщение означает, что в текущем траизакционном блоке уже был объявлен курсор с заданным именем. В этом случае ранее объявленный курсор автоматически уничтожается. ERROR: DECLARE CURSOR may only be used in begin/end transaction blocks. Ошибка — попытка объявления курсора вне трапзакционного блока. Курсоры используются только в транзакционных блоках.Описание
Команда DECLARE создает в транзакционном блоке курсор, предназначенный для выборки данных, полученных в результате запроса. Возвращаемые данные могут храниться в текстовом или двоичном формате. Курсоры поддерживаются только в контексте транзакций, при попытке их использования вне транзакцноииого блока выдается сообщение об ошибке.
ВНИМАНИЕ
При использовании двоичных (BINARY) курсоров необходима осторожность, поскольку они не поддерживаются некоторыми клиентами.
PostgreSQL не требует отдельного открытия курсоров — курсоры открываются автоматически при объявлении. Тем не менее препроцессор ecpg поддерживает команду OPEN для встроенных и интерактивных приложений SQL.
Пример
В следующем фрагменте объявляется курсор cur_publ i sher, который затем используется для выборки двух записей. В клиенте psql результаты выборки отображаются немедленно.
booktown=# BEGIN WORK; BEGIN
booktown=# DECLARE cur_publisher CURSOR FOR SELECT name FROM publishers: SELECT
booktown=# FETCH FORWARD 2 IN cur_publisher; name
Kids Can Press
Henry Holt & Company. Inc.
(2 rows)
DELETE
Удаление записей из таблицы.
Синтаксис
DELETE FROM [ ONLY ] таблица [ WHERE условие ]
Параметры
таблица. Имя таблицы, из которой удаляются записи. условие. Критерий отбора удаляемых записей. Структура условия аналогична условию секции WHERE команды SELECT — за дополнительной информацией об условиях обращайтесь к описанию команды SELECT. Учтите, что без секции WHERE из таблицы удаляются все записи.Результаты
DELETE число. Это сообщение выдается при выполнении команды. Параметр число определяет количество удаленных записей. Если число равно 0, это означает, что ни одна запись не соответствовала критерию удаления пли же таблица не содержала ни одной записи.
Описание
Команда DELETE удаляет записи из таблицы. Удаляются только те записи, которые соответствуют заданному условию. Чтобы полностью удалить из таблицы все записи, достаточно вызвать команду DELETE без условия. После выполнения команды таблица остается пустой.
ПРИМЕЧАНИЕ
Команда TRUNCATE очищает таблицу более эффективно (и более наглядно), чем команда DELETE без условия.
Ключевое слово ONLY предотвращает удаление записей из таблиц, производных от заданной таблицы. В этом случае операция удаления выполняется только с непосредственно указанной таблицей. Если ключевое слово ONLY отсутствует, операция удаления распространяется не только на заданную таблицу, но и на все производные таблицы.
Пример
Следующая команда удаляет из таблицы shipments все записи о поставках книг покупателю с кодом 142 и датой отгрузки, предшествующей 7 августа 2001 года.
booktown-# DELETE FROM shipments
booktown-# WHERE customeMd = 142
booktown-# AND ship_date < '2001-08-07';
DELETE 1
DROP AGGREGATE
Удаление агрегатной функции из базы данных.
Синтаксис
DROP AGGREGATE функция тип
Параметры
функция. Имя удаляемой агрегатной функции. тип. Тип данных, передаваемый агрегатной функции.Результаты
DROP. Сообщение выдается при успешном удалении агрегатной функции. ERROR: RemoveAggregate: aggregate 'функция' for 'тип' does not exist. Ошибка — агрегатная функция с заданным именем и типом не существует.Описание
Команда DROP AGGREGATE удаляет определение агрегатной функции из базы данных. Выполнение этой команды, как и остальных команд DROP, разрешено только владельцу удаляемых объектов.
Пример
Следующая команда удаляет агрегатную функцию sum, работающую с типом text:
booktown=# DROP AGGREGATE sum text;
DROP
DROP DATABASE
Удаление базы данных из системы.
Синтаксис
DROP DATABASE база_двнных
Параметры
Имя удаляемой базы данных.
Результаты
DROP DATABASE. Сообщение выдается при успешном удалении базы данных. ERROR: user 'пользователь' is not allowed to create/drop databases. Ошибка — команда удаления базы данных введена пользователем, не обладающим привилегиями суперпользователя. Удаление баз данных разрешено только пользователям с привилегией CREATEDB. За дополнительной информацией обращайтесь к описанию команды CREATE USER. ERROR: dropdb: cannot be executed on the template database. Ошибка —попытка удаления базы данных tempi atel. Эта база данных является системной и не может удаляться пользователями. ERROR: dropdb: cannot be executed on an open database. Ошибка — попытка удаления базы данных, к которой пользователь подключен в настоящий момент. При получении этой ошибке попробуйте подключиться к базе данных tempi atel и повторно введите команду удаления базы данных, с которой вы раньше работали. ERROR: dropdb: database ' база_данных' does not exist. Ошибка — попытка удаления несуществующей базы данных. ERROR: dropdb: database 'база_данных' Is not owned by you. Ошибка — попытка удаления базы данных, принадлежащей другому пользователю. ERROR: dropdb: May not be called in a transaction block. Ошибка — попытка вызова команды DROP DATABASE в транзакциониом блоке. Перед удалением базы данных необходимо завершить все текущие транзакции. NOTICE: The database directory 'каталог' could not be removed. Попытка удаления каталога данных завершилась неудачей. Для завершения операции каталог данных необходимо удалить вручную.Описание
Команда DROP DATABASE удаляет базу данных из системы. При удалении базы уничтожаются все данные и информация в системном каталоге, относящиеся к базе. Попытка удаления текущей базы данных, к которой вы подключены в настоящий момент, приводит к ошибке; по этой причине бывает удобнее использовать сценарий dropdb.
ПРИМЕЧАНИЕ
Удаление базы данных может производиться только владельцем или суперпользователем.
Пример
Следующая команда удаляет базу данных testdb:
tempiatel=# DROP DATABASE testdb:
DROP
DROP FUNCTION
Удаление пользовательской функции.
Синтаксис
DROP FUNCTION функция ( [ тип [....]])
Параметры
функция. Имя существующей функции, удаляемой из базы данных. тип. Ноль пли более типов аргументов функции. Комбинация имени и типов однозначно определяет функцию.Результаты
DROP. Сообщение выдается при успешном удалении функции. ERROR: RemoveFunction: Function 'имя(тиггы)' does not exist»Ошибка— функция с заданным именем и типами аргументов не существует в текущей базе данных.Описание
Команда удаляет пользовательские функции, определенные в базе данных. Перечисление типов аргументов, передаваемых функции, обеспечивает ее правильную идентификацию: это необходимо при удалении внешних функций С, поскольку в базе данных могут существовать функции с одинаковыми именами, различающиеся только тинами параметров.
ВНИМАНИЕ
Команда DROP FUNCTION не проверяет наличие других объектов базы данных, зависящих от данной функции. Поэтому вы должны проверить самостоятельно, не отразится ли удаление функции на работе таких объектов.
Пример
Следующая команда удаляет функцию title(integer) из базы данных booktown:
booktown=# DROP FUNCTION tit1e(integer):
DROP
DROP GROUP
Удаление группы пользователей из базы данных.
Синтаксис
DROP GROUP группа
Параметры
Имя удаляемой группы.
Результаты
DROP GROUP. Это сообщение выдается при успешном удалении группы.
Описание
Команда DROP GROUP удаляет группу из текущей базы данных. Команда работает независимо от команды DROP USER, поэтому учетные записи пользователей, входивших в удаляемую группу, остаются в базе данных.
Пример
Следующая команда удаляет группу sales:
booktown=# DROP GROUP sales;
DROP GROUP
DROP INDEX
Удаление индекса из базы данных.
Синтаксис
DROP INDEX индекс [, ...]
Параметры
Имя индекса, удаляемого из базы данных.
Результаты
DROP. Сообщение выдается при успешном удалении индекса. ERROR: index "индекс" does not exist. Ошибка — индекс с указанным именем отсутствует в базе данных.Описание
При помощи этой команды владелец индекса может удалить его из базы данных.
Пример
Следующая команда удаляет индекс customer_id_idx из базы данных booktown:
booktown=# DROP INDEX customer_id_idx;
DROP
DROP LANGUAGE
Удаление процедурного языка из базы данных.
Синтаксис
DROP [ PROCEDURAL ] LANGUAGE 'язык'
Параметры
Имя существующего языка, удаляемого из базы данных.
Результаты
DROP. Сообщение выдается в том случае, если удаление языка прошло без ошибок. ERROR: Language "язык" does not exist. Ошибка — язык с указанным именем не существует в базе данных.Описание
Команда DROP PROCEDURAL LANGUAGE удаляет определение процедурного языка из базы данных.
ВНИМАНИЕ
Команда DROP LANGUAGE не следит за удалением зарегистрированных функций и триггеров, использующих данный язык. Прежде чем удалять язык, убедитесь в том, что ни одна существующая функция в базе данных не зависит от него.
Пример
Следующая команда удаляет язык pi example из базы данных booktown:
booktown=# DROP PROCEDURAL LANGUAGE 'plexample':
DROP
DROP OPERATOR
Удаление оператора из базы данных.
Синтаксис
DROP OPERATOR оператор
( { левый__тип NONE } .
{ правый_тип | NONE } )
Параметры
оператор. Удаляемый оператор. левыйjnun \ NONE. Тип левого операнда (или NONE при его отсутствии). правый_тип \ NONE. Тип правого операнда (или NONE при его отсутствии).Результаты
DROP. Сообщение возвращается при успешном удалении пользователя. ERROR: RemoveOperator: binary operator 'оператор' taking 'левый_тип' and 1 правый_тип' does not exi st. Ошибка — оператор с указанным именем и типами операндов не существует. ERROR: RemoveOperator: left unary operator 'оператор' taking 'левый_mun' does not exist. Ошибка — левый унарный оператор с указанным именем не существует. ERROR: RemoveOperator: right unary operator 'оператор' taking 'правыйjnun' does not exist. Ошибка — правый унарный оператор с указанным именем не существует.Описание
Команда DROP OPERATOR удаляет существующий оператор из базы данных. Удаление операторов разрешено только их владельцам и суперпользователям.
ВНИМАНИЕ
При удалении оператора, используемого методами доступа или операторными классами, возникают проблемы. Прежде чем удалять оператор, убедитесь в том, что от него не зависят существующие элементы базы данных.
Пример
Следующая команда удаляет бинарный оператор !#, работающий с двумя операндами типа Integer:
booktown=# DROP OPERATOR !# (integer, integer);
DROP
DROP RULE
Удаление правила из базы данных.
Синтаксис
DROP RULE правило [, ...]
Параметры
Имя удаляемого правила. Одной командой можно удалить сразу несколько правил, имена которых перечисляются через запятую.
Результаты
DROP. Сообщение возвращается при успешном удалении правила. ERROR: Rule or view "правило" not found. Ошибка — правило с указанным именем не существует.Описание
Команда DROP RULE предназначена для удаления правил из баз данных PostgreSQL. Удаление правила вступает в силу немедленно; правило сразу же становится недоступным, а его определение полностью исключается из базы данных.
Пример
Следующая команда удаляет правило sync_stock_with_editions:
booktown=# DROP RULE sync_stock_with_editions;
DROP
DROP SEQUENCE
Удаление последовательности из базы данных.
Синтаксис
DROP SEQUENCE последовательность [. ...]
Параметры
Имя удаляемой последовательности. В одной команде можно удалить сразу несколько последовательностей, имена которых перечисляются через запятую.
Результаты
DROP. Сообщение возвращается при успешном удалении последовательности. ERROR: sequence "последовательности" does not exist. Ошибка — последовательность с указанным именем не существует.Описание
Команда DROP SEQUENCE удаляет из базы данных генератор числовых последовательностей.
Пример
Команда, удаляющая из базы данных последовательность shipments_ship_id_seq:
booktown=# DROP SEQUENCE shipments_ship_id_seq;
DROP
DROP TABLE
Удаление таблицы из базы данных.
Синтаксис
DROP TABLE таблица [. ...]
Параметры
Имя существующей таблицы, удаляемой из базы данных. В одной команде можно удалить сразу несколько таблиц, имена которых перечисляются через запятую.
Результаты
DROP. Сообщение возвращается при успешном удалении таблицы. ERROR: table "таблица" does not exist. Ошибка — таблица или представление с указанным именем не существует в базе данных.Описание
Команда DROP TABLE удаляет таблицы из базы данных. Удаление таблиц разрешено только их владельцам и суперпользователям.
ПРИМЕЧАНИЕ
Стирание данных в таблице (вместо ее полного уничтожения) выполняется при помощи команд TRUNCATE и DELETE.
При удалении таблицы также уничтожаются все индексы, построенные для этой таблицы.
Пример
Следующая команда удаляет таблицу employees из базы данных booktown:
booktown=# DROP TABLE employees:
DROP
DROP TRIGGER
Удаление определения триггера из базы данных.
Синтаксис
DROP TRIGGER триггер ON таблица
Параметры
триггер. Имя удаляемого триггера. таблица. Имя таблицы, для которой устанавливался триггер.Результаты
DROP. Сообщение возвращается при успешном удалении триггера. ERROR: DropTrigger: there is no trigger "триггер" on relation таблица. Ошибка — в таблице не существует триггер с указанным именем.Описание
Команда DROP TRIGGER удаляет триггер из базы данных. При выполнении этой команды уничтожаются все ссылки на удаляемый триггер. Удаление триггеров разрешено только их владельцам.
Пример
Следующая команда удаляет триггер sync_authors_books из таблицы authors базы данных booktown:
booktown-# DROP TRIGGER sync_authors_books ON authors:
DROP
DROP TYPE
Удаление типа данных из системных каталогов.
Синтаксис
DROP TYPE тип [,. . .]
Параметры
Имя удаляемого типа. В одной команде можно удалить сразу несколько типов, имена которых перечисляются через запятую.
Результаты
DROP. Сообщение возвращается при успешном удалении типа. ERROR: RemoveType: type 'тип' does not exist. Ошибка — указанный тип не найден в текущей базе данных.Описание
Команда DROP TYPE предназначена для удаления типов данных из базы данных. Удаление типов разрешено только их владельцам и суперпользователям.
ПРИМЕЧАНИЕ
Суперпользователю также разрешено удалять системные типы данных, но это нарушает стабильную работу системы. Будьте внимательны!
Выполнение команды DROP TYPE не приводит к автоматическому удалению объектов, использующих удаляемый тип (или типы). После удаления типа данных все объекты, в которых он используется, обычно перестают работать. Не забудьте удалить объекты, зависящие от удаляемого типа данных, и не удаляйте типы, используемые сохраняемыми объектами!
Пример
Следующая команда удаляет тип данных zero из базы данных booktown:
booktown=# DROP TYPE zero;
DROP
DROP USER
Удаление пользователя PostgreSQL
Синтаксис
DROP USER пользователь
Параметры
Имя удаляемого пользователя PostgreSQL.
Результаты
DROP USER. Сообщение возвращается при успешном удалении пользователя PostgreSQL. ERROR: DROP USER: user "пользователь" does not exist. Ошибка — пользователь PostgreSQL не найден на подключенном хосте. ERROR: DROP USER: user "пользователь" owns database "база_данных". cannot be removed. Ошибка— удаляемый пользователь остается владельцем базы данных. Перед удалением пользователя необходимо удалить принадлежащие ему базы данных.Описание
Команда DROP USER удаляет пользователей PostgreSQL. Удаляемый пользователь не может быть владельцем существующей базы данных. Все объекты, ранее принадлежавшие пользователю, остаются в базе данных.
ПРИМЕЧАНИЕ
Удаление пользователей из командной строки производится сценарием dropuser — «оболочкой» для вызова команды SQL (сценарий dropuser описан в главе 10).
Пример
Следующая команда удаляет из системы пользователя PostgreSQL с именем Jonathan:
tempiatel=# DROP USER Jonathan;
DROP
DROP VIEW
Удаление существующего представления из базы данных.
Синтаксис
DROP VIEW представление. [. ...]
Параметры
Имя удаляемого представления.
Результаты
DROP. Сообщение возвращается при успешном удалении представления. ERROR: view "представление" does not exlst. Ошибка — указанное представление не существует в текущей базе данных.Описание
Команда DROP VIEW удаляет представление из базы данных. Представления, как и большинство других объектов, удаляются только владельцами.
Пример
Следующая команда удаляет представление h_publishers из базы данных booktown:
booktown=# DROP VIEW h_publishers;
DROP
END
Завершение текущей транзакции блока и фиксация изменений в базе.
Синтаксис
END [ WORK | TRANSACTION ]
Параметры
Необязательные ключевые слова, делающие команду SQL более наглядной.
Результаты
COMMIT. Сообщение возвращается при успешной фиксации транзакции. NOTICE: COMMIT: no transaction In progress. He существует незавершенной транзакции, к которой бы могла относиться команда END.Описание
Команда END является синонимом команды COMMIT и используется для фиксации транзакций по тем же правилам.
Пример
В следующем примере транзакция фиксируется командой END.
booktown=# END WORK;
COMMIT
EXPLAIN
Вывод плана выполнения запроса.
Синтаксис
EXPLAIN [ VERBOSE ] запрос
Параметры
VERBOSE. При наличии необязательного ключевого слова VERBOSE в плане запроса выводится дополнительная информация. запрос. Запрос, план выполнения которого вы хотите получить.Результаты
NOTICE: QUERY PLAN: plan. За сообщением следует план выполнения запроса, полученный от сервера. EXPLAIN. Сообщение выводится после плана запроса и является признаком завершения выходных данных.Описание
Команда EXPLAIN отображает план выполнения запроса, сгенерированный планировщиком PostgreSQL. Планировщиком (planner) называется компонент PostgreSQL, пытающийся выработать наиболее эффективный способ выполнения запроса SQL. В плане выполнения содержится информация о том, как будет организован просмотр таблиц, задействованных в запросе, сервером базы данных. В зависимости от ситуации возможен последовательный перебор или выборка по индексу. В план включена информация обо всех таблицах, участвующих в запросе. По выходным данным команды EXPLAIN можно оценить относительную стоимость планов выполнения запроса, измеряемую в количестве операций выборки страниц с диска.
PostgreSQL не пытается преобразовать это число в более привычные промежутки времени, поскольку результат в значительной мере зависит от требований к оборудованию и загрузки операционной системы. Таким образом, стоимость плана выполнения запроса может использоваться только как относительный критерий для сравнения с другими, альтернативными запросами.
Стоимость выражается двумя числами, разделенными точкой. Первое число определяет приблизительную стоимость инициализации (интервал времени перед получением первой записи). Второе число определяет приблизительную оценку общего времени, необходимого для полной обработки запроса.
При наличии ключевого слова VERBOSE команда EXPLAIN выводит внутреннее представление плана в виде дерева. Для среднего пользователя эта информация не представляет интереса; она предназначена для программистов, знакомых с внутренним устройством PostgreSQL.
Примеры
Ниже приведены результаты, полученные при выполнении команды EXPLAIN для запроса к таблице books базы данных booktown.
booktown=# EXPLAIN SELECT * FROM books AS b (book_id)
booktown-* NATURAL INNER JOIN editions;
NOTICE: QUERY PLAN:
Merge Join (cost=71.27..83.96 rows=150 width=64)
-> Sort (cost=1.44..1.44 rows=15 width=24)
-> Seq Scan on books b (cost=0.00..1.15 rows=15 width=24)
-> Sort (cost=69.83..69.83 rows=1000 width=40)
-> Seq Scan on editions (cost=0.00..20.00 rows=1000 width=40)
EXPLAIN
В следующем примере приведен расширенный вывод для более простого запроса с ключевым словом VERBOSE.
booktown=# EXPLAIN VERBOSE SELECT * FROM books;
NOTICE: QUERY DUMP:
{ SEQSCAN :startup_cost 0.00 :total_cost 1.15 :rows 15 :width 24 :qptargetlist
({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname id
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1}}
{ TARGETENTRY :resdom { RESDOM :resno 2 :restype 25 :restypmod -1 :resname title :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 2 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 2}}
{ TARGETENTRY :resdom { RESDOM :resno 3 :restype 23 :restypmod -1 :resname
authoMd :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr
{ VAR :varno 1 :varattno 3 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold
1 :varoattno 3}} { TARGETENTRY :resdom { RESDOM :resno 4 :restype 23 :restypmod
-1 :resname subject_id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 1 :varattno 4 :vartype 23 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 4}}) :qpqual <> :lefttree <> :righttree <> :extprm
0 :locprm 0 :initplan <> :nprm 0 :scanrelid 1 }
NOTICE: QUERY PLAN:
Seq Scan on books (cost=0.00..1.15 rows=15 width=24)
EXPLAIN
FETCH
Выборка записей с использованием курсора.
Синтаксис
FETCH направление
[ количество_записей ] { IN | FROM } курсор
направление ::- { FORWARD | BACKWARD | RELATIVE }
количество_записей ::={ число \ ALL NEXT PRIOR }
Параметры
направление. Необязательный параметр, определяющий направление выборки. Допустимыми значениями являются перечисленные ниже ключевые слова. FORWARD. Признак выборки записей, следующих за текущей позицией курсора. Используется по умолчанию, если направление не указано. BACKWARD. Признак выборки записей, предшествующих текущей позиции курсора. RELATIVE. Необязательное ключевое слово, поддерживаемое для совместимости со стандартом SQL92. В PostgreSQL 7.1.x курсоры производят выборку только от текущей позиции курсора, поэтому наличие ключевого слова RELATIVE ни на что не влияет. Учтите, что при использовании ключевого слова RELATIVE с параметром количество_записей, равным 0, происходит ошибка (см. ниже пункт «Результаты»). количество_записей. Количество записей в выборке. Значение параметра представляет собой число или одно из следующих ключевых слов: ALL — выборка всех записей; NEXT — запись, следующая за текущей позицией курсора; PRIOR — запись, предшествующая текущей позиции курсора. курсор. Имя открытого курсора, используемого для выборки записей.Результаты
В случае успешного завершения команда FETCH возвращает результаты запроса, ассоциированного с указанным курсором. Если запрос завершается неудачей, выводится одно из представленных ниже сообщений.
NOTICE: PerformPortalFetch: portal "курсор" not found. Сообщение означает, что курсор с указанным именем не был объявлен. Не забывайте о том, что перед использованием курсор необходимо объявить в транзакционном блоке. NOTICE: FETCH/ABSOLUTE not supported, using RELATIVE. Попытка выборки с абсолютным позиционированием (ключевое слово ABSOLUTE вместо RELATIVE). PostgreSQL не поддерживает абсолютное позиционирование курсора, то есть перемещение к заданной записи итогового набора (вместо перемещения относительно текущей позиции курсора). ERROR: FETCH/RELATIVE at current position is not supported. Ошибка — попытка выборки нуля записей с ключевым словом RELATIVE. Это связано с тем, что в соответствии со стандартом SQL92 синтаксис FETCH RELATIVE О FROM Курсор должен обеспечивать повторную выборку записи в текущей позиции курсора. В PostgreSQL этот синтаксис не поддерживается. Без ключевого слова RELATIVE число 0 интерпретируется как запрос на выборку всех записей. Но с ключевым словом RELATIVE PostgreSQL предполагает, что вы пытаетесь использовать синтаксис SQL92, и вместо того, чтобы вернуть все записи, выводит сообщение об ошибке.Описание
Команда FETCH возвращает заданное количество записей от текущей позиции курсора. Курсоры используются только в транзакцнонных блоках, поскольку возвращаемая ими информация теоретически зависит от действий других пользователей в системе. Количество записей может быть как положительным, так и отрицательным. При положительном значении отсчет ведется в направлении, заданном соответствующим параметром (если параметр не указан, по умолчанию используется параметр FORWARD).
При отрицательном количестве записей отсчет ведется в направлении, противоположном заданному. Например, конструкция FORWARD -5 эквивалентна конструкции BACKWARD 5. Если количество записей превышает количество записей в базе, команда FETCH выбирает все существующие записи в указанном направлении.
ПРИМЕЧАНИЕ
В PostgreSQL 7.1.x не поддерживается обновление данных с использованием курсоров.
Примеры
В дальнейших примерах используется курсор cur_empl oyee, возвращающий данные из таблицы employees.
Следующая команда выбирает первые две записи через курсор cur_employee:
booktown=# BEGIN:
BEGIN
booktown=# DECLARE cur_employee CURSOR FOR
booktown-# SELECT firstjiame, lastjiame FROM employees:
SELECT
booktown=# FETCH FORWARD 2 IN cur_employee;
first_name last_name
Vincent | Appel
Michael Holloway
(2 rows)
В следующем примере конструкция BACKWARD -2 (двойное отрицание) также производит выборку двух записей в прямом направлении:
booktown=# FETCH BACKWARD -2 IN cur_employee:
firstjiame | last_name
David Joble Ben Noble
(2 rows)
Наконец, следующая команда демонстрирует выборку в обратном направлении через курсор cur_employee.
booktown=# FETCH BACKWARD 3 IN cur_employee;
first_name | last_name
David Joble
Michael Holloway
Vincent Appel
(3 rows)
GRANT
Предоставление прав доступа пользователю, группе или всем пользователям базы данных.
Синтаксис
GRANT привилегия [, ...] ON объект [. ...]
ТО { PUBLIC | GROUP группа \ пользователь }
Параметры
привилегия. Предоставляемая привилегия. Допустимые значения: SELECT — пользователю или группе разрешается выборка из всех полей заданной таблицы или представления; INSERT — пользователю или группе разрешается вставка данных во все поля полей заданной таблицы; UPDATE — пользователю или группе разрешается обновление всех полей заданной таблицы; DELETE — пользователю или группе разрешается удаление данных из всех полей заданной таблицы; RULE — пользователю или группе разрешается удаление правил из заданной таблицы; ALL — сокращенная запись для предоставления всех перечисленных привилегий пользователю или группе. объект. Имя объекта, для которого предоставляются привилегии. Допустимыми типами объектов являются таблицы, представления и последовательности. PUBLIC. Необязательное ключевое слово PUBLIC означает, что привилегия предоставляется всем пользователям базы данных. группа. Имя группы, которой предоставляются привилегии. пользователь. Имя пользователя PostgreSQL, которому предоставляются привилегии. Ключевое слово PUBLIC обозначает всех пользователей.Результаты
CHANGE. Сообщение возвращается в том случае, если предоставление привилегий пользователю или группе прошло успешно. ERROR: ChangeAcl: class "объект" not found. Ошибка — указанный объект не найден в текущей базе данных. ERROR: aclparse: non-existent user "пользователь". Ошибка — пользователь с указанным именем не существует. ERROR: non-existent group "группа". Ошибка — группа не существует.Описание
Команда GRANT предоставляет пользователям и группам права доступа к объектам. Права могут предоставляться конкретному пользователю или группе, а ключевое слово PUBLIC предоставляет их всем пользователям базы данных. По умолчанию доступ к объекту разрешен только его владельцу. После того как объект создан, владелец предоставляет права доступа к нему другим пользователям.
Чтобы предоставить частичный доступ к данным, содержащимся в таблице, создайте представление для нужного подмножества полей и/или записей и предоставьте пользователям доступ к этому представлению.
В клиенте psql информация о правах доступа к существующим объектам выводится командой \z.
Примеры
Следующая команда предоставляет пользователю manager все права доступа к таблице publishers:
booktown=# GRANT ALL ON publishers TO manager;
GRANT
Ниже приведен пример использования команды \z клиента psql для вывода информации о правах доступа к таблице publ I shers:
booktown=# \z publishers
Access permissions for database "booktown"
Relation | Access permissions
publishers | {"=","manager=arwR"}
(1 row)
INSERT
Вставка новых записей в таблицу.
Синтаксис
INSERT INTO таблице [ ( поле [. ...] ) ]
{ DEFAULT VALUES
VALUES ( значение [. ...] ) |
запрос }
Параметры
таблица. Таблица, в которую вставляются новые данные. поле. Имя поля, для которого задается значение. Должно совпадать с именем одного из полей таблицы, хотя порядок перечисления полей в команде не обязан совпадать с естественным порядком их следования в таблице. значение. Константа или выражение, заносимое в таблицу. Если в команде задан список полей, значение ассоциируется с соответствующим полем в списке (перечисленные поля должны однозначно соответствовать выражениям в списке). Если для какого-либо поля выражение относится к другому типу данных, PostgreSQL пытается выполнить преобразование типа. Если попытка завершается неудачей, вся команда INSERT отменяется. запрос. Синтаксически правильная команда SQL SELECT. Количество полей, возвращаемых запросом, должно соответствовать количеству полей в списке, а их типы данных должны быть совместимы.Результаты
INSERT old 1. Сообщение возвращается в случае успешной вставки одной записи. Значение aid представляет собой идентификатор объекта вставленной записи. INSERT 0 число. Сообщение возвращается в случае успешной вставки нескольких записей. Параметр число определяет общее количество вставленных записей.Описание
Команда INSERT вставляет в таблицу новые записи. Записи могут вставляться как по одной, так и группами. В сочетании с ключевым словом VALUES команда INSERT всегда вставляет ровно одну запись. Чтобы вставить несколько записей, можно воспользоваться данными, полученными в результате запроса. Данные из итогового набора запроса заносятся в таблицу, указанную в команде INSERT.
Если при вставке для какого-либо поля указано значение неправильного типа, PostgreSQL автоматически преобразует его к нужному типу. Если преобразование невозможно, команда INSERT завершается неудачей. При вставке отдельных полей (вместо целых записей) допускается перечисление полей в произвольном порядке; однако порядок следования значений должен соответствовать порядку следования полей.
ПРИМЕЧАНИЕ
Если значения каких-либо полей не указаны, PostgreSQL выбирает один из двух вариантов. Если для поля не задано значение по умолчанию, ему присваивается псевдозначение NULL. Поля, для которых задано значение по умолчанию, инициализируются этим значением.
Примеры
Следующая команда вставляет одну запись в таблицу employees:
booktown=# INSERT INTO employees
booktown-# VALUES (106. 'Hall'. 'Timothy');
INSERT 3752064 1
В другом примере задаются значения полей i d и 1 astjname (без поля f I rstjname); эти поля перечисляются в списке, предшествующем секции VALUES. В результате поле firstjiame инициализируется значением NULL:
booktown=# INSERT INTO employees (id. lastjiame)
booktown=# VALUES (108, 'Williams');
INSERT 3752065 1
В последнем примере вес 15 записей таблицы books сохраняются в таблице books_backup, при этом источником данных при вставке является запрос:
booktown=# INSERT INTO book_backup
booktown-# SELECT * FROM books;
INSERT 0 15
LISTEN
Ожидание уведомлений о событиях.
Синтаксис
LISTEN событие
Параметры
Имя события, ожидаемого сервером.
Результаты
LISTEN. Сообщение возвращается при успешном выполнении команды, когда серверный процесс ожидает уведомления. NOTICE: Async_Listen: We are already listening on событие. Сообщение возвращается в том случае, если сервер уже ожидает уведомления об указанном событии.Описание
Пара команд NOTIFY и LISTEN обеспечивает взаимодействие между компонентами PostgreSQL. Получив команду LISTEN, серверный процесс ожидает событие с указанным именем. Уведомления передаются командой NOTIFY, которая обычно включается в правила, отслеживающие изменения в различных компонентах базы данных. Таким образом, все «слушатели» автоматически оповещаются об изменении ситуации.
Команда NOTIFY, выполненная любым процессом, рассылает оповещения всем работающим серверным процессам, ожидающим данного события. Когда серверный процесс получает уведомление, он передает его клиентскому приложению (например, psql), а клиентское приложение распоряжается им по своему усмотрению. За дополнительной информацией об этом механизме межпроцессных взаимодействий обращайтесь к описанию команды NOTIFY.
Имя события представляет собой произвольную строку длиной не более 31 символа (то же относится и к команде NOTIFY). Чтобы вывести серверный процесс из состояния ожидания, воспользуйтесь командой UNLISTEN.
Пример
В следующем фрагменте продемонстрированы ожидание и уведомление о событии в клиенте psql:
booktown=# LISTEN publisherjjpdate;
LISTEN
booktown=# NOTIFY publisherjjpdate:
Asynchronous NOTIFY 'publisherjjpdate' from backend with pid '16864' received.
LOAD
Динамическая загрузка объектных файлов в базу данных.
Синтаксис
LOAD 'файл'
Параметры
Имя загружаемого объектного файла.
Результаты
LOAD. Сообщение возвращается при успешной загрузке объектного файла. ERROR: LOAD: could not open file 'файл'. Ошибка —указанный файл не найден. Убедитесь в том, что серверный процесс PostgreSQL обладает правами доступа к файлу.Описание
Команда LOAD загружает объектный файл в контексте серверного процесса PostgreSQL. После загрузки объектный файл обеспечивает выполнение тех функций, для которых он создавался. Механизм загрузки объектных файлов позволяет динамически подгружать компоненты базы данных (например, типы и функции). Если объектный файл не был загружен специальной командой, загрузка происходит автоматически при вызове функции, реализованной в файле. В настоящее время поддерживаются только объектные файлы С.
ПРИМЕЧАНИЕ
Команда LOAD также используется для повторной загрузки перекомпилированных объектных файлов.
Пример
Следующая команда загружает объектный файл /usr/local/src/lxp/libxpl.so в базу данных 1х:
lx=# LOAD 7usr/local/src/lxp/libxpl.so';
LOAD
LОСК
Блокировка записей в транзакциях.
Синтаксис
LOCK [ TABLE ] таблица
LOCK [ TABLE ] таблица IN режим
режим ::= { [ ROW | ACCESS ] { SHARE | EXCLUSIVE } |
SHARE ROW EXCLUSIVE } MODE
Параметры
таблица. Имя таблицы, для которой устанавливается блокировка. режим. Существуют семь режимов блокировки, обозначаемые разными комбинациями ключевых слов. Ниже приведен их список в порядке возрастания ограничений, с указанием блокируемых команд. ACCESS SHARE MODE. Устанавливается автоматически командой SELECT для таблиц, из которых производится выборка данных. В заблокированных таблицах запрещается выполнение команд ALTER TABLE, DROP TABLE n VACUUM. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровня ACCESS EXCLUSIVE MODE. ROW SHARE MODE. Устанавливается автоматически командами SELECT, содержащими секцию FOR UPDATE. В заблокированных таблицах запрещается выполнение команд ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE. ROW EXCLUSIVE MODE. Устанавливается автоматически командами UPDATE, INSERT и DELETE. В заблокированных таблицах запрещается выполнение команд ALTER TABLE, DROP TABLE и CREATE INDEX. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней SHARE MODE, SHARE ROW EXCLUSIVE MODE,EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE. SHARE MODE. Устанавливается автоматически командами CREATE INDEX. В заблокированных таблицах запрещается выполнение команд INSERT, UPDATE, DELETE, ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней ROW EXCLUSIVE MODE, SHARE ROW EXCLUSIVE MODE, EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE. SHARE ROW EXCLUSIVE MODE. Специальный режим блокировки, практически идентичный режиму EXCLUSIVE MODE, но допускающий установку параллельных блокировок уровня ROW SHARE MODE. EXCLUSIVE MODE. Запрещает выполнение команд INSERT, UPDATE, DELETE, CREATE INDEX, ALTER TABLE, DROP TABLE и VACUUM, а также команд SELECT с секцией FOR UPDATE. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней ROW SHARE MODE, ROW EXCLUSIVE MODE, SHARE MODE, SHARE ROW EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE. ACCESS EXCLUSIVE MODE. Устанавливается автоматически командами ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц запрещаются любые команды или параллельные блокировки любого уровня.Результаты
LOCK TABLE. Сообщение выдается при успешном установлении блокировки для таблицы. ERROR: Relation 'таблица' does not exist. Ошибка— таблицас указанным именем не существует в базе данных. ERROR: Deadlock detected. Ошибка — взаимная блокировка, возникшая из-за выполнения команд LOCK TABLE в двух параллельных транзакциях.Описание
Команда LOCK TABLE предназначена для блокировки таблиц на время транзакции. Блокировкой называется временное ограничение доступа к таблице (в зависимости от выбранного режима). Сеанс, заблокировавший таблицу, пользуется нормальным доступом; последствия блокировки распространяются только на других пользователей, пытающихся получить доступ к заблокированной таблице.
Обратите внимание: блокировка не означает отказа в доступе. С точки зрения пользователя, подключенного к базе данных и пытающегося обратиться к заблокированному ресурсу, блокировка приводит к задержке, но не к отказу в предоставлении доступа. Пользователю приходится ожидать либо завершения заблокированной команды пользователем, либо снятия блокировки с таблицы.
Некоторые команды SQL автоматически устанавливают блокировку для выполнения своих функций; в таких случаях PostgreSQL всегда выбирает минимально необходимый уровень блокировки. После завершения транзакции блокировка немедленно снимается.
Команда LOCK TABLE без параметра устанавливает максимально жесткий режим блокировки (ACCESS EXCLUSIVE). Чтобы ограничения были менее жесткими, следует явно задать нужный режим.
ВНИМАНИЕ
Блокировка таблиц возможна только в транзакциях. Выполнение команды LOCK TABLE вне транзакционного блока не приводит к ошибке, но установленная блокировка немедленно снимается. Транзакция создается командой BEGIN; команда COMMIT фиксирует изменения в базе данных и снимает блокировку.
Ситуация взаимной блокировки (deadlock) возникает в там случае, когда каждая из двух транзакций ожидает снятия блокировки другой транзакцией. Хотя PostgreSQL распознает взаимные блокировки и завершает их командой ROLLBACK, это все равно причиняет определенные неудобства. Приложения не должны сталкиваться с проблемой взаимных блокировок, поэтому проектируйте их так, чтобы объекты всегда блокировались в одинаковом порядке.
Пример
Следующая команда блокирует таблицу books в базе данных booktown в режиме
ACCESS EXCLUSIVE:
booktown=# BEGIN;
BEGIN
booktown=# LOCK TABLE books IN ACCESS EXCLUSIVE MODE;
LOCK TABLE
MOVE
Перемещение курсора к другой записи.
Синтаксис
MOVE [ направление ] [ количество ]
{ IN | FROM } курсор
Параметры
направление. Направление, в котором перемещается указанный курсор. За дополнительной информацией о направлениях обращайтесь к описанию команды FETCH. количество. Расстояние, на которое перемещается курсор. курсор. Перемещаемый курсор.Результаты
Команда MOVE выдает те же ошибки и сообщения, что и команда FETCH, но при этом она не возвращает записей. Сообщения перечислены в описании команды FETCH.
Описание
Команда MOVE перемещает курсор в новую позицию. Она очень похожа на команду FETCH, но в отличие от этой команды не возвращает пройденные записи.
Примеры
В этих примерах используется транзакция и курсор cur_employee, возвращающий данные из таблицы employees. Следующая команда MOVE смещает курсор на одну запись вперед в итоговом наборе:
booktown=# MOVE FORWARD 1 IN cur_employee;
MOVE
Выходные данные команды состоят из единственного сообщения MOVE. В следующем примере команда FETCH используется для отображения второй записи (после перемещения курсора):
booktown=# FETCH I IN cur_employee;
first_name | lastjiame
Michael Holloway
(1 row)
NOTIFY
Уведомление всех серверных процессов, ожидающих некоторого события.
Синтаксис
NOTIFY событие
Параметры
Событие, о наступлении которого оповещаются процессы.
Результаты
NOTIFY. Это сообщение выдается в том случае, если рассылка прошла успешно.
Описание
Команда NOTIFY работает в сочетании с командой LISTEN, описанной выше. Эти две команды обеспечивают простой механизм межпроцессных взаимодействий, который при правильном применении способен принести существенную пользу.
Команда NOTIFY рассылает уведомление о событии с заданным именем; если какая-либо клиентская программа ранее выполнила команду LISTEN с тем же именем события, она получает уведомление.
ПРИМЕЧАНИЕ
Поведение клиентского процесса после получения уведомления, отправленного командой NOTIFY, полностью зависит от реализации, поэтому процесс может немедленно отреагировать на происходящее, а может ничего не делать.
Уведомление содержит информацию об имени события и идентификаторе серверного процесса, рассылающего уведомления. Проектировщик базы данных определяет имена событий и реакцию на них со стороны базы данных.
Команды NOTIFY и LISTEN чаще всего применяются для оповещения клиентских процессов о модификации таблиц, поэтому имена событий часто совпадают с именами таблиц. Впрочем, это всего лишь распространенный вариант использования этих команд; совпадение имен событий с именами таблиц не обязательно.
ПРИМЕЧАНИЕ
Автоматическое уведомление об изменениях в таблицах достигается включением команды NOTIFY в правило, срабатывающее при обновлении таблицы.
Транзакции
Следует обратить внимание на особенности выполнения команды NOTIFY в транзакциях. Самая важная особенность заключается в том, что рассылка уведомлений по команде NOTIFY в транзакциях откладывается до момента фиксации. Тем самым предотвращается рассылка уведомлений транзакциями, которые в дальнейшем могут быть откачены.
Также следует учитывать, что серверный процесс не доставляет уведомление подключенному клиенту при наличии незавершенной транзакции. Если в клиентском процессе в настоящее время существует открытая транзакция, серверный процесс отправляет уведомление лишь после того, как транзакция будет завершена командой COMMIT или ROLLBACK.
Повторные сигналы
Поведение пары команд NOTIFY и LISTEN напоминает систему сигналов в Unix. Даже если уведомление рассылается многократно несколькими командами NOTIFY, ожидающие процессы получают его только один раз.
По этой причине количество принятых уведомлений не может использоваться для накопления значений счетчиков или отслеживания важных событий, происходящих в базе данных. В правильной реализации эти действия выполняются с применением команды NOTIFY с объектом последовательности (или его аналогом).
Пример
В следующем фрагменте первая команда определяет некоторое событие, а вторая команда уведомляет серверный процесс о наступлении этого события:
booktown=# LISTEN publisherjteletion;
LISTEN
booktown=# NOTIFY publisherjjeletlon;
Asynchronous NOTIFY 'publisher_deletion' from backend with pid '16864' received.
REINDEX
Восстановление индексов в таблицах.
Синтаксис
REINDEX { TABLE | DATABASE | INDEX } объект [ FORCE ]
Параметры
TABLE ] DATABASE | INDEX. Тип индексируемого объекта. объект. Имя индексируемого объекта. FORCE. Ключевое слово FORCE восстанавливает индексы для всех перечисленных объектов. Если параметр не задан, восстанавливаются только поврежденные индексы.Результаты
REINDEX. Это сообщение выдается при успешной реиндексации целевого объекта.
Описание
Команда REINDEX восстанавливает (строит заново) поврежденные индексы. Возможность восстановления особенно важна при повреждении системных индексов. Чтобы восстановить системные индексы, завершите процесс postmaster и запустите его с ключом командной строки -о " -О -Р". В результате запускается автономный сервер, допускающий восстановление системных индексов. В приглашении командной строки psql введите команду REINDEX DATABASE.
Пример
Следующая команда восстанавливает все индексы для таблицы books базы данных booktown:
booktown=# REINDEX TABLE books;
REINDEX
RESET
Восстановление стандартных значений конфигурационных переменных.
Синтаксис
RESET переменная
Параметры
Переменная, которой присваивается значение по умолчанию.
Результаты
RESET VARIABLE. Это сообщение выдается при успешном сбросе переменной.
Описание
Команда RESET восстанавливает стандартные значения конфигурационных переменных. За дополнительной информацией об этих переменных и их первоначальных значениях обращайтесь к описанию команды SET.
Аналогичного результата можно добиться при помощи следующей команды:
SET переменная ТО DEFAULT
Пример
Следующая команда присваивает переменной SEED значение по умолчанию:
testdb=# RESET SEED;
RESET VARIABLE
REVOKE
Отмена привилегий доступа у пользователя, группы или всех пользователей.
Синтаксис
REVOKE привилегия [. ... ]
ON объект [. ...]
FROM { PUBLIC | GROUP группа \ пользователь }
Параметры
привилегия. Отменяемая привилегия. Значения SELECT, INSERT, UPDATE и DELETE отменяют привилегии использования соответствующих команд. Значение RULE отменяет привилегию создания правил в таблицах. Значение ALL отменяет все привилегии доступа для таблицы или другого объекта. объект. Имя объекта, для которого отменяются привилегии. Таким объектом может быть таблица, представление или последовательность. группа. Имя группы, которая лишается привилегии. пользователь. Имя пользователя PostgreSQL, который лишается привилегии. PUBLIC. Ключевое слово PUBLIC лишает всех пользователей PostgreSQL указанной привилегии.Результаты
CHANGE. Сообщение возвращается, если привилегии были успешно отменены. ERROR: Relation 'объект' does not exist. Ошибка — указанный объект не найден в текущей базе данных. ERROR: aclparse: non-existent user "пользователь". Ошибка — пользовательс указанным именем не существует. ERROR: non-existent group "группа". Ошибка — группа не существует.Описание
Команда REVOKE отменяет привилегии доступа к объекту. Привилегии могут отзываться у конкретного пользователя, у группы или у всех пользователей (с ключевым словом PUBLIC).
Пример
Следующая команда лишает пользователя guest права вставки новых записей в таблицу books:
booktown=# REVOKE INSERT ON guest FROM books;
CHANGE
ROLLBACK
Откат текущей транзакции с отменой всех изменений.
Синтаксис
ROLLBACK [ WORK TRANSACTION ]
Параметры
Необязательные ключевые слова, делающие команду SQL более наглядной.
Результаты
ROLLBACK. Сообщение выдается при успешном откате транзакции. NOTICE: ROLLBACK: no transaction In progress. Сообщение выдается при отсутствии незавершенных транзакций.Описание
Команда ROLLBACK прерывает текущую транзакцию и отменяет все изменения, находящиеся в очереди на фиксацию.
Пример
Пример использования команды ROLLBACK для отмены случайно введенной команды DELETE:
booktown=# BEGIN WORK;
BEGIN
booktown=# DELETE FROM shipments;
DELETE 36
booktown=# ROLLBACK WORK:
ROLLBACK
SELECT
Выборка записей из таблицы или представления.
Синтаксис
SELECT [ ALL | DISTINCT [ ON ( уникальное_выражение [. ...] ) ] ]
цель [ AS выходное_имя ] [. ...]
[ FROM источник [ { . | CROSS JOIN } ...] ] [ WHERE условие_фильтрации ]
[ GROUP BY условие_группировки [. ... ] ]
[ HAVING агрегатное_условие [. ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] подзапрос ]
[ ORDER BY выражение [ ASC DESC | USING оператор ] [. ...] ]
[ FOR UPDATE [ OF таблица [. ... 3 3 3
[ LIMIT { число | ALL } [ { OFFSET , } начало ] 3
источник ::= { [ ONLY ] таблица [ * 3
[ [ AS ] псевдоним источника [ ( список_псевдонимов) ] ] |
( подзапрос ) [ [ AS ] псевдоним [ ( список_псевдонимов ) ] ] |
источник [ NATURAL 3 тип_обьединения источник
[ ON ( условие_обьединения ) | USING ( список_полей_объединения ) ] }
тип_обьединения -.-.= [ INNER |
LEFT [ OUTER ]
RIGHT [ OUTER ]
FULL [ OUTER ] ] JOIN
Параметры
ALL | DISTINCT. Ключевое слово DISTINCT означает, что при наличии дубликатов в нескольких записях в выборку включается только одна (первая) запись. Ключевое слово ALL означает, что в выборку включаются все записи независимо от наличия дубликатов (используется по умолчанию). Обратите внимание: секция ORDER BY сортирует записи перед удалением дубликатов. Совместное использование этих секций позволяет убедиться в том, что в выборку включена именно та запись, которая вас интересует. DISTINCT ON. Ключевое слово ON, следующее за ключевым словом DISTINCT, позволяет задать одно или несколько выражений, уникальность которых проверяется командой. уникальное_выражение. Имя поля источника или синтаксически правильное выражение, уникальность значения которого проверяется секцией DISTINCT ON при удалении дубликатов. цель. Имя поля источника или синтаксически правильное выражение. выходное_имя. После ключевого слова AS указывается альтернативное имя выходного поля. В дальнейшем это имя используется при выводе данных, а также для ссылок в секциях ORDER BY и GROUP BY в той же команде SELECT. Помните, что это имя не действует в секциях WHERE и HAVING; в них должны использоваться настоящие имена. FROM источник. Таблица, подзапрос или объединенный набор, из которого берутся данные. { . | CROSS JOIN }. Перечисляемые источники разделяются запятыми или секциями CROSS JOIN. WHERE. Секция WHERE содержит условия фильтрации итогового набора. условие. Логическое выражение, применяемое к негруппированным целевым выражениям. GROUP BY. Секция GROUP BY содержит условия группировки записей. условие группировки. Имя поля источника или синтаксически правильное выражение, используемое для группировки записей. HAVING. Секция HAVING содержит условия фильтрации итогового набора. агрегатное условие. Логическое выражение, применяемое к группированным целевым выражениям. UNION. Слияние двух итоговых наборов, имеющих совместимую структуру, в один объединенный итоговый набор. INTERSECT. Из итогового набора исключаются все записи, не входящие в итоговый набор следующего подзапроса (то есть результат представляет собой пересечение двух множеств записей). EXCEPT. Из итогового набора исключаются все записи, входящие в итоговый набор следующего подзапроса (то есть результат представляет собой разность двух множеств записей). подзапрос. Полная команда SELECT. Чтобы в подзапрос могли входить секции ORDER BY, FOR UPDATE и LIMIT, он должен быть заключен в круглые скобки. ORDER BY. Сортировка итогового набора по заданным критериям. выражение [ ASC | DESC | USING оператор']. Имя поля итогового набора, по которому секция ORDER BY сортирует результаты. Ключевое слово ASC указывает на то, что сортировка выполняется по возрастанию (этот режим используется по умолчанию), а с ключевым словом DESC результат сортируется по убыванию. В секции USING указывается оператор (например, >), используемый для последующих сравнений. FOR UPDATE. Установка блокировки ROW SHARE MODE (см. описание команды LOCK) для таблицы-источника в текущей транзакции. OF таблица. Таблица, для которой устанавливается блокировка ROW SHARE MODE, если секция FROM содержит несколько таблиц. LIMIT. Вывод части полученного итогового набора. ALL | число. С ключевым словом ALL итоговый набор не ограничивается (режим используется по умолчанию). Если задан параметр число, итоговый набор ограничивается заданным количеством записей. { OFFSET | . } начало. Ключевое слово OFFSET (может заменяться запятой после ключевого слова LIMIT) означает, что в итоговом наборе игнорируется указанное количество начальных записей.Перечисленные ниже ключевые слова и параметры могут присутствовать в каждом определении источника.
[ ONLY ] таблица [ * ], Имя существующей таблицы или представления, из которого производится выборка. При отсутствии ключевого слова ONLY поиск также производится во всех производных таблицах. С этой же целью можно добавить после имени таблицы символ *. подзапрос. Команда SELECT, выполняемая в секции FROM другой команды SELECT. В результате выполнения подзапроса создается временная таблица, записи которой обрабатываются во время выполнения команды. Код подзапросов заключается в круглые скобки. [ AS ] псевдоним_источника. Альтернативное имя для источника данных, упоминаемого в секции FROM. список_псевдонимов. Разделенный запятыми список псевдонимов для полей предшествующего источника. Количество псевдонимов в списке может быть меньше количества нолей в источнике, к которому относится список. тип_объедипения. Тип объединения, задается одним из следующих значений: о [ INNER ] JOIN; LEFT [ OUTER ] JOIN; RIGHT [ OUTER ] JOIN; FULL [ OUTER ] JOIN. NATURAL. Необязательное ключевое слово NATURAL означает, что объединение двух источников производится по всем одноименным полям. Наличие ключевого слова NATURAL исключает необходимость объединения по явно заданным условиям. условие_объедипения. После ключевого слова ON указывается условие объединения. Синтаксис аналогичен синтаксису секции WHERE. список_полей_объединения. Список полей, приведенный в секции USING, представляет собой сокращенную запись для секции ON. Предполагается, что объединение производится по совпадающим значениям одноименных полей двух объединяемых источников.Результаты
Основным результатом команды SELECT является список записей итогового набора, за которым следует количество записей в выборке. Также возможны сообщения об ошибках.
ERROR: Relation 'источник' does not exist. Ошибка — указанная таблица или представление отсутствует в текущей базе данных. ERROR: Table name "источник" specified more than once. Ошибка — таблица или представление встречается в списке дважды без определения псевдонима. Ошибка исправляется определением псевдонима в секции AS для одного из источников. ERROR: Attribute 'поле' not found. Ошибка — поле не найдено ни в одном из перечисленных источников.Описание
Команда SELECT предназначена для выборки данных из таблицы, представления, подзапроса или набора, полученного в результате объединения. Секция WHERE определяет критерий отбора записей; если запись не удовлетворяет критерию, она не включается в набор. При отсутствии секции WHERE в выборку включаются все записи источника данных.
В команде SELECT могут включаться различные секции, полный список которых с краткими описаниями приведен выше в пункте «Параметры». Дополнительные примеры их использования имеются в главе 4.
Примеры
Следующая команда возвращает все записи из таблицы books:
booktown=# SELECT * FROM books;
id 1 title | authorjd | subjectjd
7808 | The Shining 4156 9
4513 Dune 1866 | 15
4267 | 2001: A Space Odyssey 2001 15
1608 The Cat in the Hat 1809 2
1590 Bartholomew and the Oobleck 1809 2
25908 Franklin in the Dark 15990 | 2
1501 Goodnight Moon 2031 | 2
190 Little Women 16 6
1234 The Velveteen Rabbit 25041 3
2038 Dynamic Anatomy 1644 | 0
156 The Tell-Tale Heart 115 9
41472 Practical PostgreSQL 1212 4
41473 | Programming Python 7805 | 4
41477 Learning Python 7805 j 4
41478 | Perl Cookbook 7806 | 4
(15 rows)
В другом примере выборка ограничивается записями с кодом, большим 5000:
booktown=# SELECT * FROM books WHERE id > 5000;
id title | authorjd | subjectjd
7808 | The Shining | 4156 | 9 25908
Franklin in the Dark 15990 2
41472 Practical PostgreSQL | 1212 4
41473 I Programming Python 7805 | 4
41477 I Learning Python 7805 | 4
41478 | Perl Cookbook 7806 | 4
(6 rows)
SELECT INTO
Создание повой таблицы по результатам команды SELECT.
Синтаксис
SELECT [ ALL | DISTINCT [ ON ( уникальное_выражение [. ...] ) ] ]
цель [ AS выходное имя ] [, ...] [ INTO [ TEMPORARY | TEMP ] [ TABLE ] новая_таблица ]
[ FROM источник [ { . | CROSS JOIN } ...] ]
[ WHERE условие_фильтрации ]
[ GROUP BY условие_группировки [, ...] ]
[ HAVING агрегатное_усповие [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] подзапрос ]
[ ORDER BY выражение [ ASC | DESC | USING оператор ] [. ...] ]
[ FOR UPDATE [ OF таблица [, ...]]]
[ LIMIT { число | ALL } [ { OFFSET | , } начало ] ]
источник ::= { [ ONLY ] таблица [ * ]
[ [ AS ] псевдоним_исгочника [ ( сгшсок_псевдонимов) ] ] |
( подзапрос ) [ [ AS ] псевдоним [ ( список_псевдонимов ) ] ] |
источник [ NATURAL ] тип_объединения источник
[ ON ( условие_обьединения ) USING ( список_полей_обьединения ) ]
тип_обьединения ::= [ INNER |
LEFT [ OUTER ]
RIGHT [ OUTER ] |
FULL [ OUTER ] ] JOIN
Параметры
Большая часть параметров команды SELECT INTO совпадает с параметрами команды SELECT. В команде SELECT INTO поддерживаются всего два новых параметра.
TEMPORARY, TEMP. Ключевое слово TEMPORARY (или TEMP) означает, что таблица предназначена для временного использования; после завершения сеанса она автоматически уничтожается. новая_таблица. Имя таблицы, создаваемой для хранения записей итогового набора запроса. Таблица создается автоматически и не должна существовать до момента выполнения команды.Результаты
Возможные результаты перечислены в описаниях команд CREATE TABLE и SELECT.
Описание
Команда SELECT INTO выполняет запрос и использует полученные записи для заполнения новой (автоматически созданной) таблицы. Имена полей и типы данных новой таблицы определяются структурой записей, полученных в результате запроса. С функциональной точки зрения команда SELECT INTO эквивалентна команде CREATE TABLE AS. На практике рекомендуется использовать команду CREATE TABLE AS, поскольку команда SELECT INTO не является стандартной и неправильно интерпретируется PL/pgSQL.
Пример
Следующая команда создает временную таблицу для хранения записей о работниках, коды которых меньше 105:
booktown=# SELECT * INTO TEMP TABLE old_emp
booktown-# FROM employees booktown-# WHERE id < 105;
SELECT
SET
Присваивание значений конфигурационным переменным.
Синтаксис
SET переменная {ТО = } { значение \ 'значение' DEFAULT }
SET TIME ZONE { 'часовой_пояс' \ LOCAL DEFAULT }
Параметры
переменная. Имя конфигурационной переменной, которой присваивается новое значение. значение. Новое значение переменной. Ключевое слово DEFAULT возвращает переменной значение по умолчанию. часовой_пояс. Часовой пояс, в котором находится клиент. Допустимые значения: PST8PDT — тихоокеанское стандартное/летнее время (смещение от времени по Гринвичу — 8 часов); EST5EDT — стандартное/летнее время на восточном побережье (смещение от времени по Гринвичу — 5 часов); NZST13NZDT — Новая Зеландия, стандартное/летнее время (смещение от времени по Гринвичу — 13 часов). LOCAL. Часовой пояс определяется конфигурацией локальной системы. DEFAULT. Переменной возвращается значение по умолчанию.Результаты
SET VARIABLE. Сообщение выдается при успешном изменении значения переменной. ERROR: not a valid option name: (имя). Ошибка — переменная с указанным именем не существует. ERROR: permission denied. Ошибка — пользователь не обладает правами для изменения заданной переменной. ERROR: name can only be set at start-up. Ошибка — присваивание указанной переменной может выполняться только при запуске системы.Описание
Команда SET изменяет значения конфигурационных переменных PostgreSQL. Ниже перечислены переменные, с которыми работает команда SET.
CLIENT_ENCODING. Расширенная кодировка, используемая на стороне клиента (ее/и ее поддержка включена в PostgreSQL). DATESTYLE. Стиль представления даты и времени. Значение выбирается из ЧИСЛЕ основных форматов вывода, двух дополнительных форматов или же определяется комбинацией основного формата с дополнительным. Исходный формат даты/времени определяется переменной среды PGDATESTYLE. Также возможш инициализация формата при помощи ключей командной строки postmaster Например, при запуске postmaster с ключом -о "-е" выбирается европейски! формат представления даты (ключи postmaster описаны в главе 9). Ниже пере-числены допустимые стили представления даты и времени. ISO. Датам время выводятся в формате ISO-8601 (маска ГГГГ-ММ-ДД ЧЧ:ММ:СС) Формат используется по умолчанию. SQL. Дата и время выводятся в формате Oracle/Ingres. Несмотря на название, по умолчанию в SQL используется не этот формат, a ISO-8601. Postgres. Традиционный формат даты и времени PostgreSQL. о German. Немецкий формат даты (маска ДД. ММ. ГГГГ). European. Стандартный европейский формат даты, дополнение форматов SQL и PostgreSQL. Дата выводится в формате ДД/ММ/ГГГГ. NonEuropean, US. Стандартный американский формат даты, дополнение форматов SQL и PostgreSQL. Дата выводится в формате ММ/ДД/ГГГГ. SEED. Переменная инициализирует внутренний генератор случайных чисел PostgreSQL, используемый функцией randomO. Допустимыми значениями являются вещественные числа в интервале от 0 до 1. Переданное число умножается на 2:!0. Генератор случайных чисел также инициализируется функцией SQL setseedO с одним аргументом типа double precision. SERVER_ENCODING. Расширенная кодировка, используемая на стороне сервера (если ее поддержка включена в PostgreSQL).Примеры
В следующем фрагменте при помощи переменной DATESTYLE выбирается традиционный формат даты PostgreSQL (Postgres) с дополнительным форматом US, обеспечивающим представление даты по американским стандартам.
booktown=# SET DATESTYLE TO Postgres.US:
SET VARIABLE
В следующем примере выбирается формат представления даты и времени по стандарту ISO:
booktown=# SET DATESTYLE TO ISO:
SET VARIABLE
SET CONSTRAINTS
Выбор режима проверки ограничений в текущей транзакции.
Синтаксис
SET CONSTRAINTS { ALL режим [.... ] }
{ DEFERRED | IMMEDIATE }
Параметры
ALL. Ключевое слово ALL означает, что указанный режим должен относиться ко всем ограничениям в текущей транзакции. режим. Имя ограничения, для которого устанавливается режим проверки. DEFERRED. Проверка ограничений (или конкретного ограничения) откладывается до момента фиксации транзакции, то есть выполнения команды COMMIT. IMMEDIATE. Ограничения (или конкретное ограничение) проверяются в конце каждой команды в транзакционном блоке.Результаты
SET CONSTRAINTS. Сообщение выдается при успешном изменении режима проверки ограничений. ERROR: Constraint 'ограничение' does not exist. Ошибка — при попытке изменения режима проверки указано имя несуществующего ограничения.Описание
Команда SET CONSTRAINTS задает режим проверки для всех ограничений или одного ограничения в текущем транзакционном блоке. Существует два режима проверки: немедленная (IMMEDIATE) и отложенная (DEFERRED) проверка ограничений. В режиме IMMEDIATE все ограничения проверяются после выполнения каждой команды транзакции, а в режиме DEFERRED ограничения проверяются лишь после выполнения команды COMMIT.
ПРИМЕЧАНИЕ
В PostgreSQL версии 7.1.x (последней на момент написания книги) изменение режима проверки поддерживается только для ограничения FOREIGN KEY. Команда SET CONSTRAINTS не распространяется на ограничения CHECK и UNIQUE.
Пример
Следующая команда выбирает режим немедленной проверки всех ограничений в текущей транзакции:
booktown=# SET CONSTRAINTS ALL IMMEDIATE;
SET CONSTRAINTS
SET TRANSACTION
Выбор уровня изоляции текущей транзакции.
Синтаксис
SET TRANSACTION ISOLATION LEVEL
{ READ COMMITTED | SERIALIZABLE }
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL
{ READ COMMITTED | SERIALIZABLE }
Параметры
READ COMMITED. Командам видны изменения, зафиксированные в базе данных до начала транзакции. Режим используется по умолчанию. SERIALIZABLE. Командам видны записи, зафиксированные в базе данных до выполнения первой команды DML в транзакции.Результаты
SET VARIABLE. Это сообщение выдается при успешном изменении режима транзакции. Команда SHOW TRANSACTION ISOLATION LEVEL выводит текущее значение режима изоляции (READ COMMITED или SERIALIZABLE).
Описание
Команда SET TRANSACTION задает уровень изоляции текущей транзакции. Изменение действует только в текущей транзакции; режимы изоляции следующих транзакций должны задаваться отдельно, поскольку по умолчанию действует режим READ COMMITED.
Команда SET TRANSACTION должна вызываться раньше первой команды DML в блоке. К категории DML относятся команды SELECT, INSERT, DELETE, UPDATE, FETCH и COPY.
Чтобы сменить уровень изоляции для всего сеанса (а не в отдельной транзакции), воспользуйтесь командой SET SESSION CHARACTERISTICS с параметром READ COMMITED или SERIALIZABLE. Выполнение команды SET TRANSACTION в транзакции временно переопределяет значение, установленное по умолчанию.
При выборе уровня изоляции READ COMMITED во всех командах транзакции «видны» только те записи, которые были зафиксированы до начала транзакции. При установке уровня изоляции SERIALIZABLE в командах транзакции также видны изменения, внесенные в базу данных до выполнения первой команды DML данной транзакции.
Примеры
Следующая команда устанавливает для текущей транзакции уровень изоляции
SERIALIZABLE:
testdb=# SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET VARIABLE
Ниже приведен пример назначения нового уровня изоляции для текущего сеанса:
testdb=# SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET VARIABLE
SHOW
Вывод значений конфигурационных переменных.
Синтаксис
SHOW переменная
Параметры
Имя конфигурационной переменной.
Результаты
SHOW VARIABLE. Сообщение выдается при успешном выполнении команды SHOW. ERROR: Option 'переменная' is not recognized. Ошибка — переменная с указанным именем не существует. ERROR: permission denied. Ошибка — пользователь не обладает правами, необходимыми для получения информации. NOTICE: Time zone is unknown. Поступил запрос на вывод переменной TIMEZONE, однако значения переменных среды TZ и PGTZ не заданы.Описание
Команда SHOW выводит текущее значение конфигурационной переменной PostgreSQL. Значения переменных присваиваются командой SET или задаются автоматически при запуске сервера.
Примеры
Следующая команда выводит уровень изоляции текущей транзакции:
booktown=# SHOW TRANSACTION ISOLATION LEVEL;
NOTICE: TRANSACTION ISOLATION LEVEL is SERIALIZABLE
SHOW VARIABLE
Другая команда выводит текущий стиль представления даты:
booktown=# SHOW DATESTYLE;
NOTICE: DateStyle is ISO with US (NonEuropean) conventions
SHOW VARIABLE
TRUNCATE
Очистка таблицы.
Синтаксис
TRUNCATE [ TABLE ] таблица
Параметры
Имя таблицы. В результате очистки из таблицы удаляются все записи.
Результаты
TRUNCATE. Сообщение выдается при успешной очистке таблицы. ERROR: Relation 'таблица' does not exist. Ошибка— таблица с указанным именем не найдена в текущей базе данных.Описание
Команда TRUNCATE используется для удаления всех записей из таблицы. Удаление производится без перебора записей, что значительно ускоряет очистку таблиц с большим количеством записей. При полном уничтожении всего содержимого таблицы команда TRUNCATE работает быстрее DELETE.
Пример
Следующая команда удаляет все записи из таблицы temp_emp:
booktown=# TRUNCATE TABLE temp_emp;
TRUNCATE
UNLISTEN
Серверный процесс выходит из режима ожидания уведомлений.
Синтаксис
UNLISTEN { событие \ * }
Параметры
событие. Имя события, ожидаемого сервером. *. Отмена ожидания всех событий, определенных ранее.Результаты
UNLISTEN. Это сообщение выдается при успешном выполнении команды UNLISTEN.
Описание
Команда UNLISTEN прерывает ожидание события с указанным именем. При передаче символа * в текущем сеансе отменяются все зарегистрированные события. При завершении работы серверный процесс автоматически выполняет команду UNLISTEN *, полностью прекращая ожидание событий.
ПРИМЕЧАНИЕ
Чтобы получить список событий, ожидаемых в настоящий момент, воспользуйтесь выборкой из поля relname системной таблицы pgjistener.
Дополнительную информацию о командах NOTIFY и LISTEN, обеспечивающих простой механизм межпроцессных взаимодействий в PostgreSQL, можно найти в описании этих команд.
Пример
Следующий код выводит список всех событий, ожидаемых в настоящий момент, после чего прекращает ожидание события publ i sher_update:
booktown=# SELECT relname FROM pgjistener; «
relname
publisherjjpdate publisher_delete (2 rows)
booktown=# UNLISTEN publisherjjpdate; UNLISTEN
booktown=# SELECT relname FROM pgjistener: relname
publisher_delete
UPDATE
Обновление записей в таблице.
Синтаксис
UPDATE [ ONLY ] таблица SET
поле = выражение [. ...]
[ FROM список_источников ]
[ WHERE условие ]
Параметры
ONLY. Обновление выполняется только в указанной таблице и не распространяется на производные таблицы (если они существуют). таблица. Имя обновляемой таблицы. поле. Имя поля, обновляемого в таблице. выражение. Выражение, результат которого присваивается указанному полю. список_источников. Таблица, представление или другой источник данных (см. описание команды SELECT). Расширенная версия команды UPDATE в PostgreSQL позволяет задействовать значения полей других таблиц в условии WHERE; чтобы данная возможность работала правильно, необходимо перечислить используемые таблицы в секции FROM. условие. В секции WHERE задается критерий отбора обновляемых записей. Условие представляет собой произвольное выражение с результатом типа boolean.Результаты
UPDATE число. Сообщение выдается при успешном выполнении команды UPDATE. В параметре число сообщается количество модифицированных записей. Например, сообщение UPDATE 0 означает, что содержимое таблицы не изменялось. ERROR: Relation 'таблица' does not exist. Ошибка— таблицас указанным именем не найдена в текущей базе данных. ERROR: Relation 'таблица' does not have attribute 'поле'. Ошибка — поле с указанным именем не существует в таблице, использованной в секции SET. ERROR: Cannot update a view without an appropriate rule. Ошибка — попытка обновить представление (вместо таблицы) без правила, указывающего, как поступать в подобных ситуациях.Описание
Команда UPDATE изменяет значения полей во всех записях, удовлетворяющих условию, заданному в секции WHERE. Команда также позволяет модифицировать поля-массивы, в которых можно обновить отдельный элемент, интервал или весь массив. Чтобы обновление выполнялось только в указанной таблице, следует включить в команду ключевое слово ONLY; в противном случае обновление распространяется на все производные таблицы.
ПРИМЕЧАНИЕ
При обновлении таблиц необходимо наличие прав записи для обновляемых полей и прав чтения для полей, используемых в секции WHERE.
Пример
В следующем примере значение поля stock в записи книги с заданным кодом ISBN увеличивается на 1:
booktown=# UPDATE stock SET stock = stock + 1 WHERE isbn = '0385121679':
UPDATE 1
VACUUM
Удаление временных данных и анализ базы данных.
Синтаксис
VACUUM [ VERBOSE ] [ ANALYZE ] [ таблица ]
VACUUM [ VERBOSE ] ANALYZE [ таблица [ (поле [. ...] ) ] ]
Параметры
VERBOSE. Вывод отчета по каждой обработанной таблице. ANALYZE. В процессе выполнения команда VACUUM генерирует статистику для оптимизатора. таблица. Имя обрабатываемой таблицы. Если таблица не указана, команда VACUUM обрабатывает все таблицы базы данных. поле. Имя анализируемого поля (используется при обновлении статистики для оптимизатора).Результаты
VACUUM. Сообщение выдается в том случае, если обработка базы данных или таблицы прошла успешно. NOTICE: - Relation таблица-. Сообщение выводится в начале обработки таблицы в режиме VERBOSE. NOTICE: Pages 1: Changed 1. reaped 1. Empty 0, New 0; Tup 12: Vac 39. Keep/VTL 0/0. Crash 0. UnUsed 0. MinLen 52, MaxLen 76; Re-using: Free/Avail. Space 7180/ 0: EndEmpty/Avall. Pages 0/0. CPU O.OOs/O.OOu sec. Данные, полученные в результате анализа таблицы. NOTICE: Index индекс: Pages 2: Tuples 12: Deleted 39. CPU O.OOs/O.OOu sec. Данные, полученные в результате анализа индекса.Описание
Команда VACUUM удаляет временные данные откаченных транзакций и обновляет статистику в системных каталогах. При вызове с ключевым словом ANALYZE команда генерирует статистическую информацию о данных. Периодический вызов команды повышает быстродействие базы данных.
Пример
Ниже приведен пример выполнения команды VACUUM с ключевым словом VERBOSE для таблицы books:
booktown=# VACUUM VERBOSE books;
NOTICE: --Relation books--
NOTICE: Pages 1: Changed 0. reaped 1. Empty 0. New 0; Tup 15:
Vac 0. Keep/VTL 0/0. Crash 0. UnUsed 5. MinLen 52.
MaxLen 76: Re-using: Free/Avail. Space 7108/0:
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE: Index books_id_pkey: Pages 2; Tuples 15: Deleted 0.
CPU 0.00s/0.00u sec.
