77 / 100

CREATE FUNCTION

Funksiya yaratish

Ushbu qo'llanmada PostgreSQLda foydalanuvchi tomonidan yaratiladigan maxsus funksiyalarni (user-defined functions) tuzish uchun CREATE FUNCTION buyrug'idan qanday foydalanishni o'rganasiz.

PostgreSQL CREATE FUNCTION buyrug'iga kirish

Funksiya — bu ma'lum bir vazifani bajaradigan qayta ishlatiluvchi kod blokidir. Masalan, CONCAT() funksiyasi ikki yoki undan ortiq matnni bitta qatorga birlashtirish imkonini beradi. CONCAT() — bu PostgreSQLning o'rnatilgan (ichki) funksiyasidir.

PostgreSQL CREATE FUNCTION buyrug'i orqali o'z shaxsiy funksiyalaringizni yaratish imkonini beradi. Bunday funksiyalar foydalanuvchi funksiyalari (user-defined functions) deb ataladi.

Foydalanuvchi funksiyasi kirish parametrlarini qabul qiladi, bir yoki bir nechta SQL buyruqlarini bajaradi va bitta yoki bir nechta natijaviy qiymatlarni qaytaradi. Ular murakkab biznes-mantiqni ma'lumotlar bazasida inkapsulyatsiya qilish va qayta-qayta ishlatish uchun xizmat qiladi.

CREATE FUNCTION buyrug'ining asosiy sintaksisi:

query.sql

CREATE [OR REPLACE] FUNCTION function_name (parameters)
RETURNS return_type 
AS 
$$
  -- funksiya tanasi
$$ 
LANGUAGE SQL;

Sintaksis tushuntirishi:

CREATE [OR REPLACE] FUNCTION — yangi funksiya yaratadi. Agar bunday nomdagi funksiya allaqachon mavjud bo'lsa, OR REPLACE opsiyasi uni yangilaydi.
function_name (parameters) — funksiya nomi va uning qabul qiladigan parametrlari ro'yxati.
RETURNS return_type — funksiya qaytaradigan qiymatning ma'lumot turi. Agar funksiya hech qanday qiymat qaytarmasa, return_type sifatida VOID ko'rsatiladi.
AS $$ ... $$ — dollar bilan o'ralgan belgilar ($$) orasida funksiya tanasi joylashadi.
LANGUAGE SQL — funksiya qaysi tilda yozilganini belgilaydi. SQLdan tashqari boshqa protsedurali dasturlash tillari (masalan, PL/pgSQL) ham ishlatilishi mumkin.

Dollar bilan o'ralgan qatorlar ($$)

PostgreSQL funksiya tanasini satr ko'rinishida saqlaydi. Agar oddiy satrlardan ('...') foydalanilsa, funksiya ichidagi barcha qo'shtirnoq va maxsus belgilarga escape qilish (\') talab etiladi. Buni o'qishni osonlashtirish uchun PostgreSQL dollar bilan o'ralgan satr sintaksisini taqdim etadi ($$ ... $$).

Foydalanuvchi funksiyasini chaqirish

Funksiyani chaqirish uchun SELECT buyrug'idan foydalaniladi:

query.sql

SELECT function_name(arguments);

Namunaviy jadvallar

Misollarni amalda sinash uchun quyidagi jadvallarni yaratib olamiz:

query.sql

DROP TABLE IF EXISTS inventories CASCADE;
DROP TABLE IF EXISTS warehouses CASCADE;
DROP TABLE IF EXISTS products CASCADE;

CREATE TABLE products (
    product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE warehouses (
    warehouse_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    warehouse_name VARCHAR(100) NOT NULL,
    address VARCHAR(255) NOT NULL
);

CREATE TABLE inventories (
    product_id INT REFERENCES products(product_id),
    warehouse_id INT REFERENCES warehouses(warehouse_id),
    quantity INT NOT NULL,
    PRIMARY KEY (product_id, warehouse_id)
);

INSERT INTO products (product_name, price) VALUES
('Samsung Galaxy S24', 999.99),
('Apple iPhone 15', 899.99),
('Dell XPS 15', 1499.99);

INSERT INTO warehouses (warehouse_name, address) VALUES
('San Jose Warehouse', '205 E Alma Ave, San Jose, CA 95112'),
('San Francisco Warehouse', '233 E Harris Ave, South San Francisco, CA 94080'),
('Los Angeles Warehouse', '1919 Vineburn Avenue, Los Angeles, CA 90032');

INSERT INTO inventories (product_id, warehouse_id, quantity) VALUES
(1, 1, 120),
(2, 1, 80),
(3, 1, 50),
(1, 2, 150),
(2, 2, 90),
(3, 2, 60),
(1, 3, 110),
(2, 3, 70),
(3, 3, 40);

CREATE FUNCTION amaliy misollari

1) Qiymat qaytarmaydigan funksiya yaratish (VOID)

Quyidagi misol yangi ombor ma'lumotlarini qabul qilib, warehouses jadvaliga qo'shadigan funksiya yaratadi:

query.sql

CREATE OR REPLACE FUNCTION add_warehouse (
  name VARCHAR, 
  location VARCHAR
) 
RETURNS VOID 
AS 
$$
  INSERT INTO warehouses(warehouse_name, address)
  VALUES(name, location);
$$ 
LANGUAGE SQL;

Bu yerda:

add_warehouse ikkita VARCHAR parametr qabul qiladi: name va location.
RETURNS VOID funksiya hech narsa qaytarmasligini bildiradi.
Funksiya ichida INSERT INTO warehouses bajariladi.

Funksiyani chaqiramiz:

query.sql

SELECT add_warehouse('San Mateo', '2222 S Delaware St, San Mateo, CA 94403');

Natijani tekshirish:

query.sql

SELECT warehouse_name, address FROM warehouses;

Natija:

matn

     warehouse_name      |                     address                     
-------------------------+-------------------------------------------------
 San Jose Warehouse      | 205 E Alma Ave, San Jose, CA 95112
 San Francisco Warehouse | 233 E Harris Ave, South San Francisco, CA 94080
 Los Angeles Warehouse   | 1919 Vineburn Avenue, Los Angeles, CA 90032
 San Mateo               | 2222 S Delaware St, San Mateo, CA 94403

2) Bitta qiymat (skalyar) qaytaruvchi funksiya

Quyidagi funksiya barcha mahsulotlarning umumiy zaxira qiymatini hisoblab beradi:

query.sql

CREATE OR REPLACE FUNCTION get_inventory_amount () 
  RETURNS DEC 
AS 
$$
  SELECT SUM(quantity * price)
  FROM inventories
  JOIN products USING (product_id);
$$ 
LANGUAGE SQL;

Funksiyani chaqirish:

query.sql

SELECT get_inventory_amount() AS inventory_amount;

Natija:

matn

 inventory_amount 
------------------
        827992.30

3) Jadval qaytaruvchi funksiya (RETURNS TABLE)

Funksiya butun boshli jadval (bir nechta qator va ustunlar) qaytarishi mumkin. Buning uchun RETURNS TABLE (...) sintaksisi ishlatiladi:

query.sql

CREATE OR REPLACE FUNCTION get_inventory_amount_by_warehouses () 
  RETURNS TABLE (
       warehouse_name VARCHAR, 
       amount DEC
  ) 
AS 
$$
  SELECT
    warehouse_name, 
    SUM(quantity * price) AS amount
  FROM
    inventories
    JOIN products USING (product_id)
    JOIN warehouses USING (warehouse_id)
  GROUP BY warehouse_name
  ORDER BY warehouse_name;
$$ 
LANGUAGE SQL;

Jadval qaytaradigan funksiyani xuddi oddiy jadval kabi FROM bandida chaqirish mumkin:

query.sql

SELECT * FROM get_inventory_amount_by_warehouses();

Natija:

matn

     warehouse_name      |  amount   
-------------------------+-----------
 Los Angeles Warehouse   | 242997.80
 San Francisco Warehouse | 320997.00
 San Jose Warehouse      | 263997.50

4) Tarkibiy tur (Composite type) qaytaruvchi funksiya

Avval product_info nomli tarkibiy turni yaratib olamiz:

query.sql

CREATE TYPE product_info AS (
    product_name VARCHAR, 
    price DECIMAL
);

Endi berilgan ID bo'yicha mahsulot ma'lumotlarini ushbu tur ko'rinishida qaytaruvchi funksiya tuzamiz:

query.sql

CREATE OR REPLACE FUNCTION get_product_info (id INT) 
  RETURNS product_info 
AS 
$$
  SELECT product_name, price
  FROM products
  WHERE product_id = id;
$$ 
LANGUAGE SQL;

Funksiyani chaqirish:

query.sql

SELECT * FROM get_product_info(1);

Natija:

matn

    product_name    | price  
--------------------+--------
 Samsung Galaxy S24 | 999.99

Xulosa

CREATE FUNCTION buyrug'i ma'lumotlar bazasida maxsus foydalanuvchi funksiyalarini yaratadi.
OR REPLACE mavjud funksiyani xatosiz yangilash imkonini beradi.
Funksiya hech qanday qiymat qaytarmasligi (RETURNS VOID), yagona qiymat qaytarishi (RETURNS type), jadval qaytarishi (RETURNS TABLE (...)) yoki kompozit tur qaytarishi mumkin.

Oldingi

Keyingi