PL/pgSQL 基本使用语法

最新推荐文章于 2025-10-17 18:10:11 发布

原创最新推荐文章于 2025-10-17 18:10:11 发布 · 937 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

PostgreSQL 专栏收录该内容

21 篇文章

订阅专栏

本文介绍了PostgreSQL数据库系统中的PL/pgSQL编程语言，用于创建函数、过程和触发器。它增加了SQL的控制结构，支持复杂计算，并能使用所有用户定义的类型、函数和操作。在PostgreSQL 9.0及更高版本中，默认安装了PL/pgSQL。通过示例展示了如何创建不同类型的函数，包括带有输入/输出参数、无返回值以及返回多行结果的函数。

参考官网

介绍：

PL/pgSQL is a loadable procedural language for the PostgreSQL database system. The design goals of PL/pgSQL were to create a loadable procedural language that
can be used to create functions, procedures, and triggers,
adds control structures to the SQL language,
can perform complex computations,
inherits all user-defined types, functions, procedures, and operators,
can be defined to be trusted by the server,
is easy to use.

Functions created with PL/pgSQL can be used anywhere that built-in functions could be used. For example, it is possible to create complex conditional computation functions and later use them to define operators or use them in index expressions.
In PostgreSQL 9.0 and later, PL/pgSQL is installed by default. However it is still a loadable module, so especially security-conscious administrators could choose to remove it.

语法模板：

CREATE OR REPLACE FUNCTION function_name([ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ])
[RETURNS rettype]
AS $$
    $BODY$;
$$ LANGUAGE SQL;

示例1

CREATE OR REPLACE FUNCTION add(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
AS $$
    SELECT a+b;
$$ LANGUAGE SQL;

# select add(2,3);
 add 
-----
   5
(1 row)

示例2

# 由于存在输出参数；这里不需要returns部分
CREATE OR REPLACE FUNCTION add1(in a NUMERIC, in b NUMERIC,out c numeric)
AS $$
    SELECT a+b;
$$ LANGUAGE SQL;

# select add1(7,4);
 add1 
------
   11

示例3

在函数定义中，可以写多个SQL语句，不一定是SELECT语句，可以是其它任意合法的SQL。
但最后一条SQL必须是SELECT语句并且该SQL的结果将作为该函数的输出结果
CREATE OR REPLACE FUNCTION plus_and_minus(IN a INTEGER, IN b NUMERIC, OUT c NUMERIC, OUT d NUMERIC)
AS $$
    SELECT a+b, a-b;
    delete from t where id > 9999;
    SELECT a-b, a+b;
$$ LANGUAGE SQL; 


select * from  plus_and_minus(7,5);
 c | d  
---+----
 2 | 12

示例4

不需要返回结果
CREATE OR REPLACE FUNCTION delete_t()
RETURNS void
AS $$
    delete from t where id > 999;
$$ LANGUAGE SQL;


# select delete_t();
 delete_t 
----------

示例5

返回最后一条查询结果的第一行
CREATE OR REPLACE FUNCTION select_emp_1()
RETURNS numeric
AS $$
    select empno from emp;
$$ LANGUAGE SQL;


select select_emp_1();
 select_emp_1 
--------------
         7369

示例6

若需要该函数返回最后一条SELECT语句的所有行，可以将函数的返回值定义为集合，即SETOF sometype。
CREATE OR REPLACE FUNCTION select_emp_2()
RETURNS setof numeric
AS $$
    select empno from emp;
$$ LANGUAGE SQL;


select select_emp_2();
 select_emp_2 
--------------
         7369
         7499
         7521
         7566
         7654
         7698
         7782