Postgresql процедура значение по умолчанию

CREATE PROCEDURE — создать процедуру

Синтаксис

Описание

Команда CREATE PROCEDURE определяет новую процедуру. CREATE OR REPLACE PROCEDURE создаёт новую процедуру либо заменяет определение уже существующей. Чтобы определить процедуру, необходимо иметь право USAGE для соответствующего языка.

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

Команда CREATE OR REPLACE PROCEDURE предназначена для изменения текущего определения существующей процедуры. С её помощью нельзя изменить имя или типы аргументов (если попытаться сделать это, будет создана новая отдельная процедура).

Когда команда CREATE OR REPLACE PROCEDURE заменяет существующую процедуру, владелец и права доступа к этой процедуре не меняются. Все другие свойства процедуры получают значения, задаваемые командой явно или по умолчанию. Чтобы заменить процедуру, необходимо быть её владельцем (или быть членом роли-владельца).

Владельцем процедуры становится создавший её пользователь.

Чтобы создать процедуру, необходимо иметь право USAGE для типов её аргументов.

За дополнительной информацией о разработке процедур обратитесь к Разделу 38.4.

Параметры

Имя создаваемой процедуры (возможно, дополненное схемой). режим_аргумента

Режим аргумента: IN , INOUT или VARIADIC . По умолчанию подразумевается IN . (Режим OUT для процедур в настоящее время не поддерживается. Используйте вместо него INOUT .) имя_аргумента

Имя аргумента. тип_аргумента

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

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

Ссылка на тип столбца записывается в виде имя_таблицы . имя_столбца %TYPE . Иногда такое указание бывает полезно, так как позволяет создать процедуру, независящую от изменений в определении таблицы. выражение_по_умолчанию

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

Имя языка, на котором реализована функция. Это может быть sql , c , internal либо имя процедурного языка, определённого пользователем, например, plpgsql . Стиль написания этого имени в апострофах считается устаревшим и требует точного совпадения регистра. TRANSFORM < FOR TYPE имя_типа > [, . ] >

Устанавливает список трансформаций, которые должны применяться при вызове процедуры. Трансформации выполняют преобразования между типами SQL и типами данных, специфичными для языков; см. CREATE TRANSFORM . Преобразования встроенных типов обычно жёстко предопределены в реализациях процедурных языков, так что их здесь указывать не нужно. Если реализация процедурного языка не может обработать тип и трансформация для него отсутствует, будет выполнено преобразование типов по умолчанию, но это зависит от реализации. [ EXTERNAL ] SECURITY INVOKER
[ EXTERNAL ] SECURITY DEFINER

Характеристика SECURITY INVOKER (безопасность вызывающего) показывает, что процедура будет выполняться с правами пользователя, вызвавшего её. Этот вариант подразумевается по умолчанию. Вариант SECURITY DEFINER (безопасность определившего) обозначает, что процедура выполняется с правами пользователя, владеющего ей.

Ключевое слово EXTERNAL (внешняя) допускается для соответствия стандарту SQL, но является необязательным, так как, в отличие от SQL, эта характеристика распространяется на все процедуры, а не только внешние.

В процедуре с характеристикой SECURITY DEFINER не могут выполняться операторы управления транзакциями (например, COMMIT и ROLLBACK в некоторых языках). параметр_конфигурации
значение

Предложение SET определяет, что при вызове процедуры указанный параметр конфигурации должен принять заданное значение, а затем восстановить своё предыдущее значение при завершении процедуры. Предложение SET FROM CURRENT сохраняет в качестве значения, которое будет применено при входе в процедуру, значение, действующее в момент выполнения CREATE PROCEDURE .

Если в определение процедуры добавлено SET , то действие команды SET LOCAL , выполняемой внутри процедуры для того же параметра, ограничивается телом процедуры: предыдущее значение параметра так же будет восстановлено при завершении процедуры. Однако обычная команда SET (без LOCAL ) переопределяет предложение SET , как и предыдущую команду SET LOCAL : действие такой команды будет сохранено и после завершения процедуры, если только не произойдёт откат транзакции.

Если к определению процедуры добавлено SET , то в этой процедуре не могут выполняться операторы управления транзакциями (например, COMMIT и ROLLBACK в некоторых языках).

За подробными сведениями об именах и значениях параметров обратитесь к SET и Главе 19. определение

Строковая константа, определяющая реализацию процедуры; её значение зависит от языка. Это может быть имя внутренней процедуры, путь к объектному файлу, команда SQL или код на процедурном языке.

Часто бывает полезно заключать определение процедуры в доллары (см. Подраздел 4.1.2.4), а не в традиционные апострофы. Если не использовать доллары, все апострофы и обратные косые черты в определении процедуры придётся экранировать, дублируя их. объектный_файл , объектный_символ

Эта форма предложения AS применяется для динамически загружаемых процедур на языке C, когда имя процедуры в коде C не совпадает с именем процедуры в SQL. Строка объектный_файл задаёт имя файла, содержащего скомпилированную процедуру на C (данная команда воспринимает эту строку так же, как и LOAD ). Строка объектный_символ задаёт символ скомпонованной процедуры, то есть имя процедуры в исходном коде на языке C. Если объектный символ не указан, предполагается, что он совпадает с именем определяемой SQL-процедуры.

Если повторные вызовы CREATE PROCEDURE ссылаются на один и тот же объектный файл, он загружается в рамках сеанса только один раз. Чтобы выгрузить и загрузить этот файл снова (например, в процессе разработки), начните новый сеанс.

Замечания

Дополнительные детали создания функций, которые применимы и к процедурам, описываются в CREATE FUNCTION .

Чтобы выполнить процедуру, воспользуйтесь командой CALL .

Примеры

Совместимость

Команда CREATE PROCEDURE определена в стандарте SQL. Её реализация в PostgreSQL близка к стандартизированной, но совместима с ней не полностью. Дополнительные подробности можно найти в описании CREATE FUNCTION .

Источник

Postgresql процедура значение по умолчанию

CREATE PROCEDURE — создать процедуру

Синтаксис

Описание

Владельцем процедуры становится создавший её пользователь.

Чтобы создать процедуру, необходимо иметь право USAGE для типов её аргументов.

За дополнительной информацией о разработке процедур обратитесь к Разделу 35.4.

Параметры

Имя создаваемой процедуры (возможно, дополненное схемой). режим_аргумента

Имя аргумента. тип_аргумента

За подробными сведениями об именах и значениях параметров обратитесь к SET и Главе 18. определение

Замечания

Дополнительные детали создания функций, которые применимы и к процедурам, описываются в CREATE FUNCTION .

Чтобы выполнить процедуру, воспользуйтесь командой CALL .

Примеры

Совместимость

Команда CREATE PROCEDURE определена в стандарте SQL. Её реализация в Postgres Pro близка к стандартизированной, но совместима с ней не полностью. Дополнительные подробности можно найти в описании CREATE FUNCTION .

Источник

Postgresql процедура значение по умолчанию

Все переменные, используемые в блоке, должны быть определены в секции объявления. (За исключением переменной-счётчика цикла FOR , которая объявляется автоматически. Для цикла по диапазону чисел автоматически объявляется целочисленная переменная, а для цикла по результатам курсора — переменная типа record .)

Переменные PL/pgSQL могут иметь любой тип данных SQL, такой как integer , varchar , char .

Примеры объявления переменных:

Общий синтаксис объявления переменной:

Предложение DEFAULT , если присутствует, задаёт начальное значение, которое присваивается переменной при входе в блок. Если отсутствует, то переменная инициализируется SQL -значением NULL . Указание CONSTANT предотвращает изменение значения переменной после инициализации, таким образом, значение остаётся постоянным в течение всего блока. Параметр COLLATE определяет правило сортировки, которое будет использоваться для этой переменной (см. Подраздел 43.3.6). Если указано NOT NULL , то попытка присвоить NULL во время выполнения приведёт к ошибке. Все переменные, объявленные как NOT NULL , должны иметь непустые значения по умолчанию. Можно использовать знак равенства ( = ) вместо совместимого с PL/SQL := .

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

43.3.1. Объявление параметров функции

Переданные в функцию параметры именуются идентификаторами $1 , $2 и т. д. Дополнительно, для улучшения читаемости, можно объявить псевдонимы для параметров $ n . Либо псевдоним, либо цифровой идентификатор используются для обозначения параметра.

Создать псевдоним можно двумя способами. Предпочтительный способ это дать имя параметру в команде CREATE FUNCTION , например:

Другой способ это явное объявление псевдонима при помощи синтаксиса:

Предыдущий пример для этого стиля выглядит так:

Примечание

Эти два примера не полностью эквивалентны. В первом случае на subtotal можно ссылаться как sales_tax.subtotal , а во втором случае такая ссылка невозможна. (Если бы к внутреннему блоку была добавлена метка, то subtotal можно было бы дополнить этой меткой.)

Ещё несколько примеров:

Когда функция на PL/pgSQL объявляется с выходными параметрами, им выдаются цифровые идентификаторы $ n и для них можно создавать псевдонимы точно таким же способом, как и для обычных входных параметров. Выходной параметр это фактически переменная, стартующая с NULL и которой присваивается значение во время выполнения функции. Возвращается последнее присвоенное значение. Например, функция sales_tax может быть переписана так:

Обратите внимание, что мы опустили RETURNS real — хотя можно было и включить, но это было бы излишним.

Чтобы вызвать функцию с параметрами OUT , при вызове функции не указывайте выходные параметры:

Выходные параметры наиболее полезны для возвращения нескольких значений. Простейший пример:

Как обсуждалось в Подразделе 38.5.4, здесь фактически создаётся анонимный тип record для возвращения результата функции. Если используется предложение RETURNS , то оно должно выглядеть как RETURNS record .

Выходные параметры поддерживаются и в процедурах, например:

При вызове процедуры нужно указать все параметры. Вызывая процедуру на чистом SQL, вместо выходных параметров можно указать NULL :

Однако в вызове процедуры из PL/pgSQL для каждого выходного параметра нужно указать переменную; эта переменная будет получать результат вызова. За подробностями обратитесь к Подразделу 43.6.3.

Есть ещё способ объявить функцию на PL/pgSQL с использованием RETURNS TABLE , например:

Это в точности соответствует объявлению одного или нескольких параметров OUT и указанию RETURNS SETOF некий_тип .

Для функции на PL/pgSQL , возвращающей полиморфный тип (см. Подраздел 38.2.5), создаётся специальный параметр $0 . Его тип данных соответствует типу, фактически возвращаемому функцией, который устанавливается на основании фактических типов входных параметров. Это позволяет функции обращаться к фактически возвращаемому типу данных, как показано в Подразделе 43.3.3. Параметр $0 инициализируется в NULL и его можно изменять внутри функции. Таким образом, его можно использовать для хранения возвращаемого значения, хотя это необязательно. Параметру $0 можно дать псевдоним. В следующем примере функция работает с любым типом данных, поддерживающим оператор + :

Такой же эффект получается при объявлении одного или нескольких выходных параметров полиморфного типа. При этом $0 не создаётся; выходные параметры сами используются для этой цели. Например:

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

Показанная функция позволяет выполнить такой вызов:

В данном случае целочисленные аргументы будут автоматически приведены к numeric. В функции же, использующей anyelement , вам нужно было бы преобразовать все три аргумента к одному вручную.

43.3.2. ALIAS

Синтаксис ALIAS более общий, чем предполагалось в предыдущем разделе: псевдонимы можно объявлять для любых переменных, а не только для параметров функции. Основная практическая польза в том, чтобы назначить другие имена переменным с предопределёнными названиями, таким как NEW или OLD в триггерной функции.

Поскольку ALIAS даёт два различных способа именования одних и тех же объектов, то его неограниченное использование может привести к путанице. Лучше всего использовать ALIAS для переименования предопределённых имён.

43.3.3. Наследование типов данных

Конструкция %TYPE предоставляет тип данных переменной или столбца таблицы. Её можно использовать для объявления переменных, содержащих значения из базы данных. Например, для объявления переменной с таким же типом, как и столбец user_id в таблице users нужно написать:

Используя %TYPE , не нужно знать тип данных структуры, на которую вы ссылаетесь. И самое главное, если в будущем тип данных изменится (например: тип данных для user_id поменяется с integer на real ), то вам может не понадобиться изменять определение функции.

Использование %TYPE особенно полезно в полиморфных функциях, поскольку типы данных, необходимые для внутренних переменных, могут меняться от одного вызова к другому. Соответствующие переменные могут быть созданы с применением %TYPE к аргументам и возвращаемому значению функции.

43.3.4. Типы кортежей

Переменная составного типа называется переменной-кортежем (или переменной типа кортежа). Значением такой переменной может быть целый кортеж, полученный в результате выполнения запроса SELECT или FOR , при условии, что набор столбцов запроса соответствует заявленному типу переменной. Доступ к отдельным полям значения кортежа осуществляется как обычно, через точку, например rowvar.field .

Переменная-кортеж может быть объявлена с таким же типом, как и строка в существующей таблице или представлении, используя нотацию имя_таблицы %ROWTYPE ; или с именем составного типа. (Поскольку каждая таблица имеет соответствующий составной тип с таким же именем, то на самом деле в PostgreSQL не имеет значения, пишете ли вы %ROWTYPE или нет. Но использование %ROWTYPE более переносимо.)

Параметры функции могут быть составного типа (строки таблицы). В этом случае соответствующий идентификатор $ n будет переменной-кортежем, поля которой можно выбирать, например $1.user_id .

Ниже приведён пример использования составных типов. table1 и table2 это существующие таблицы, имеющие, по меньшей мере, перечисленные столбцы:

43.3.5. Тип record

Переменные типа record похожи на переменные-кортежи, но они не имеют предопределённой структуры. Они приобретают фактическую структуру от строки, которая им присваивается командами SELECT или FOR . Структура переменной типа record может меняться каждый раз при присваивании значения. Следствием этого является то, что пока значение не присвоено первый раз, переменная типа record не имеет структуры и любая попытка получить доступ к отдельному полю приведёт к ошибке во время исполнения.

Обратите внимание, что RECORD это не подлинный тип данных, а только лишь заполнитель. Также следует понимать, что функция на PL/pgSQL , имеющая тип возвращаемого значения record , это не то же самое, что и переменная типа record , хотя такая функция может использовать переменную типа record для хранения своего результата. В обоих случаях фактическая структура строки неизвестна во время создания функции, но для функции, возвращающей record , фактическая структура определяется во время разбора вызывающего запроса, в то время как переменная типа record может менять свою структуру на лету.

43.3.6. Упорядочение переменных PL/pgSQL

Когда функция на PL/pgSQL имеет один или несколько параметров сортируемых типов данных, правило сортировки определяется при каждом вызове функции в зависимости от правил сортировки фактических аргументов, как описано в Разделе 24.2. Если оно определено успешно (т. е. среди аргументов нет конфликтов между неявными правилами сортировки), то все соответствующие параметры неявно трактуются как имеющее это правило сортировки. Внутри функции это будет влиять на поведение операторов, зависящих от используемого правила сортировки. Рассмотрим пример:

В первом случае less_than будет использовать для сравнения общее правило сортировки для text_field_1 и text_field_2 , в то время как во втором случае будет использоваться правило C .

Кроме того, определённое для вызова функции правило сортировки также будет использоваться для любых локальных переменных соответствующего типа. Таким образом, функция не станет работать по-другому, если её переписать так:

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

Локальная переменная может иметь правило сортировки, отличное от правила по умолчанию. Для этого используется параметр COLLATE в объявлении переменной, например:

Этот параметр переопределяет правило сортировки, которое получила бы переменная в соответствии с вышеуказанными правилами.

И, конечно же, можно явно указывать параметр COLLATE для конкретных операций внутри функции, если к ним требуется применить конкретное правило сортировки. Например:

Как и в обычной SQL-команде, это переопределяет правила сортировки, связанные с полями таблицы, параметрами и локальными переменными, которые используются в данном выражении.

Источник