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