PostgreSQL——存储过程、函数

概述

介绍

PostgreSQL（简称PG）是一个功能强大的开源对象-关系数据库系统，支持几乎所有的SQL标准并且提供了许多其他现代特性。在PostgreSQL中，存储过程和函数是数据库编程的重要组成部分，它们允许用户封装复杂的数据库操作逻辑，以提高代码的重用性、减少网络传输的数据量，并增强数据库操作的安全性。

存储过程（Procedure）：

• 主要用于执行一系列的操作，通常没有明确的返回值（尽管可以通过OUT参数返回数据）。
• 在PostgreSQL中，存储过程使用CREATE PROCEDURE语句创建。

函数（Function）：

• 主要用于对输入参数进行处理，并返回一个结果值。
• 在PostgreSQL中，函数可以使用CREATE FUNCTION语句创建，并且可以在SQL查询中作为表达式的一部分被调用。

使用

存储过程的使用：

1. 创建存储过程：使用CREATE PROCEDURE语句定义存储过程的名称、参数（包括IN、OUT和INOUT类型）以及过程体（包含一系列的SQL语句或PL/pgSQL代码块）。
2. 调用存储过程：使用CALL语句或SELECT * FROM语法调用存储过程。

函数的使用：

1. 创建函数：使用CREATE FUNCTION语句定义函数的名称、参数（只有IN类型）、返回类型以及函数体。
2. 调用函数：函数可以在SQL查询中直接作为表达式调用，例如，在SELECT语句的列表达式中。

优缺点

优点：

1. 性能提升：存储过程和函数在第一次被调用后会被编译，后续调用时不需要再次编译，减少了应用与数据库服务器的通信开销，提升了数据库的整体性能。
2. 业务逻辑共享：实现了一套业务逻辑被不同应用程序共享，减少了应用程序的开发复杂度，保证了不同应用程序使用的一致性。
3. 安全性增强：通过存储过程和函数，可以屏蔽数据库中的敏感数据，区分应用程序访问数据的权限，从而更细粒度地加强数据库权限的管理。
4. 业务与应用程序解耦：当业务需求更新时，只需更改存储过程或函数的定义，而不需要更改应用程序。
5. 丰富的编程能力：PostgreSQL支持多种编程语言（如PL/pgSQL、PL/Perl、PL/Python）来编写存储过程和函数，提供了强大的SQL编程能力。

缺点：

1. 移植性较差：不同数据库的存储过程和函数在定义方式、语言及语法上存在差异，因此将PostgreSQL的存储过程和函数移植到其他数据库系统时可能会面临较高的成本。
2. 学习曲线较陡：虽然存储过程和函数提供了强大的编程能力，但也需要开发者具备一定的编程基础和数据库知识，学习曲线相对较陡。

综上所述，PostgreSQL的存储过程和函数是数据库编程中不可或缺的工具，它们能够显著提高数据库的性能、安全性和可维护性。然而，在使用时也需要注意其移植性和学习成本等问题。

基本语法

函数

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ [ in ] [ out ] [ INOUT ]][ { DEFAULT | = } default_expr ]  ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | WINDOW
    | { IMMUTABLE | STABLE | VOLATILE }
    | [ NOT ] LEAKPROOF
    | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
    | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SUPPORT support_function
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
    | sql_body
  } 

存储过程

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ [ in ] [ out ] [ INOUT ]][ { DEFAULT | = } default_expr ]  ] )
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | WINDOW
    | { IMMUTABLE | STABLE | VOLATILE }
    | [ NOT ] LEAKPROOF
    | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
    | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SUPPORT support_function
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
    | sql_body
  } 

测试用例

CREATE OR REPLACE FUNCTION process_data() RETURNS void AS $$  
DECLARE  
    r RECORD;  
    cursor_name CURSOR FOR SELECT id, value FROM source_table;  
    -- 注意：在真实应用中，通常不需要为这么简单的查询使用动态SQL  
    -- 但为了演示，我们构造一个动态SQL语句  
    dynamic_sql TEXT := 'SELECT id, value FROM source_table';  
BEGIN  
    -- 打开游标  
    OPEN cursor_name;  
  
    -- 循环遍历游标  
    LOOP  
        FETCH cursor_name INTO r;  
        EXIT WHEN NOT FOUND; -- 如果没有更多行，则退出循环  
  
        -- 这里我们简单地将value加倍作为处理后的值  
        -- 实际上，这里可以包含更复杂的逻辑或调用其他函数  
        INSERT INTO target_table (processed_value) VALUES (r.value * 2);  
  
        -- 注意：在这个简单例子中，我们没有将id插入到target_table中，  
        -- 但你可以根据需要调整INSERT语句来包含它。  
  
        -- 如果需要使用动态SQL进行插入（尽管这通常不是必需的），可以这样做：  
        -- EXECUTE format('INSERT INTO target_table (processed_value) VALUES (%L)', r.value * 2);  
    END LOOP;  
  
    -- 关闭游标  
    CLOSE cursor_name;  
END;  
$$ LANGUAGE plpgsql;

存储过程和函数的区别

在PostgreSQL数据库中，存储过程和函数是两种相似的可重用数据库对象，它们都是由SQL语句或PL/pgSQL（PostgreSQL的过程语言）编写的一组操作。然而，它们在用途、返回值以及调用方式上存在一些关键区别。以下是这些区别的详细归纳：

1. 用途和返回值

• 函数（Functions）：
  • 通常用于执行一些操作并返回一个值。这个值可以是任何数据类型，包括数字、字符串、日期等。
  • 函数的主要目的是进行计算或数据处理，并将结果返回给调用者。
• 存储过程（Procedures）：
  • 主要用于执行一系列操作，这些操作可能包括多个SQL语句或其他代码块，但它们通常不返回任何值（或者说，它们的返回类型为void）。
  • 存储过程的设计初衷是执行一系列复杂的数据库操作，而不一定需要返回结果给调用者。

2. 调用方式

• 函数：
  • 可以在SQL查询中直接调用，特别是在SELECT语句中，函数可以作为列表达式的一部分出现。
  • 函数的调用结果可以直接在SQL查询中使用，如作为条件、与其他列进行比较或作为其他函数的参数。
• 存储过程：
  • 通常需要通过CALL语句或类似的语法来调用。
  • 存储过程不能在SELECT语句中直接作为列表达式调用，它们更适合于执行一系列独立的数据库操作。