PostgreSQL 存储函数开发

介绍

在本实验中，你将学习如何开发 PostgreSQL 存储函数。你将经历定义一个基础存储函数、添加带有默认值的输入参数、在查询中执行函数，以及最后删除一个未使用的函数以保持数据库的整洁。

定义基本存储函数

在本步骤中，你将学习如何在 PostgreSQL 中定义一个基础存储函数。存储函数是可重用的代码块，用于执行特定任务，并可以在数据库内执行。

首先，打开一个终端，并使用 psql 命令行工具连接到 PostgreSQL 数据库。你将在 psql shell 中执行所有数据库操作。

sudo -u postgres psql

现在你应该看到 PostgreSQL 的提示符，它看起来像 postgres=#。

接下来，创建一个名为 get_total_products 的函数，该函数返回一个固定的整数值。这个简单的函数将帮助你理解基础语法。在 psql shell 中执行以下 SQL 命令：

CREATE FUNCTION get_total_products()
RETURNS INTEGER AS $$
BEGIN
  RETURN 100;
END;
$$ LANGUAGE plpgsql;

让我们分解一下这个命令：

• CREATE FUNCTION get_total_products(): 这定义了一个名为 get_total_products 的新函数，它没有输入参数。
• RETURNS INTEGER: 这指定了函数将返回一个 INTEGER 数据类型的单个值。
• AS $$ ... $$: 美元符号引用的字符串 $$ 用于包含函数体。这是 PostgreSQL 中一种常见的做法，用于避免函数代码中的单引号引起的问题。
• BEGIN ... END;: 这个块包含函数的可执行部分。
• RETURN 100;: 这是我们函数的逻辑，它简单地返回整数 100。
• LANGUAGE plpgsql: 这指定了函数是用 plpgsql 编写的，这是 PostgreSQL 的过程语言。

执行命令后，PostgreSQL 将确认函数的创建：

CREATE FUNCTION

为了验证函数是否已创建，你可以在 SELECT 语句中调用它：

SELECT get_total_products();

输出将显示函数返回的值：

 get_total_products
--------------------
                100
(1 row)

这证实了你的第一个存储函数正在正常工作。

创建带参数的函数

当存储函数能够接受输入参数时，它们会变得更加强大。在本步骤中，你将创建一个接受两个数字并返回它们之和的新函数。

确保你仍然在上一步骤的 psql shell 中。现在，创建一个名为 add_numbers 的函数，该函数接受两个整数参数。

CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

语法与上一步类似，但函数签名有一个关键区别：

• add_numbers(a INTEGER, b INTEGER): 这定义了两个参数 a 和 b，它们的类型都是 INTEGER，在调用函数时必须提供。

执行命令后，你将看到 CREATE FUNCTION 的确认信息。

现在，通过提供两个数字作为参数来测试该函数：

SELECT add_numbers(15, 25);

函数将把这两个数字相加并返回结果：

 add_numbers
-------------
          40
(1 row)

你也可以使用带有默认参数值的函数。让我们创建一个名为 greet_user 的新函数，其中问候语有一个默认值。

CREATE OR REPLACE FUNCTION greet_user(username VARCHAR, greeting VARCHAR DEFAULT 'Hello')
RETURNS TEXT AS $$
BEGIN
  RETURN greeting || ', ' || username || '!';
END;
$$ LANGUAGE plpgsql;

这里，CREATE OR REPLACE 将在函数已存在时更新它。greeting 参数被赋予了默认值 'Hello'。|| 操作符用于字符串连接。

通过仅提供必需的 username 参数来测试该函数：

SELECT greet_user('Alex');

函数使用了默认的问候语：

       greet_user
------------------------
 Hello, Alex!
(1 row)

现在，再次调用它，但这次提供一个自定义的问候语：

SELECT greet_user('Alex', 'Welcome');

输出现在显示你的自定义消息：

      greet_user
-----------------------
 Welcome, Alex!
(1 row)

在查询中执行函数

存储函数的一个常见用途是对表中的数据执行计算。在本步骤中，你将创建一个表，用数据填充它，然后在该表的查询中使用一个函数。

首先，创建一个名为 products 的简单表来存储产品名称和价格。

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  price NUMERIC(10, 2)
);

你将看到 CREATE TABLE 的确认消息。

接下来，向 products 表中插入一些示例数据：

INSERT INTO products (name, price) VALUES
('Laptop', 1200.00),
('Mouse', 25.50),
('Keyboard', 75.00);

你将看到 INSERT 0 3，表示已插入三行。

现在，让我们创建一个计算含税价格的函数。此函数将价格作为输入，并返回包含 7% 税的价格。

CREATE FUNCTION calculate_taxed_price(price NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
  RETURN price * 1.07;
END;
$$ LANGUAGE plpgsql;

创建函数后，你可以直接在 products 表的 SELECT 查询中使用它。此查询将显示每个产品的原始价格和含税价格。

SELECT name, price, calculate_taxed_price(price) AS taxed_price FROM products;

输出将显示函数对每一行的调用结果：

   name   |  price  | taxed_price
----------+---------+-------------
 Laptop   | 1200.00 |    1284.0000
 Mouse    |   25.50 |      27.2850
 Keyboard |   75.00 |      80.2500
(3 rows)

这演示了如何将业务逻辑封装在函数中并将其应用于你的数据。

删除已存储函数

移除不再需要的数据库对象是一种良好的实践。在本步骤中，你将学习如何从数据库中删除或“丢弃”一个存储函数。我们将删除在第一步中创建的 get_total_products 函数。

首先，你可以列出数据库中的函数以确认 get_total_products 存在。

\df

你将看到一个函数列表，其中包括 get_total_products。

要删除函数，请使用 DROP FUNCTION 命令。你必须指定函数名称。如果函数有参数，你需要指定它们的类型，但由于 get_total_products 没有参数，你只需使用名称即可。

DROP FUNCTION get_total_products();

PostgreSQL 将确认该操作：

DROP FUNCTION

现在，如果你再次使用 \df 列出函数，你将看到 get_total_products 已不再列表中。

如果函数被重载（即，具有相同名称但不同参数的多个函数），指定参数类型也很重要。例如，要删除 add_numbers 函数，你必须指定其整数参数：

DROP FUNCTION add_numbers(INTEGER, INTEGER);

最后，为了清理你的环境，删除上一步创建的 products 表。

DROP TABLE products;

本实验到此结束。要退出 psql shell，请输入 \q 并按 Enter。

总结

在本实验中，你学习了 PostgreSQL 中开发存储函数的基础知识。你创建了一个基本函数，通过参数和默认值对其进行了增强，在查询表数据时应用了函数，最后通过删除函数和表来清理数据库。这些技能对于创建更模块化和高效的数据库解决方案至关重要。