Sql

Postgresql function: PostgreSQL : Документация: 9.6: CREATE FUNCTION : Компания Postgres Professional

Список определяемых пользователем функций (UDF) в базе данных PostgreSQL

Запрос ниже возвращает все определяемые пользователем функции и информацию о них в базе данных PostgreSQL.

Примечание

В PostgreSQL 11 введены процедуры, поэтому нам нужно различать процедуры и функции. В этом случае используйте: PostgreSQL 11 Query

Универсальный

Запрос 

 выберите n.nspname как function_schema,
       p.proname как имя_функции,
       l.lanname как function_language,
       случай, когда l.lanname = 'internal', тогда p.prosrc
            иначе pg_get_functiondef(p.oid)
            конец как определение,
       pg_get_function_arguments(p.oid) как function_arguments,
       t.typname как return_type
из pg_proc p
левое соединение pg_namespace n на p.pronamespace = n.oid
левое соединение pg_language l на p.prolang = l.oid
левое соединение pg_type t на t.oid = p.prorettype
где n.nspname не в ('pg_catalog', 'information_schema')
порядок по function_schema,
         имя_функции;

Столбцы

  • function_schema
    — имя схемы, содержащей функцию
  • имя_функции — имя функции
  • function_language — язык, на котором написана функция
  • определение — фактический исходный код функции для интерпретируемых языков, символ ссылки или имя файла (в зависимости от языка)
  • function_arguments — тип аргументов функции
  • return_type — тип возврата функции

Ряды

  • Одна строка — представляет одну функцию
  • Объем строк: — все функции в базе данных
  • Заказано по — схема функции, имя функции

Результаты проб

PostgreSQL 11+

Запрос 

 выберите n. nspname как имя_схемы,
       p.proname как определенное_имя,
       чехол п.прокинд
            когда 'f', то 'ФУНКЦИЯ'
            когда "p", то "ПРОЦЕДУРА"
            когда «а», то «ОБЪЕДИНИТЬ»
            когда 'w', то 'WINDOW'
            конец как добрый,
       l.lanname как язык,
       случай, когда l.lanname = 'internal', тогда p.prosrc
            иначе pg_get_functiondef(p.oid)
            конец как определение,
       pg_get_function_arguments(p.oid) в качестве аргументов,
       t.typname как return_type
из pg_proc p
левое соединение pg_namespace n на p.pronamespace = n.oid
левое соединение pg_language l на p.prolang = l.oid
левое соединение pg_type t на t.oid = p.prorettype
где n.nspname не в ('pg_catalog', 'information_schema')
порядок по имени_схемы,
         конкретное_имя;

Столбцы

  • имя_схемы — имя схемы, содержащей функцию/процедуру
  • Specific_name — имя функции/процедуры
  • вид :
    • ФУНКЦИЯ — нормальная функция
    • ПРОЦЕДУРА — процедура
    • АГРЕГАТ — агрегатная функция
    • ОКНО — оконная функция
  • язык — язык, на котором написана функция/процедура
  • определение — фактический исходный код функции/процедуры для интерпретируемых языков, символ ссылки или имя файла (в зависимости от языка)
  • аргументы — аргументы функции/процедуры
  • return_type — тип возврата

Ряды

  • Одна строка — представляет одну функцию или процедуру
  • Область действия строк: — все функции и процедуры в базе данных
  • Заказ — имя схемы, конкретное имя

Результаты проб

Включите JavaScript для просмотра комментарии на базе Disqus.

Как создавать и использовать процедуры и функции в PostgreSQL

Введение

 

PostgreSQL — самая популярная система объектно-реляционных баз данных. Это надежная, высокопроизводительная система баз данных. Кроме того, с открытым исходным кодом и бесплатно. В этой статье мы обсудим, как использовать процедуры и функции для выполнения таких операций, как вставка, удаление, обновление и выбор.

 

Функция

 

В общем, функция представляет собой набор операторов SQL, которые выполняют любую операцию, такую ​​как выбор, вставка, удаление и обновление. В PostgreSQL есть два типа функций: «определяемые системой функции» и «определяемые пользователем функции». В этой статье мы обсудим определяемую пользователем функцию.

 

Синтаксис

  1. СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ФУНКЦИЮ имя_функции (список-параметров)  
  2. ВОЗВРАТЫ return_type  
  3. ЯЗЫК plpgsql  
  4. КАК  
  5. $$  
  6.     ОБЪЯВЛЕНИЕ  
  7.     — переменные  
  8.     НАЧАЛО  
  9.         — операторы SQL (логика)  
  10.     КОНЕЦ  
  11. $$  

 Листинг 1.

 

Преимущество

 

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

 

Ограничение

 

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

 

Процедура

 

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

 

Синтаксис

  1. СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ПРОЦЕДУРУ имя_процедуры (список параметров)  
  2. ЯЗЫК plpgsql  
  3. КАК  
  4. $$  
  5. ОБЪЯВИТЬ  
  6. — Переменные  
  7. НАЧАЛО  
  8.   
  9.     — операторы SQL (логика)  
  10.   
  11. КОНЕЦ  
  12. $$  

 Листинг 2.

 

Создать базу данных

 

Чтобы создать базу данных, щелкните правой кнопкой мыши базы данных и выберите Создать базу данных, как показано на рисунке 1.

Рисунок 1.

 

В общем случае вкладка устанавливает имя базы данных в этой демонстрации. В нашем случае мы используем «EmployeeManagementSystem».

 

 

Рисунок 2.

 

Создание таблицы

 

Давайте создадим таблицу Employees в базе данных EmployeeManagementSystem, используя следующий сценарий SQL, как показано в листинге 1.0003

  1. СОЗДАТЬ ТАБЛИЦУ Сотрудники  
  2. (  
  3.     Серийный идентификатор,  
  4.     Имя VARCHAR(100),  
  5.     DateOfBirth Date,  
  6.     Город VARCHAR(100),  
  7.     Обозначение VARCHAR(100),  
  8.     Дата присоединения   
  9. )  

Листинг 3.  

 

Нажмите кнопку «Выполнить», чтобы выполнить приведенный выше сценарий, как показано на рисунке 3.

 

Тип параметров

 

Прежде чем создавать процедуру и функцию, давайте обсудим тип параметров, есть три типа параметров, мы можем использовать in функцию и процедуру:

  • IN
  • ВЫХОД
  • ВХОД

В

IN представляет параметр типа ввода. Он используется для передачи значения в функцию или процедуру, по умолчанию все параметры имеют тип ввода, если мы не используем ключевое слово IN после имени параметра.

 

OUT

 

OUT представляет параметры типа вывода. Он возвращает значение; вы можете передать его как null или он может быть неинициализирован, потому что эти типы параметра используются только для установки и возврата значения из функции и процедуры параметров можно использовать для передачи значения, а также для возврата значения из функции или процедуры.

 

Создать процедуру

 

Используйте приведенный ниже сценарий для создания процедуры с именем «AddEmployee». Это вставит информацию о сотруднике в таблицу сотрудников.

  1. СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ПРОЦЕДУРУ AddEmployee  
  2. (  
  3.     EmpId INOUT INT,  
  4.     EmpName VARCHAR(100),  
  5.     DATE EmpDob,  
  6.     EmpCity VARCHAR(100),  
  7.     EmpDesignation VARCHAR(100),  
  8.     EmpJoiningDate DATE  
  9. )  
  10. ЯЗЫК plpgsql КАК  
  11. $$  
  12. НАЧАЛО         
  13.    ВСТАВИТЬ В Сотрудники (Имя,ДатаРождения,Город,Должность,Дата приема на работу) ЗНАЧЕНИЯ   
  14.     (EmpName,  
  15.      EmpDob,  
  16.      EmpCity,  
  17.      ИмпНазначение,  
  18.      EmpJoiningDate  
  19.     ) ВОЗВРАЩЕНИЕ Id В EmpId;
  20. КОНЕЦ  
  21. $$;

Листинг 4.

 

Рисунок 4.

 

Давайте выполним эту процедуру, используя оператор SQL, как показано в листинге 5. Он вставит записи о сотрудниках в таблицу сотрудников.

  1. CALL AddEmployee(null,’Питер Паркер’,’1997-10-01′,’Нью-Йорк’,’Веб-разработчик’,’2020-11-01′)  

Листинг 5.

 

Рисунок 5.

 

Теперь проверьте таблицу, чтобы увидеть вставленную запись с помощью оператора help select, как показано в листинге 6.

  1. ВЫБЕРИТЕ * ИЗ Сотрудников  

Листинг 6.

 

Рисунок 6.

 

 Теперь мы создадим процедуру для обновления записи о сотруднике, как показано в листинге 7.

  • (  
  •     EmpId INT,  
  •     EmpName VARCHAR(100),  
  •     DATE EmpDob,  
  •     EmpCity VARCHAR(100),  
  •     EmpDesignation VARCHAR(100),  
  •     EmpJoiningDate DATE  
  • )  
  • ЯЗЫК plpgsql КАК  
  • $$  
  • НАЧАЛО         
  •    ОБНОВЛЕНИЕ Набор сотрудников   
  •    Имя = EmpName,  
  •    DateOfBirth = EmpDob,  
  •    Город = EmpCity,  
  •    Обозначение = EmpDesignation,  
  •    JoiningDate = EmpJoiningDate  
  •    Где Id = EmpId;
  • КОНЕЦ  
  • $$;

    • Листинг 7.

     

    Рис. 7.

     

    Следуйте листингу 8, чтобы вызвать процедуру UpdateEmployee, которая позволит обновить записи о сотрудниках.

    1. CALL UpdateEmployee(1,’Peter S Parker’,’1999-10-01′,’Нью-Йорк’,’Web Developer’,’2020-11-01′)  

    Листинг 8.

     

    Рисунок 8.

     

    Мы создали процедуры для вставки и обновления, теперь мы создадим процедуру, которая позволит нам удалять записи о сотрудниках. См. листинг 8.9.0003

    1. СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ПРОЦЕДУРУ DeleteEmployee  
    2. (  
    3.     EmpId INT  
    4. )  
    5. ЯЗЫК plpgsql КАК  
    6. $$  
    7. НАЧАЛО  
    8.     УДАЛИТЬ ИЗ Сотрудников, ГДЕ Id = EmpId;
    9. КОНЕЦ  
    10. $$;

     Листинг 8.

     

     

    0003

    1. ЗВОНОК DeleteEmployee(2)  

    Листинг 9.

    Рисунок 10.

    Теперь пришло время создавать функции, создайте функцию getallemployee (), которая вернет всех сотрудников, см. Листинг 10.

    1. Создать или заменить функцию getallempule ().
    2. ВОЗВРАТЫ Сотрудники  
    3. ЯЗЫК SQL   
    4. КАК   
    5. $$  
    6.     SELECT * FROM Employees;
    7. $$;

     Листинг 10.

     

     

     

    Рисунок 11.

     

    Оператор Select будет использоваться для запуска и получения данных из функции GetAllEmployee(), как показано в листинге 11. 

    1. SELECT * FROM GetAllEmployees()  

      Листинг 11.

     

    Рисунок 12.

     

    Приведенная ниже функция GetemployeeById(), показанная в листинге 12, возвращает одну строку для определенного идентификатора сотрудника.

    1. СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ФУНКЦИЮ GetEmployeeById(EmpId INT)  
    2. ВОЗВРАТЫ Сотрудники  
    3. ЯЗЫК SQL   
    4. КАК   
    5. $$  
    6.     SELECT * FROM Employees WHERE Id = EmpId;
    7. $$;

      Листинг 12.

     

    Рисунок 13.

     

    Давайте передадим идентификатор сотрудника «3», чтобы получить запись о сотруднике с оператором выбора.

    1. ВЫБЕРИТЕ * ИЗ GetEmployeeById(3)  

      Листинг 13.

     

     

    Рис. 14.

     

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

    1. СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ФУНКЦИЮ GetEmployeeAge(EmpId INT, Age OUT VARCHAR(100))  
    2. ЯЗЫК plpgsql   
    3. КАК   
    4. $$  
    5. НАЧАЛО  
    6.     SELECT AGE(NOW()::Date,DateOfBirth) в Age FROM Employees WHEREId = EmpId;
    7. КОНЕЦ;
    8. $$  

     Листинг 14.

    Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *