在PostgreSQL中，存储过程（Procedure）和函数（Function）是两种可执行的数据库对象，它们之间有一些区别。以下是它们的主要区别：

1. 返回值：函数具有返回值，而存储过程可以没有返回值或返回多个结果集。
2. 调用方式：函数可以像普通的SQL表达式一样调用，可以在查询中直接使用函数的返回值。而存储过程需要使用CALL语句来调用，并且无法将其嵌入到查询中。
3. 事务控制：函数在调用时会自动启动一个事务，并且可以使用COMMIT或ROLLBACK语句来控制事务的提交或回滚。存储过程可以在其中包含多个SQL语句，但不会自动启动事务，需要手动控制事务的开始和结束。
4. 参数传递：函数和存储过程都可以接收参数，但在函数中，参数可以通过IN、OUT和INOUT模式来指定，而存储过程中的参数只能通过IN模式传递。
5. 使用场景：函数通常用于执行一系列计算或操作，并返回结果。存储过程通常用于执行一系列的数据库操作，例如数据加载、数据清理、数据迁移等。存储过程通常用于封装复杂的业务逻辑，并且可以被其他应用程序或过程调用。

虽然存储过程和函数在某些方面有区别，但它们也有一些共同点，例如都可以使用PL/pgSQL等编程语言编写，都可以访问数据库对象和执行SQL语句。选择使用存储过程还是函数取决于具体的需求和使用场景。

一、PROCEDURE

https://www.postgresql.org/docs/current/sql-createprocedure.html

1.1 语法

CREATE [ OR REPLACE ] PROCEDURE
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
    | sql_body
  } ...

1.2 描述

可以指定在 某 schema 下，创建 procedure，否则在当前 schema 下创建。

procedure 和 function，如果有不同的参数，是可以重名的（即 overloading）。

用 CREATE OR REPLACE PROCEDURE 可以更改 body。但正因为 overloading，所以不能通过 CREATE OR REPLACE PROCEDURE 来更改 name 或 arguments。（如果尝试的话，会 create 一个新的，不同的 procedure）。

必须有 USAGE 权限的用户才能创建 PROCEDURE。

使用 CREATE OR REPLACE PROCEDURE 时，ownership 和 permission 不会变化。

1.3 参数

name：名称

argmode：IN、OUT、INOUT、VARIADIC，默认是 IN
argname：参数名称
argtype：是存储过程参数的数据类型。可以是基本类型、复合类型、域类型，或者引用表列的类型。参数类型可以选择模式限定，如果需要的话。根据实现的编程语言，也可以指定“伪类型”，例如cstring。伪类型表示实际参数类型不完全指定，或者不属于普通SQL数据类型集合之内。
通过写入table_name.column_name%TYPE，可以引用列的类型。使用这个特性有时可以帮助使存储过程独立于对表定义的更改。

default_expr: 如果未指定参数，则用作默认值的表达式。该表达式必须对参数的实参类型是强制的。带有缺省值的参数后面的所有输入参数也必须具有缺省值。

lang_name: 实现过程所使用的语言的名称。它可以是SQL、C、INTERNAL或用户定义的过程语言的名称，例如plpgsql。如果指定了SQL_BODY，则默认为SQL。不建议使用单引号将名称括起来，并且需要大小写匹配。

TRANSFORM { FOR TYPE type_name } [, … ] }
列出应应用哪些转换对过程的调用。转换在SQL类型和语言特定的数据类型之间进行转换；请参阅创建转换。过程语言实现通常具有内置类型的硬编码知识，因此不需要在这里列出这些知识。如果过程语言实现不知道如何处理类型，并且没有提供转换，则它将退回到转换数据类型的默认行为，但这取决于实现。

1.4 示例

CREATE PROCEDURE insert_data(a integer, b integer)
LANGUAGE SQL
AS $$
INSERT INTO tbl VALUES (a);
INSERT INTO tbl VALUES (b);
$$;

CREATE PROCEDURE insert_data(a integer, b integer)
LANGUAGE SQL
BEGIN ATOMIC
  INSERT INTO tbl VALUES (a);
  INSERT INTO tbl VALUES (b);
END;

调用方式为

CALL insert_data(1, 2);

二、FUNCTION

http://postgres.cn/docs/14/sql-createfunction.html

2.1 语法

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { 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 FUNCTION定义一个新函数。CREATE OR REPLACE FUNCTION将创建一个新函数或者替换一个现有的函数。要定义一个函数，用户必须具有该语言上的USAGE特权。

如果包括了一个模式名，那么该函数会被创建在指定的模式中。否则，它会被创建在当前模式中。新函数的名称不能匹配同一个模式中具有相同输入参数类型的任何现有函数或过程。不过，不同参数类型的函数和过程能够共享一个名字（这被称作重载）。

要替换一个现有函数的当前定义，可以使用CREATE OR REPLACE FUNCTION。但不能用这种方式更改函数的名称或者参数类型（如果尝试这样做，实际上就会创建一个新的不同的函数）。还有，CREATE OR REPLACE FUNCTION将不会让你更改一个现有函数的返回类型。要这样做，你必须先删除再重建该函数（在使用OUT参数时，这意味着除了删除函数之外无法更改任何OUT参数的类型）。

当CREATE OR REPLACE FUNCTION被用来替换一个现有的函数，该函数的拥有权和权限不会改变。所有其他的函数属性会按照该命令中所指定的或者隐含的来赋值。必须拥有（包括成为拥有角色的成员）该函数才能替换它。

如果你删除并且重建一个函数，新函数将和旧的不一样，你将必须删掉引用旧函数的现有规则、视图、触发器等。使用CREATE OR REPLACE FUNCTION更改一个函数定义不会破坏引用该函数的对象。还有，ALTER FUNCTION可以被用来更改一个现有函数的大部分辅助属性。

创建该函数的用户将成为该函数的拥有者。

要创建一个函数，你必须拥有参数类型和返回类型上的USAGE特权。

2.2 重载

PostgreSQL允许函数重载，也就是说同一个名称可以被用于多个不同的函数，只要它们具有可区分的输入参数类型。不管是否使用它，在有些用户不信任另一些用户的数据库中调用函数时，这种兼容性需要安全性的预防措施，请参考第 10.3 节。

如果两个函数具有相同的名称和***输入***参数类型，它们被认为相同（不考虑任何OUT参数）。因此这些声明会冲突：

CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, out text) ...

具有不同参数类型列表的函数在创建时将不会被认为是冲突的，但是如果默认值被提供，在使用时它们有可能会冲突。例如，考虑

CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, int default 42) ...

调用foo(10)将会失败，因为在要决定应该调用哪个函数时会有歧义。

2.3 示例

使用SQL函数对两个整数相加：

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

同一个函数以更符合SQL习惯的样式编写，使用参数名称和未加引号的函数体，如下：

CREATE FUNCTION add(a integer, b integer) RETURNS integer
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
    RETURN a + b;

在PL/pgSQL中，使用一个参数名称增加一个整数：

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
	BEGIN
	    RETURN i + 1;
	END;
$$ LANGUAGE plpgsql;

返回一个包含多个输出参数的记录：

CREATE FUNCTION dup(in int, out f1 int, out f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

-- code result:
postgres=# SELECT * FROM dup(42);
(42,"42 is text")

你可以用更复杂的方式（用一个显式命名的组合类型）来做同样的事情：

CREATE TYPE dup_result AS (f1 int, f2 text);

CREATE FUNCTION dup(int) RETURNS dup_result
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

-- code result:
postgres=# SELECT * FROM dup(42);
 f1 |     f2
----+------------
 42 | 42 is text

另一种返回多列的方法是使用一个TABLE函数：

CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

-- code result:
postgres=# SELECT * FROM dup(42);
 f1 |     f2
----+------------
 42 | 42 is text

不过，TABLE 函数与之前的例子不同，因为它实际返回了一个记录集合而不只是一个记录。

2.4 兼容性

SQL标准中定义了CREATE FUNCTION命令。 PostgreSQL实现可以以兼容的方式使用，但有许多扩展。 相反，SQL标准指定了许多未在PostgreSQL中实现的可选功能。

以下是重要的兼容性问题：

• OR REPLACE是PostgreSQL的扩展。
• 为了与其他一些数据库系统兼容，argmode 可以在*argname*之前或之后写入。 但只有第一种方式符合标准。
• 对于参数默认值，SQL标准仅指定带有DEFAULT关键字的语法。 =的语法是在T-SQL和Firebird中被使用的。
• SETOF修饰符是PostgreSQL的扩展。
• 只有SQL是被标准化的一个语言。
• 除了 CALLED ON NULL INPUT和 RETURNS NULL ON NULL INPUT以外的所有其他属性都没有标准化。
• 对于LANGUAGE SQL函数的主体，SQL标准只指定了 *SQL_body*形式。

简单的LANGUAGE SQL函数可以以既符合标准又可移植到其他实现的方式编写。 使用高级特性、优化属性或其他语言的更复杂的函数必然在很大程度上特定于PostgreSQL。

2.5 示例

2.5.1 declare variable 定义变量

DECLARE variable_name [ CONSTANT ] data_type [ { DEFAULT | := } initial_value ];

其中：
DECLARE：用于声明一个变量。
variable_name：变量的名称。
[ CONSTANT ]：可选项，用于指定变量是否为常量。
data_type：变量的数据类型，可以是基本数据类型、复合类型或自定义类型。
[ DEFAULT | := ]：可选项，用于指定变量的初始值。DEFAULT关键字和:=赋值符号都可以用于指定初始值。
initial_value：变量的初始值。

以下是一个示例，演示在PostgreSQL函数中声明和使用变量的过程：

CREATE OR REPLACE FUNCTION calculate_sum(a INT, b INT)
RETURNS INT AS $$

DECLARE
	result INT;
BEGIN
	result := a + b;
	RETURN result;
END;

$$ LANGUAGE plpgsql;

在上述示例中，函数calculate_sum声明了一个名为result的整数变量，并将a和b的和赋值给该变量。最后，函数返回了变量result的值。

2.5.2 declare、ARRAY、ANY

-- 建表
CREATE TABLE a(id TEXT, ts BIGINT);
INSERT INTO a VALUES ('a', 1), ('b', 2), ('c', 3), ('d', 4), ('e', 5);
postgres=# SELECT * FROM a;
 id | ts
----+----
 a  |  1
 b  |  2
 c  |  3
 d  |  4
 e  |  5
(5 rows)

-- 建FUNCTION
CREATE OR REPLACE FUNCTION f(id_arr TEXT[], src_start_ts BIGINT, src_end_ts BIGINT, tgt_start_ts BIGINT)
RETURNS VOID AS $$
DECLARE
    offset_ms BIGINT;
BEGIN
    offset_ms := tgt_start_ts - src_start_ts; -- 偏移多少毫秒
    DROP TABLE IF EXISTS t; -- 临时表
    CREATE TABLE t AS SELECT * FROM a WHERE id = ANY(id_arr) AND ts >= src_start_ts AND ts <= src_end_ts; -- 查原始表a的数据
    UPDATE t SET ts = ts + offset_ms; -- 执行偏移（在临时表）
    INSERT INTO a SELECT * FROM t; -- 将偏移写入原始表
END;
$$ LANGUAGE plpgsql;

-- 执行
SELECT f(ARRAY['a', 'b', 'c', 'd', 'e'], 2, 4, 10); -- 操作从ts=2到ts=4的三行数据，使它们的ts向后偏移10-2=8个单位，从而得到ts=10到ts=12的三行数据并写入原始表
postgres=# SELECT * FROM a;
 id | ts
----+----
 a  |  1
 b  |  2
 c  |  3
 d  |  4
 e  |  5
 b  | 10
 c  | 11
 d  | 12
(8 rows)

2.5.2.1 ARRAY 和 ANY

在PostgreSQL中，可以使用关键字IN来判断一个值是否存在于一个数组中。以下是使用IN操作符来检查一个值是否存在于数组中的示例：

SELECT * FROM my_table WHERE my_column IN ('value1', 'value2', 'value3');

在上述示例中，my_table是表名，my_column是包含数组的列名。'value1', 'value2', 'value3'是要检查的值的列表。如果my_column列中的值与列表中的任何一个值匹配，那么该行将被返回。

如果你想要检查一个值是否存在于一个数组类型的参数中，可以使用ANY关键字。以下是检查一个值是否存在于数组参数中的示例：

CREATE OR REPLACE FUNCTION my_function (my_array text[], value text)
RETURNS boolean AS $$
BEGIN
	RETURN value = ANY(my_array);
END;
$$ LANGUAGE plpgsql;

在上述示例中，my_array是一个文本数组类型的参数，value是要检查的值。函数使用ANY关键字来判断value是否存在于my_array中，并返回布尔值。

这是使用IN操作符和ANY关键字在PostgreSQL中检查一个值是否存在于一个数组中的常见方法。根据实际需求，你可以根据这些示例进行调整和修改。

三、其他

3.1 PL/pgSQL

PL/pgSQL是PostgreSQL数据库中的一种过程化语言（Procedural Language），用于编写存储过程、触发器、函数和其他数据库相关的程序。PL/pgSQL是PostgreSQL的默认过程化语言，提供了丰富的功能和语法，可以用于编写复杂的业务逻辑和数据处理逻辑。

PL/pgSQL基于SQL语言，结合了流程控制结构（如条件语句、循环语句）和SQL语句，使开发人员能够在数据库中编写更灵活和强大的程序。它支持变量声明、条件判断、循环、异常处理等常见的编程特性，并且可以直接访问数据库对象和执行SQL查询。

使用PL/pgSQL，开发人员可以在数据库中创建存储过程，以便执行一系列的数据库操作，如数据加载、数据清理、数据转换等。它还可以用于编写触发器，以在数据库表上定义特定的触发行为。此外，PL/pgSQL还可以编写函数，用于执行计算、数据处理和数据转换等任务。

总之，PL/pgSQL是PostgreSQL数据库中的一种过程化语言，用于编写复杂的数据库程序和逻辑。它是PostgreSQL强大而灵活的编程工具之一。