2. 首页 > PostgreSQL教程 > 正文

PostgreSQL教程FG052-PG标量函数与表函数：使用场景与区别

教程整理：风哥教程  |  更新时间：2026-01-22  |  教程分类：PostgreSQL教程  |  文档学习：25

本文档风哥主要介绍PostgreSQL教程052相关内容。风哥教程参考PostgreSQL官方文档Server Administration, SQL Language内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

1. 标量函数概述

标量函数（Scalar Function）返回单个值，可以在SQL表达式中直接使用。

学习交流加群风哥微信: itpux-com

特点：标量函数每处理一行数据返回一个结果，可以嵌套使用，适用于数据转换、计算等场景。

— 查看内置标量函数示例
SELECT
UPPER(‘hello’) AS upper_result,
LENGTH(‘PostgreSQL’) AS length_result,
ROUND(123.456, 2) AS round_result,
TO_CHAR(CURRENT_DATE, ‘YYYY-MM-DD’) AS format_date;

执行结果：

upper_result | length_result | round_result | format_date
————–+—————+————–+————-
HELLO | 10 | 123.46 | 2026-04-04
(1 row)

2. 标量函数创建与使用

2.1 创建基本标量函数

— 创建计算税后价格的标量函数
CREATE OR REPLACE FUNCTION fgedu_calc_tax_price(
p_price NUMERIC,
p_tax_rate NUMERIC DEFAULT 0.13
) RETURNS NUMERIC AS $$
BEGIN
RETURN ROUND(p_price * (1 + p_tax_rate), 2);
END;
$$ LANGUAGE plpgsql;

— 测试标量函数
SELECT
fgedu_calc_tax_price(100) AS price_with_default_tax,
fgedu_calc_tax_price(100, 0.10) AS price_with_custom_tax,
fgedu_calc_tax_price(199.99) AS price_199_99;

执行结果：

price_with_default_tax | price_with_custom_tax | price_199_99
————————+———————–+————–
113.00 | 110.00 | 225.99
(1 row)

2.2 在查询中使用标量函数

— 创建测试表
CREATE TABLE fgedu_products_scalar (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100),
base_price NUMERIC(10, 2),
category VARCHAR(50)
);

INSERT INTO fgedu_products_scalar (product_name, base_price, category) VALUES
(‘笔记本电脑’, 5999.00, ‘电子产品’),
(‘无线鼠标’, 99.00, ‘电子产品’),
(‘机械键盘’, 399.00, ‘电子产品’),
(‘办公椅’, 899.00, ‘家具’),
(‘书架’, 299.00, ‘家具’);

— 在查询中使用标量函数
SELECT
product_id,
product_name,
base_price,
fgedu_calc_tax_price(base_price) AS price_with_tax,
CASE
WHEN base_price > 1000 THEN fgedu_calc_tax_price(base_price, 0.10)
ELSE fgedu_calc_tax_price(base_price)
END AS discounted_tax_price
FROM fgedu_products_scalar
ORDER BY base_price DESC;

执行结果：

from oracle:www.itpux.com更多视频教程www.fgedu.net.cn

product_id | product_name | base_price | price_with_tax | discounted_tax_price
————+————–+————+—————-+———————
1 | 笔记本电脑 | 5999.00 | 6778.87 | 6598.90
4 | 办公椅 | 899.00 | 1015.87 | 1015.87
3 | 机械键盘 | 399.00 | 450.87 | 450.87
5 | 书架 | 299.00 | 337.87 | 337.87
2 | 无线鼠标 | 99.00 | 111.87 | 111.87
(5 rows)

2.3 创建复杂标量函数

— 创建格式化姓名的标量函数
CREATE OR REPLACE FUNCTION fgedu_format_name(
p_first_name VARCHAR,
p_last_name VARCHAR,
p_format VARCHAR DEFAULT ‘full’
) RETURNS VARCHAR AS $$
BEGIN
IF p_first_name IS NULL AND p_last_name IS NULL THEN
RETURN ‘未知’;
END IF;

CASE LOWER(p_format)
WHEN ‘full’ THEN
RETURN COALESCE(p_last_name, ”) || COALESCE(p_first_name, ”);
WHEN ‘short’ THEN
RETURN LEFT(COALESCE(p_last_name, ‘?’), 1) || ‘.’ || LEFT(COALESCE(p_first_name, ‘?’), 1);
WHEN ‘last_first’ THEN
RETURN COALESCE(p_last_name, ”) || ‘ ‘ || COALESCE(p_first_name, ”);
ELSE
RETURN COALESCE(p_last_name, ”) || COALESCE(p_first_name, ”);
END CASE;
END;
$$ LANGUAGE plpgsql;

— 测试姓名格式化函数
SELECT
fgedu_format_name(‘三’, ‘张’) AS full_name,
fgedu_format_name(‘三’, ‘张’, ‘short’) AS short_name,
fgedu_format_name(‘三’, ‘张’, ‘last_first’) AS last_first_name,
fgedu_format_name(NULL, ‘张’) AS null_first,
fgedu_format_name(NULL, NULL) AS both_null;

执行结果：

full_name | short_name | last_first_name | null_first | both_null
———–+————+—————–+————+———–
风哥1号 | 张.三 | 张 三 | 张 | 未知
(1 row)

3. 表函数概述

表函数（Table Function）返回一个结果集（多行多列），可以在FROM子句中使用。

更多学习教程公众号风哥教程itpux_com

特点：表函数返回一个表，可以像普通表一样查询、连接、过滤，适用于生成序列、拆分数据、复杂计算等场景。

— 查看内置表函数示例
SELECT * FROM generate_series(1, 5) AS num;

执行结果：

num
—–
1
2
3
4
5
(5 rows)

4. 表函数创建与使用

4.1 创建基本表函数

— 创建生成日期序列的表函数
CREATE OR REPLACE FUNCTION fgedu_generate_dates(
p_start_date DATE,
p_end_date DATE
) RETURNS TABLE (
date_value DATE,
day_of_week VARCHAR,
is_weekend BOOLEAN
) AS $$
BEGIN
RETURN QUERY
SELECT
d.date_value,
TO_CHAR(d.date_value, ‘DY’) AS day_of_week,
EXTRACT(DOW FROM d.date_value) IN (0, 6) AS is_weekend
FROM generate_series(p_start_date, p_end_date, INTERVAL ‘1 day’) AS d(date_value);
END;
$$ LANGUAGE plpgsql;

— 测试日期序列表函数
SELECT * FROM fgedu_generate_dates(‘2026-04-01’, ‘2026-04-07’);

执行结果：

date_value | day_of_week | is_weekend
————-+————-+————
2026-04-01 | WED | f
2026-04-02 | THU | f
2026-04-03 | FRI | f
2026-04-04 | SAT | t
2026-04-05 | SUN | t
2026-04-06 | MON | f
2026-04-07 | TUE | f
(7 rows)

4.2 创建字符串拆分表函数

— 创建字符串拆分表函数
CREATE OR REPLACE FUNCTION fgedu_split_string(
p_string TEXT,
p_delimiter TEXT DEFAULT ‘,’
) RETURNS TABLE (
position INTEGER,
value TEXT
) AS $$
DECLARE
arr TEXT[];
i INTEGER;
BEGIN
arr := string_to_array(p_string, p_delimiter);

FOR i IN 1..array_length(arr, 1) LOOP
position := i;
value := arr[i];
RETURN NEXT;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— 测试字符串拆分
SELECT * FROM fgedu_split_string(‘苹果,香蕉,橙子,葡萄’, ‘,’);

执行结果：

position | value
———-+——–
1 | 苹果
2 | 香蕉
3 | 橙子
4 | 葡萄
(4 rows)

4.3 创建复杂表函数

— 创建销售报表表函数
CREATE TABLE fgedu_fgfgfgfgsales_report (
sale_id SERIAL PRIMARY KEY,
product_name VARCHAR(100),
quantity INTEGER,
unit_price NUMERIC(10, 2),
sale_date DATE,
region VARCHAR(50)
);

INSERT INTO fgedu_fgfgfgfgsales_report (product_name, quantity, unit_price, sale_date, region) VALUES
(‘产品A’, 100, 50.00, ‘2026-04-01’, ‘华东’),
(‘产品B’, 150, 30.00, ‘2026-04-01’, ‘华东’),
(‘产品A’, 80, 50.00, ‘2026-04-02’, ‘华南’),
(‘产品B’, 200, 30.00, ‘2026-04-02’, ‘华南’),
(‘产品A’, 120, 50.00, ‘2026-04-03’, ‘华北’);

— 创建销售汇总表函数
CREATE OR REPLACE FUNCTION fgedu_get_fgfgfgfgsales_summary(
p_start_date DATE DEFAULT NULL,
p_end_date DATE DEFAULT NULL,
p_region VARCHAR DEFAULT NULL
) RETURNS TABLE (
product_name VARCHAR,
total_quantity BIGINT,
total_amount NUMERIC,
avg_price NUMERIC,
sale_count BIGINT
) AS $$
BEGIN
RETURN QUERY
SELECT
s.product_name,
SUM(s.quantity)::BIGINT AS total_quantity,
SUM(s.quantity * s.unit_price)::NUMERIC AS total_amount,
ROUND(AVG(s.unit_price)::NUMERIC, 2) AS avg_price,
COUNT(*)::BIGINT AS sale_count
FROM fgedu_fgfgfgfgsales_report s
WHERE (p_start_date IS NULL OR s.sale_date >= p_start_date)
AND (p_end_date IS NULL OR s.sale_date <= p_end_date) AND (p_region IS NULL OR s.region = p_region) GROUP BY s.product_name ORDER BY total_amount DESC; END; $$ LANGUAGE plpgsql; -- 测试销售汇总表函数 SELECT * FROM fgedu_get_fgfgfgfgsales_summary('2026-04-01', '2026-04-03', NULL);

执行结果：

product_name | total_quantity | total_amount | avg_price | sale_count
————–+—————-+————–+———–+————
产品A | 300 | 15000.00 | 50.00 | 3
产品B | 350 | 10500.00 | 30.00 | 2
(2 rows)

5. 标量函数与表函数对比

— 对比标量函数和表函数的使用场景

— 标量函数：在SELECT列表中使用
SELECT
product_name,
base_price,
fgedu_calc_tax_price(base_price) AS with_tax
FROM fgedu_products_scalar
LIMIT 3;

执行结果：

学习交流加群风哥QQ113257174

product_name | base_price | with_tax
————–+————+———-
笔记本电脑 | 5999.00 | 6778.87
无线鼠标 | 99.00 | 111.87
机械键盘 | 399.00 | 450.87
(3 rows)
— 表函数：在FROM子句中使用
SELECT
p.product_name,
p.base_price,
d.date_value,
d.day_of_week
FROM fgedu_products_scalar p
CROSS JOIN fgedu_generate_dates(‘2026-04-01’, ‘2026-04-03’) d
WHERE p.product_id = 1
ORDER BY d.date_value;

执行结果：

product_name | base_price | date_value | day_of_week
————–+————+————-+————-
笔记本电脑 | 5999.00 | 2026-04-01 | WED
笔记本电脑 | 5999.00 | 2026-04-02 | THU
笔记本电脑 | 2026-04-03 | FRI
(3 rows)

6. 实战案例：业务场景应用

— 创建综合业务函数

— 标量函数：计算折扣价格
CREATE OR REPLACE FUNCTION fgedu_calc_discount_price(
p_price NUMERIC,
p_member_level VARCHAR DEFAULT ‘普通’
) RETURNS NUMERIC AS $$
DECLARE
v_discount NUMERIC;
BEGIN
CASE p_member_level
WHEN ‘VIP’ THEN v_discount := 0.85;
WHEN ‘金卡’ THEN v_discount := 0.90;
WHEN ‘银卡’ THEN v_discount := 0.95;
ELSE v_discount := 1.00;
END CASE;

RETURN ROUND(p_price * v_discount, 2);
END;
$$ LANGUAGE plpgsql;

— 表函数：生成会员价格表
CREATE OR REPLACE FUNCTION fgedu_get_member_prices(
p_product_id INTEGER DEFAULT NULL
) RETURNS TABLE (
product_id INTEGER,
product_name VARCHAR,
base_price NUMERIC,
normal_price NUMERIC,
silver_price NUMERIC,
gold_price NUMERIC,
vip_price NUMERIC
) AS $$
BEGIN
RETURN QUERY
SELECT
ps.product_id,
ps.product_name,
ps.base_price,
fgedu_calc_discount_price(ps.base_price, ‘普通’) AS normal_price,
fgedu_calc_discount_price(ps.base_price, ‘银卡’) AS silver_price,
fgedu_calc_discount_price(ps.base_price, ‘金卡’) AS gold_price,
fgedu_calc_discount_price(ps.base_price, ‘VIP’) AS vip_price
FROM fgedu_products_scalar ps
WHERE p_product_id IS NULL OR ps.product_id = p_product_id
ORDER BY ps.product_id;
END;
$$ LANGUAGE plpgsql;

— 测试会员价格表函数
SELECT * FROM fgedu_get_member_prices();

执行结果：

product_id | product_name | base_price | normal_price | silver_price | gold_price | vip_price
————+————–+————+————–+————–+————+———–
1 | 笔记本电脑 | 5999.00 | 5999.00 | 5699.05 | 5399.10 | 5099.15
2 | 无线鼠标 | 99.00 | 99.00 | 94.05 | 89.10 | 84.15
3 | 机械键盘 | 399.00 | 399.00 | 379.05 | 359.10 | 339.15
4 | 办公椅 | 899.00 | 899.00 | 854.05 | 809.10 | 764.15
5 | 书架 | 299.00 | 299.00 | 284.05 | 269.10 | 254.15
(5 rows)

7. 清理环境

— 删除函数
DROP FUNCTION IF EXISTS fgedu_calc_tax_price(NUMERIC, NUMERIC);
DROP FUNCTION IF EXISTS fgedu_format_name(VARCHAR, VARCHAR, VARCHAR);
DROP FUNCTION IF EXISTS fgedu_generate_dates(DATE, DATE);
DROP FUNCTION IF EXISTS fgedu_split_string(TEXT, TEXT);
DROP FUNCTION IF EXISTS fgedu_get_fgfgfgfgsales_summary(DATE, DATE, VARCHAR);
DROP FUNCTION IF EXISTS fgedu_calc_discount_price(NUMERIC, VARCHAR);
DROP FUNCTION IF EXISTS fgedu_get_member_prices(INTEGER);

— 删除测试表
DROP TABLE IF EXISTS fgedu_products_scalar;
DROP TABLE IF EXISTS fgedu_fgfgfgfgsales_report;

执行结果：

风哥提示：

DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP TABLE
DROP TABLE
风哥教程风哥教程风哥教程总结：标量函数返回单个值，适合在SELECT列表、WHERE条件等位置使用；表函数返回结果集，适合在FROM子句中使用，可以像表一样进行查询和连接。选择使用哪种函数取决于具体需求：需要单个计算结果用标量函数，需要返回多行数据用表函数。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息