sql — Процедура c циклом на pl/pgsql
Вопрос задан
Изменён 5 месяцев назад
Просмотрен 32 раза
Подскажите реализацию следующего алгоритма на pg/plsql. Сам алгоритм придумал, но не знаю как его реализовать в рамках postgres
Есть две таблицы
create table organisation(
id int4,
full_name varchar(512),
name varchar(255),
short_name varchar(255),
activity_id int4
)
create table contract(
id int4,
registry_number varchar(30),
provider_id int4
)
В таблице организаций есть дублирующие организации, к которым привязаны контракты в таблице контрактов(id из organisation прописан в поле provider_id в таблице contract).
Нужно поменять привязку контрактов с дублирующих организаций на оригинальные.
Найти все дублирующие организации в таблице organisation:
select * from organisation o where activity_id is null and name is null and short_name = ''
Далее в цикле по каждой записи из выборки выше сделать следующие действия:
- Найти и запомнить id оригинальной организации(origin_org_id) для дублирующей организации (dubl_org_id) текущий итерации (у них совпадает поле full_name, но оно в разных регистрах):
select id from organisation o where lower (full_name) = lower (<< full_name current iteration with dubl_org_id >>) and name is not null order by id asc
- В таблице контрактов в записи, которые привязаны к dubl_org_id текущей итерации в поле provider_id проставляем origin_org_id:
update contract set provider_id = origin_org_id where provider_id = dubl_org_id
- sql
- postgresql
- plpgsql
10
Зарегистрируйтесь или войдите
Регистрация через Google
Регистрация через Facebook
Регистрация через почту
Отправить без регистрации
Почта
Необходима, но никому не показывается
Отправить без регистрации
Почта
Необходима, но никому не показывается
Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки
PSQL-01.
Основы PL/pgSQL — курсы в Санкт-ПетербургеЦелью реализации программы (учебного модуля) «PSQL-01. Основы PL/pgSQL» является формирование у слушателей специальных знаний и компетенций, необходимых для разработки программных модулей в базах данных PostgreSQL на основе использования языков SQL и PL/pgSQL.
В результате освоения модуля обучающийся должен уметь:
• использовать различные типы данных и функции в запросах SQL и PL/pgSQL;
• создавать функции и триггера на языке PL/pgSQL;
• конструировать код запросов динамически из кода приложения;
• работать с курсорами;
• работать с массивами;
• настраивать триггеры, проводить перехват ошибок в исполняемом коде.
В результате освоения модуля обучающийся должен знать:
• преимущества использования PL/pgSQL;
• основы языка PL/pgSQL;
• встроенные функции PostgreSQL;
• поддерживаемые типы данных аргументов и возвращаемых значений.
В результате освоения модуля обучающийся должен приобрести практический опыт:
• использования различных типов данных и функций в запросах SQL и PL/pgSQL;
• разработки и отладки блоков кода на языке PL/pgSQL.
Тема 1. Основы языка PL/pgSQL
1.1 PL/pgSQL — процедурный язык SQL
Введение в язык PL/pgSQL.
Общие сведения о процедурных языках.
Работа с переменными и выражениями.
Основные операторы.
1.2 Управляющие структуры
Блоки и их структура.
Условные операторы.
Простые циклы.
Цикл по результатам запроса
1.3 Пользовательские функции
Использование функций в PL/pgSQL.
Создание пользовательских функций.
Объявление параметров функции.
Практические занятия
Написание функций в PL/pgSQL
Тема 2. Работа с запросами SQL в PL/pgSQL
2.1 SQL-операторы и выражения внутри функции на PL/pgSQL
Выполнение инструкций SQL.
Подстановка переменных.
Использование табличных функций.
Практические занятия
Использование запросов в блоках PL/pgSQL
Тема 3. Использование курсоров в PL/pgSQL
3.1 Введение в курсоры
Общие сведения о курсорах.
Объявление курсорных переменных.
Открытие курсора.
3.2 Работа с курсорами
Использование курсоров для обработки данных.
Операции с курсорами.
Обработка курсора в цикле
Практические занятия
Работа с курсорами
Тема 4. Динамическое построение запросов
4.1 Динамические запросы
Назначение и возможности динамических запросов.
Выполнение динамического запроса.
Конструкции динамических запросов
Практические занятия
Динамические запросы
Тема 5. Работа с массивами
5.1 Работа с массивами
Общие сведения о массивах.
Использование функций для обработки массивов.
Работа с массивами средствами PL/pgSQL.
Цикл по элементам массива
Практические занятия
Работа с массивами
Тема 6. Исключения: перехват ошибок
6.1 Сообщения и ошибки
Необходимость работы с исключениями.
Вывод сообщений и ошибок.
Перехват ошибок.
Журналирование ошибок.
Уровни обработки ошибок
Практические занятия
Работа с исключениями
Тема 7. Использование триггеров
7.1 Триггеры при изменении данных
Общие сведения о триггерах.
Функции-обработчики триггеров.
PostgreSQL: Документация: 15: 43.1. Обзор
- 43.1.1. Преимущества использования PL/pgSQL
- 43.1.2. Поддерживаемые типы данных аргументов и результатов
PL/pgSQL — это загружаемый процедурный язык для системы баз данных PostgreSQL. Целью разработки PL/pgSQL было создание загружаемого процедурного языка, который
можно использовать для создания функций, процедур и триггеров,
добавляет управляющие структуры в язык SQL,
может выполнять сложные вычисления,
наследует все пользовательские типы, функции, процедуры и операторы,
может быть определен сервером как доверенный,
прост в использовании.
Функции, созданные с помощью PL/pgSQL, можно использовать везде, где можно использовать встроенные функции. Например, можно создавать сложные функции условных вычислений, а затем использовать их для определения операторов или использовать их в индексных выражениях.
В PostgreSQL 9.0 и более поздних версиях PL/pgSQL устанавливается по умолчанию. Однако это по-прежнему загружаемый модуль, поэтому особо заботящиеся о безопасности администраторы могут удалить его.
43.1.1. Преимущества использования PL/pgSQL
SQL — это язык, который PostgreSQL и большинство других реляционных баз данных используют в качестве языка запросов. Он портативный и простой в освоении. Но каждый оператор SQL должен выполняться отдельно сервером базы данных.
Это означает, что ваше клиентское приложение должно отправлять каждый запрос на сервер базы данных, ждать его обработки, получать и обрабатывать результаты, выполнять некоторые вычисления, а затем отправлять дальнейшие запросы на сервер.
С помощью PL/pgSQL вы можете сгруппировать блок вычислений и серию запросов внутри сервера базы данных, таким образом, обладая мощью процедурного языка и простотой использования SQL, но со значительной экономией связи клиент/сервер накладные расходы.
Устранены лишние круговые поездки между клиентом и сервером
Промежуточные результаты, которые не нужны клиенту, не нужно упорядочивать или передавать между сервером и клиентом
Можно избежать нескольких раундов синтаксического анализа запроса
Это может привести к значительному увеличению производительности по сравнению с приложением, которое не использует хранимые функции.
Кроме того, с PL/pgSQL вы можете использовать все типы данных, операторы и функции SQL.
43.1.2. Поддерживаемые типы данных аргументов и результатов
Функции, написанные на PL/pgSQL, могут принимать в качестве аргументов любой скалярный или массивный тип данных, поддерживаемый сервером, и они могут возвращать результат любого из этих типов. Они также могут принимать или возвращать любой составной тип (тип строки), указанный по имени. Также можно объявить функцию PL/pgSQL принимающей запись , что означает, что в качестве входных данных будет использоваться любой составной тип, или как возвращаемая запись , что означает, что результатом является тип строки, столбцы которого определяются спецификацией в вызывающем запросе, как обсуждалось в разделе 7.2.1.4.
Функции PL/pgSQL могут быть объявлены так, чтобы они принимали переменное количество аргументов, используя маркер VARIADIC . Это работает точно так же, как и для функций SQL, как описано в разделе 38.5.6.
Функции PL/pgSQL также могут быть объявлены так, чтобы они принимали и возвращали полиморфные типы, описанные в Разделе 38.
2.5, что позволяет фактическим типам данных, обрабатываемым функцией, изменяться от вызова к вызову. Примеры приведены в Разделе 43.3.1.
Функции PL/pgSQL также могут быть объявлены для возврата «набора» (или таблицы) любого типа данных, которые могут быть возвращены как отдельный экземпляр. Такая функция генерирует свои выходные данные, выполняя RETURN NEXT для каждого желаемого элемента набора результатов или используя RETURN QUERY для вывода результата оценки запроса.
Наконец, можно объявить функцию PL/pgSQL, возвращающую void , если она не имеет полезного возвращаемого значения. (В качестве альтернативы в этом случае это можно было бы записать в виде процедуры.)
Функции PL/pgSQL также могут быть объявлены с выходными параметрами вместо явного указания типа возвращаемого значения. Это не добавляет никаких фундаментальных возможностей языку, но часто бывает удобно, особенно для возврата нескольких значений. Обозначение RETURNS TABLE также можно использовать вместо RETURN SETOF .
Конкретные примеры приведены в Разделе 43.3.1 и Разделе 43.6.1.
Архивы PL/pgSQL — Digital Owl’s Prose
Недавно опубликовав этот пост в блоге о котировках долларов в PLpgSQL, я счел уместным продолжить пост о параметрах функций. В этом посте я продемонстрирую 2 варианта простой функции, обе из которых принимают параметр. Тем не менее, способ доступа к параметру и его указания отличаются.
[Направляйтесь сюда, чтобы вести отличный блог PostgresSQL >>>]
Нравится:
Нравится Загрузка…
Наряду с написанием качественных постов в блогах по SQL с открытым исходным кодом у меня появилось стремление стать хорошо разбирающимся программистом PostgreSQL PLpgSQL. Какая прекрасная возможность поделиться тем, что я узнаю о PLpgSQL, в своем блоге, одновременно приобретая прочные навыки работы с SQL. В этом посте я расскажу о 9Предложение 0075 SELECT INTO
STRICT для еще более тонкой настройки функциональности.
[Направляйтесь сюда, чтобы вести отличный блог PostgresSQL >>>]
Нравится:
Нравится Загрузка…
Начни говорить о долларах, и все внимание мира привлечет к тебе внимание. Ну, в данном случае речь идет не столько о долларах в денежном смысле, сколько в программном. Вы, вероятно, задаетесь вопросом, о чем я говорю. Прочтите и узнайте вместе со мной об удобной функции, которую предоставляет процедурный язык PostgreSQL PLpgSQL при создании функций и хранимых процедур.
[Направляйтесь сюда, чтобы вести отличный блог PostgresSQL >>>]
Нравится:
Нравится Загрузка…
Меня весьма заинтриговал процедурный язык PostgreSQL, PLpgSQL (честно говоря, хранимые процедуры и функции SQL в целом). Я полюбил писать функции на «серверной стороне», исследуя их использование и преимущества. Но что можно сделать с помощью простых (er) функций? Давайте учиться вместе.
(далее…)
Нравится:
Нравится Загрузка.