Создание функций, параметры, возвращаемые значения, перегрузка
Функции — это именованные блоки кода, которые принимают параметры и возвращают значения. В этой теме вы изучите создание функций, различные способы возврата данных, параметры и перегрузку функций.
CREATE OR REPLACE FUNCTION function_name(
param1 type,
param2 type DEFAULT default_value
)
RETURNS return_type AS $$
BEGIN
-- тело функции
RETURN value;
END;
$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION get_full_name(
first_name TEXT,
last_name TEXT
)
RETURNS TEXT AS $$
BEGIN
RETURN first_name || ' ' || last_name;
END;
$$ LANGUAGE plpgsql;
-- Вызов
SELECT get_full_name('John', 'Doe'); -- 'John Doe'PostgreSQL поддерживает несколько языков для функций:
| Язык | Описание |
|---|---|
SQL | Чистый SQL, без процедурной логики |
plpgsql | Процедурный язык PostgreSQL |
plpython3u | Python (требует расширения) |
plperl | Perl |
C | Нативный код (для расширений) |
CREATE OR REPLACE FUNCTION get_user_count()
RETURNS BIGINT AS $$
SELECT COUNT(*) FROM users;
$$ LANGUAGE sql;
-- Функция с параметром на SQL
CREATE OR REPLACE FUNCTION get_user_by_id(user_id INTEGER)
RETURNS users AS $$
SELECT * FROM users WHERE id = user_id;
$$ LANGUAGE sql;Когда использовать SQL:
CREATE OR REPLACE FUNCTION get_user_email(user_id INTEGER)
RETURNS TEXT AS $$
DECLARE
email TEXT;
BEGIN
SELECT u.email INTO email
FROM users u
WHERE u.id = user_id;
RETURN email;
END;
$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION get_user_stats()
RETURNS TABLE (
total_users BIGINT,
active_users BIGINT,
avg_age NUMERIC
) AS $$
BEGIN
RETURN QUERY
SELECT
COUNT(*),
COUNT(*) FILTER (WHERE is_active = true),
AVG(age)
FROM users;
END;
$$ LANGUAGE plpgsql;
-- Вызов
SELECT * FROM get_user_stats();-- Возвращает множество строк одного типа
CREATE OR REPLACE FUNCTION get_active_users()
RETURNS SETOF users AS $$
BEGIN
RETURN QUERY
SELECT * FROM users WHERE is_active = true;
END;
$$ LANGUAGE plpgsql;
-- Вызов
SELECT * FROM get_active_users();
-- С использованием TABLE
CREATE OR REPLACE FUNCTION get_users_by_age(min_age INTEGER, max_age INTEGER)
RETURNS SETOF TABLE (
id INTEGER,
name TEXT,
email TEXT
) AS $$
BEGIN
RETURN QUERY
SELECT u.id, u.name, u.email
FROM users u
WHERE u.age BETWEEN min_age AND max_age;
END;
$$ LANGUAGE plpgsql;
-- Вызов
SELECT * FROM get_users_by_age(18, 30);CREATE OR REPLACE FUNCTION get_user_record(user_id INTEGER)
RETURNS RECORD AS $$
DECLARE
result RECORD;
BEGIN
SELECT * INTO result
FROM users WHERE id = user_id;
RETURN result;
END;
$$ LANGUAGE plpgsql;
-- Вызов (нужно указать структуру)
SELECT * FROM get_user_record(1) AS (id INTEGER, name TEXT, email TEXT);CREATE OR REPLACE FUNCTION get_user_emails()
RETURNS TEXT[] AS $$
DECLARE
emails TEXT[];
BEGIN
SELECT ARRAY_AGG(u.email) INTO emails
FROM users u WHERE u.is_active = true;
RETURN emails;
END;
$$ LANGUAGE plpgsql;
-- Вызов
SELECT get_user_emails(); -- {email1, email2, ...}CREATE OR REPLACE FUNCTION get_user_json(user_id INTEGER)
RETURNS JSONB AS $$
DECLARE
result JSONB;
BEGIN
SELECT jsonb_build_object(
'id', u.id,
'name', u.name,
'email', u.email
) INTO result
FROM users u WHERE u.id = user_id;
RETURN result;
END;
$$ LANGUAGE plpgsql;
-- Вызов
SELECT get_user_json(1); -- {"id": 1, "name": "...", "email": "..."}CREATE OR REPLACE FUNCTION demo_params(
IN a INTEGER, -- входной (по умолчанию)
OUT sum_result INTEGER, -- выходной
INOUT both INTEGER -- вход и выход
) AS $$
BEGIN
sum_result := both + a;
both := both * 2;
END;
$$ LANGUAGE plpgsql;
-- Вызов
SELECT * FROM demo_params(5, 10); -- sum_result=15, both=20| Режим | Описание |
|---|---|
IN | Входной параметр (по умолчанию) |
OUT | Выходной параметр (возвращается) |
INOUT | И входной, и выходной |
VARIADIC | Переменное число аргументов |
CREATE OR REPLACE FUNCTION create_user(
name TEXT,
email TEXT DEFAULT NULL,
age INTEGER DEFAULT 18,
is_active BOOLEAN DEFAULT TRUE
)
RETURNS INTEGER AS $$
DECLARE
new_id INTEGER;
BEGIN
INSERT INTO users (name, email, age, is_active)
VALUES (name, email, age, is_active)
RETURNING id INTO new_id;
RETURN new_id;
END;
$$ LANGUAGE plpgsql;
-- Вызов с разными комбинациями
SELECT create_user('John'); -- только name
SELECT create_user('John', 'john@example.com'); -- name, email
SELECT create_user('John', 'john@example.com', 25); -- name, email, age-- Вызов с именованными параметрами
SELECT create_user(
name := 'John',
age := 25,
email := 'john@example.com'
);
-- Можно пропускать параметры с DEFAULT
SELECT create_user(
name := 'John',
is_active := FALSE
);CREATE OR REPLACE FUNCTION sum_all(VARIADIC numbers INTEGER[])
RETURNS INTEGER AS $$
DECLARE
total INTEGER := 0;
num INTEGER;
BEGIN
FOREACH num IN ARRAY numbers LOOP
total := total + num;
END LOOP;
RETURN total;
END;
$$ LANGUAGE plpgsql;
-- Вызов
SELECT sum_all(1, 2, 3, 4, 5); -- 15
SELECT sum_all(10, 20); -- 30PostgreSQL позволяет создавать несколько функций с одним именем, но разными параметрами:
-- Функция для чисел
CREATE OR REPLACE FUNCTION add_values(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql;
-- Функция для строк (конкатенация)
CREATE OR REPLACE FUNCTION add_values(a TEXT, b TEXT)
RETURNS TEXT AS $$
BEGIN
RETURN a || b;
END;
$$ LANGUAGE plpgsql;
-- Функция для чисел с плавающей точкой
CREATE OR REPLACE FUNCTION add_values(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql;
-- Вызов (PostgreSQL выбирает нужную версию по типам аргументов)
SELECT add_values(1, 2); -- INTEGER версия
SELECT add_values(1.5, 2.5); -- NUMERIC версия
SELECT add_values('Hello', 'World'); -- TEXT версияВажно: Перегрузка возможна только если параметры различаются по:
-- IMMUTABLE: всегда возвращает одинаковый результат для одинаковых входов
CREATE OR REPLACE FUNCTION add(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- STABLE: результат не меняется в рамках одной транзакции
CREATE OR REPLACE FUNCTION get_user_name(user_id INTEGER)
RETURNS TEXT AS $$
DECLARE
name TEXT;
BEGIN
SELECT u.name INTO name FROM users u WHERE u.id = user_id;
RETURN name;
END;
$$ LANGUAGE plpgsql STABLE;
-- VOLATILE (по умолчанию): результат может меняться
CREATE OR REPLACE FUNCTION get_random_number()
RETURNS INTEGER AS $$
BEGIN
RETURN random()::INTEGER;
END;
$$ LANGUAGE plpgsql; -- VOLATILE по умолчанию| Категория | Описание | Примеры |
|---|---|---|
IMMUTABLE | Всегда одинаковый результат, можно кэшировать | add(a, b), upper(text) |
STABLE | Одинаково в рамках транзакции, зависит от данных | SELECT FROM table, CURRENT_DATE |
VOLATILE | Может меняться даже в рамках транзакции | random(), NOW(), INSERT/UPDATE |
-- Функция возвращает NULL, если любой аргумент NULL
CREATE OR REPLACE FUNCTION multiply(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
RETURN a * b;
END;
$$ LANGUAGE plpgsql STRICT; -- или RETURNS NULL ON NULL INPUT
-- Вызов
SELECT multiply(5, 3); -- 15
SELECT multiply(5, NULL); -- NULL (функция не вызывается)
SELECT multiply(NULL, 3); -- NULL (функция не вызывается)-- SECURITY INVOKER (по умолчанию): выполняется с правами вызывающего
CREATE OR REPLACE FUNCTION get_user_data()
RETURNS SETOF users AS $$
SELECT * FROM users;
$$ LANGUAGE sql SECURITY INVOKER;
-- SECURITY DEFINER: выполняется с правами создателя функции
CREATE OR REPLACE FUNCTION get_sensitive_data()
RETURNS SETOF sensitive_table AS $$
SELECT * FROM sensitive_table;
$$ LANGUAGE sql SECURITY DEFINER;Использование SECURITY DEFINER:
search_pathCREATE OR REPLACE FUNCTION get_sensitive_data()
RETURNS SETOF sensitive_table AS $$
SELECT * FROM sensitive_table;
$$ LANGUAGE sql SECURITY DEFINER
SET search_path = public, pg_temp; -- защита от подмены объектовCREATE OR REPLACE FUNCTION get_paginated_users(
page INTEGER DEFAULT 1,
page_size INTEGER DEFAULT 10
)
RETURNS TABLE (
id INTEGER,
name TEXT,
email TEXT,
total_count BIGINT
) AS $$
BEGIN
RETURN QUERY
SELECT
u.id,
u.name,
u.email,
COUNT(*) OVER () as total_count
FROM users u
ORDER BY u.id
LIMIT page_size
OFFSET (page - 1) * page_size;
END;
$$ LANGUAGE plpgsql;
-- Вызов
SELECT * FROM get_paginated_users(1, 20);CREATE OR REPLACE FUNCTION soft_delete_user(user_id INTEGER)
RETURNS BOOLEAN AS $$
BEGIN
UPDATE users
SET
is_active = FALSE,
deleted_at = NOW()
WHERE id = user_id AND deleted_at IS NULL;
RETURN FOUND; -- TRUE если строка обновлена
END;
$$ LANGUAGE plpgsql;
-- Вызов
SELECT soft_delete_user(1); -- TRUE или FALSE-- Таблица для кэша
CREATE TABLE function_cache (
key TEXT PRIMARY KEY,
value JSONB,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE OR REPLACE FUNCTION get_cached_user_data(
user_id INTEGER,
cache_ttl INTERVAL DEFAULT INTERVAL '5 minutes'
)
RETURNS JSONB AS $$
DECLARE
cache_key TEXT := 'user_' || user_id;
cached_data JSONB;
result_data JSONB;
BEGIN
-- Проверка кэша
SELECT fc.value INTO cached_data
FROM function_cache fc
WHERE fc.key = cache_key
AND fc.created_at > NOW() - cache_ttl;
IF cached_data IS NOT NULL THEN
RETURN cached_data;
END IF;
-- Получение данных из БД
SELECT jsonb_build_object(
'id', u.id,
'name', u.name,
'email', u.email
) INTO result_data
FROM users u WHERE u.id = user_id;
-- Сохранение в кэш
INSERT INTO function_cache (key, value)
VALUES (cache_key, result_data)
ON CONFLICT (key) DO UPDATE
SET value = result_data, created_at = NOW();
RETURN result_data;
END;
$$ LANGUAGE plpgsql;-- Просмотр всех функций
SELECT
routine_name,
routine_schema,
data_type
FROM information_schema.routines
WHERE routine_schema = 'public';
-- Просмотр определения функции
\d+ function_name
-- Удаление функции
DROP FUNCTION IF EXISTS function_name(type1, type2);
-- Переименование функции
ALTER FUNCTION old_name(type) RENAME TO new_name;
-- Изменение владельца
ALTER FUNCTION function_name(type) OWNER TO new_owner;Теперь вы умеете создавать функции с различными типами возврата и параметрами. В следующей теме вы изучите триггеры — функции, которые автоматически выполняются при изменении данных.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.