Функции и процедуры postgresql: PostgreSQL : Документация: 11: 38.4. Пользовательские процедуры : Компания Postgres Professional

Хранимые процедуры, функции и триггеры [АйТи бубен]

  • SQL

  • Управляющие конструкции SQL (if, case и т.д.)

  • Хранимая процедура — это объект базы данных, представляющий собой набор SQL- инструкций, который компилируется один раз и хранится на сервере.

  • Хранимая функция отличается от хранимой пороцедуры, тем что хранимая функция всегда возвращает только скаляр(то есть единичное значение), а процедура возвращает набор значений.

  • Триггер (trigger) — это хранимая процедура, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено действием по модификации данных: добавлением INSERT, удалением DELETE строки в заданной таблице, или изменением UPDATE данных в определенном столбце заданной таблицы реляционной базы данных. В PostgreSQL триггеры создаются на основе существующих функции, т.е. сначала командой CREATE FUNCTION определяется триггерная функция, затем на ее основе командой CREATE TRIGGER определяется собственно триггер. Посмотреть существующие триггеры:

    select * from pg_trigger;

  • Есть ли в PostgreSQL хранимые процедуры? В чистом виде нет.

  • Почему не работают BEGIN, ROLLBACK и COMMIT внутри хранимых процедур/функций?

  • Вместо CREATE RULE в 99% будет правильно использовать CREATE TRIGGER

  • Пример функции(PL/pgSQL), которая выводит текущую дату

    CREATE OR REPLACE FUNCTION getDate() RETURNS text AS 
$BODY$
BEGIN
RETURN CURRENT_TIMESTAMP;
END
$BODY$
LANGUAGE 'plpgsql' ;

  • Функция возвращающая много строк

    CREATE OR REPLACE FUNCTION g_peer() RETURNS SETOF text AS
$BODY$
DECLARE
	r text;
BEGIN
FOR r IN SELECT DISTINCT "peer_id"
	FROM "ratesheets"
	ORDER BY peer_id
LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql'

  • Функция возвращает сведения о времени последнего процесa autovacuum, autoanalyze. Возвращающей столбец записей.

    CREATE OR REPLACE FUNCTION sql_last_v_a() RETURNS SETOF record AS'
SELECT schemaname,relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze
FROM pg_stat_all_tables
ORDER BY schemaname, relname;
'LANGUAGE sql;

Триггеры Движок БД MySQL. Поддержка для триггеров включена, начиная с MySQL 5.0.2.

Триггер представляет собой именованный объект базы данных (хранимая процедура), который связан с таблицей, и он будет активизирован, когда специфическое событие INSERT, UPDATE и DELETE (вставка, обновление строки или удаление) происходит для таблицы. Триггеры могут срабатывать как до BEFORE так и после AFTER изменения таблицы.

CREATE TRIGGER trigger_name trigger_time trigger_event
  ON tbl_name FOR EACH ROW trigger_stmt

где

trigger_name — название триггера
trigger_time — Время срабатывания триггера. BEFORE — перед событием. AFTER — после события. 
trigger_event — Событие:
insert — событие возбуждается операторами insert, data load, replace
update — событие возбуждается оператором update
delete — событие возбуждается операторами delete, replace.
Операторы DROP TABLE и TRUNCATE не активируют выполнение триггера
tbl_name — название таблицы
trigger_stmt выражение, которое выполняется при активации триггера

  • Просмотреть все триггеры в конкретной базе данных. Чтобы вывести все триггеры, определенных для базы данных dbname, нужно выполнить запрос к таблице INFORMATION_SCHEMA.TRIGGERS:

    SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, 
ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='dbname';

    или

    SHOW TRIGGERS FROM dbname;

  • Для удаления триггера нужно указывать схему

    mysql> DROP TRIGGER dbname.my_trigger;

Задача. Нужно после вставки (INSERT) строки в таблицу cdr, обновить (UPDATE) таблицу actions внеся в нее значение id новой записи таблицы cdr.

mysql> DELIMITER //
mysql> CREATE TRIGGER `ti_cdr` AFTER INSERT ON `mbillcc`.`cdr`
    -> FOR EACH ROW
    -> BEGIN
    -> UPDATE actions SET state = NEW.id WHERE action = 'cdrrow_end';
    -> END//
Query OK, 0 ROWS affected (0.00 sec)
 
mysql> DELIMITER ;

NEW — для доступа к новым записям; OLD — для доступа к старым записям

Отличия функций от хранимых процедур в языке T-SQL (Microsoft SQL Server) | Info-Comp.ru

Приветствую всех посетителей сайта Info-Comp.ru! Сегодня мы с Вами поговорим о том, чем же отличаются функции от хранимых процедур в Microsoft SQL Server, и для наглядности сформируем итоговую таблицу отличий.

Заметка! Обзор Azure Data Studio. Что это за инструмент и для чего он нужен.

В Microsoft SQL Server существуют такие объекты базы данных, как функции и хранимые процедуры, которые используются для хранения неких уже реализованных алгоритмов на языке T-SQL.

Примечание! В данном материале речь идет о пользовательских функциях и хранимых процедурах, не о системных, которые уже реализованы в SQL Server, а о тех, которые создаем мы с Вами, т.е. программисты T-SQL.

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

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


У начинающих может возникнуть вопрос – а чем отличаются функции от процедур? Или это одно и то же?

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

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

Функция

Хранимая процедура

Функция должна возвращать значение.Хранимая процедура может как возвращать, так и не возвращать значение.
Функции не могут возвращать несколько результирующих наборов.Хранимая процедура может сформировать и вернуть несколько результирующих наборов данных.
Функции можно использовать в операторе SELECT.Процедуры нельзя использовать в операторе SELECT и во всех его секциях (WHERE, JOIN, HAVING и т.д.), так как процедуры вызываются с помощью команды EXECUTE или EXEC.
В функциях можно использовать только оператор SELECT на выборку данных.

Операторы DML (INSERT, UPDATE, DELETE) для модификации данных использовать нельзя.

В хранимых процедурах можно использовать оператор SELECT, а также операторы DML (INSERT, UPDATE, DELETE) для модификации данных.
Из функции нельзя вызвать хранимые процедуры.В хранимых процедурах можно вызывать и функции, и другие хранимые процедуры.
Конструкцию для обработки ошибок TRY CATCH нельзя использовать в функциях. Так же как нельзя использовать инструкцию RAISERROR.В хранимых процедурах можно использовать и конструкцию TRY CATCH, и инструкцию RAISERROR.
В функциях запрещено использование транзакций.В хранимых процедурах транзакции разрешены.
В функциях можно использовать только табличные переменные, временные таблицы использовать не получится.В хранимых процедурах можно использовать как табличные переменные, так и временные таблицы.
В функциях нельзя использовать динамический SQL.В процедурах можно использовать динамический SQL.
В функциях можно использовать только входные параметры.В хранимых процедурах можно использовать как входные, так и выходные параметры.

Заметка! Курсы по T-SQL для начинающих.

На сегодня это все, надеюсь, материал был Вам полезен, пока!

В чем разница между «Хранимой процедурой» и другими типами функций в PostgreSQL?

спросил

Изменено
3 года, 3 месяца назад

Просмотрено
12 тысяч раз

Я понимаю разницу между:

  • Скалярные функции
  • Set-Returning-Functions (SRF) s
  • Внутренние функции
  • Оконные функции
  • Агрегированные функции всех видов
  • Пользовательские функции (которые в PostgreSQL могут быть реализованы на любом языке)
  • И т. д.

В SQL Server «Хранимые процедуры» разрешены через EXEC . Что это дает по сравнению с любой другой функцией, выполняемой с помощью SELECT , которая возвращает NULL ?

Когда PostgreSQL получит хранимые процедуры, что они мне принесут и каково формальное различие между функцией и хранимой процедурой в спецификации?

Я читал этот вопрос, но, похоже, он предшествует объявлению о реализации

2

Поскольку функции Postgres ( CREATE FUNCTION ) только выполняются в контексте (одной) транзакции, некоторые важные команды не могут выполняться внутри тела функции. Нравится СОЗДАТЬ БАЗУ ДАННЫХ или СОЗДАТЬ ИНДЕКС ОДНОВРЕМЕННО или ВАКУУМ . Руководство:

VACUUM не может выполняться внутри блока транзакции.

Функции часто называют «хранимыми процедурами», что всегда было вводящим в заблуждение термином — вероятно, заимствованным из других СУБД. С появлением процедур SQL ( CREATE PROCEDURE ) в Postgres 11 этого неправильного названия следует полностью избегать.

Процедуры SQL могут начинать и завершать транзакции. Но команды, упомянутые выше, вообще не разрешены внутри какого-либо блока транзакций, поэтому они также не могут быть включены в процедуры SQL (пока).

Несколько наборов результатов запланированы на будущее, но еще не реализованы.

Связанный:

  • Когда использовать хранимую процедуру/определяемую пользователем функцию?
  • Функция Производительность

Функции PostgreSQL не могут запускать или завершать транзакции. Они застряли с любой транзакцией, которую они унаследовали от своего вызывающего запроса.

Когда PostgreSQL получит хранимые процедуры, они смогут открывать и закрывать транзакции.

См. также

  • https://www.postgresql.org/message-id/178d3380-0fae-2982-00d6-c43100bc8748%402ndquadrant. com

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя электронную почту и пароль

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания, политикой конфиденциальности и политикой использования файлов cookie

postgresql — Когда использовать хранимую процедуру/определяемую пользователем функцию?

Я запутался в использовании SP (хранимые процедуры) и UDF (определяемые пользователем функции). Как правило, также возможно писать код в программах вне базы данных.

Есть ли какой-нибудь общий совет, когда их использовать?

  • postgresql
  • хранимые процедуры
  • функции

1

Postgres 11 или новее

Процедуры SQL (не функции ) могут COMMIT и запускать новые транзакции:

  • Руководство для CREATE0PROCEDURE 90.

  • Запись в блоге 2ndquadrant, представляющая эту функцию.

Однако команды, которые не разрешены в блоке транзакций, не могут (пока) быть включены. Важные примеры СОЗДАТЬ БАЗУ ДАННЫХ или СОЗДАТЬ ИНДЕКС ОДНОВРЕМЕННО или ВАКУУМ .

Процедуры And могут возвращать только одну результирующую строку, если они созданы с параметрами INOUT .
Postgres 14 или более поздней версии также позволяет использовать параметры OUT .

Postgres 10 или старше (исходный ответ)

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

  • В чем разница между «Хранимой процедурой» и другими типами функций в PostgreSQL?

Обработка данных в SQL может иметь значительные преимущества по сравнению с извлечением всех необработанных данных в ваше приложение и выполнением там вычислений:

  • Каковы плюсы и минусы выполнения вычислений в sql по сравнению с вашим приложением

Тот же принцип может применяться к более крупным операциям, которые нельзя выполнить в одном операторе, даже при использовании «сцепленных» команд в CTE (модифицирующем данные):

  • Являются ли запросы типа SELECT единственным типом, который может быть вложенным?
  • RETURN QUERY-Record в PostgreSQL

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