postgresql扩展SQL与存储过程

Pg系统本身提供不同扩展,可以通过select * from pg_language 来查看支持

1
2
3
4
5
6
lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl
----------+----------+---------+--------------+---------------+-----------+--------------+--------
internal | 10 | f | f | 0 | 0 | 2246 |
c | 10 | f | f | 0 | 0 | 2247 |
sql | 10 | f | t | 0 | 0 | 2248 |
plpgsql | 10 | t | t | 13335 | 13336 | 13337 |



internal

内部函数都是用C写的函数,它们已经通过静态链接的方式嵌入 PostgreSQL服务器进程中了。 函数定义的”函数体”确定了函数的C语言名称, 它不必与给 SQL 使用的名称相同。出于向下兼容考虑, 一个空的函数体也可以被接受, 这意味着 C 函数名与 SQL 函数名相同。

通常,所有在服务器里出现的内部函数都在数据库初始化时定义。 但是用户可以用CREATE FUNCTION为内部函数创建额外的别名。 内部函数在CREATE FUNCTION命令里是带着internal语言名声明的。

1
2
3
4
CREATE FUNCTION square_root(double precision) RETURNS double precision
AS 'dsqrt'
LANGUAGE internal
STRICT;
  • pg_proc.h
  • 需要在pg源码中添加c执行函数(静态编译到Postgres)

    示例

  • Pg源码添加系统函数 创建函数相同, 直接在psql中create function 即可。

c

用户定义的函数可以用 C 写(或者是与C兼容的语言,比如C++)。 这样的函数被编译进动态加载对象(共享库)并且由服务器根据需要加载。 动态加载的特性是”C 语言函数”和”内部函数”之间的区别—不过, 实际的编码习惯在两者之间实际上是一样的。

  • 需要在pg中添加C代码(插件形式)
  • 在插件sql代码中 或者 单独执行 create function 用来动态加载 sa '$libdir/xxx' 查找相应动态库

示例

sql

方法是把该函数的返回类型声明为SETOF *sometype*。 或者等价声明它为RETURNS TABLE(*columns*)。 这种情况下,最后一条查询结果的所有行都会被返回。

1
2
3
4
5
CREATE FUNCTION clean_emp() RETURNS void
AS $$
DELETE FROM emp
WHERE salary < 0;
$$ LANGUAGE SQL;

plpgsql

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE FUNCTION clean_emp() RETURNS void 
AS $$
[ <<label>> ]
[ DECLARE
declaration ]
BEGIN
statements
END [label];
$$ LANGUAGE PLPGSQL;
  • 中括号部分为可选部分
  • 块中的每一个declaration和每一条statement都由一个分号终止
  • 块支持嵌套,嵌套时子块的END后面必须跟一个分号,最外层的块END后可不跟分号
  • BEGIN后面不必也不能跟分号
  • END后跟的label名必须和块开始时的标签名一致
  • 所有关键字都不区分大小写。标识符被隐含地转换成小写字符,除非被双引号包围
  • 声明的变量在当前块及其子块中有效,子块开始前可声明并覆盖(只在子块内覆盖)外部块的同名变量
  • 变量被子块中声明的变量覆盖时,子块可以通过外部块的label访问外部块的变量

存储过程

存储过程(Stored Procedure)是大型数据库系统中,一组为了完成特定功能的SQL语句集,存储在数据库中,首次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果有)来执行它。它是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。

Postgresql对存储过程的描述是:存储过程和用户定义的函数(UDF)是SQL和过程语句的集合,它存储于数据库服务器并能呗SQL接口调用。

存储过程特性:

  • 存储于数据库服务器
  • 一次编译后可多次调用
  • 由SQL和过程语句来定义
  • 应用程序通过SQL接口来调用
sql /PLSql的区别:
  • SQL 是结构化查询语言,比较接近自然语言,使用SQL,只需要说干什么,不需要说怎么干。有数据定义语言,数据操纵语言,数据控制语言构成。 它不面向对象,即前一句与后一句无关。SQL是标准的语句。
  • PL/SQL ,Oracle对SQL标准的扩充,增加了面向过程功能,所以可以用来编写存储过程,存储函数,触发器等等。PL/SQL是结构化的SQL,就是在标准SQL中加入了 IF..ELSE. FOR... 等控制过程的SQL
    • PL/SQL是块结构语言,意味着程序可以分成若干逻辑块,各自包含那个单元里要求的逻辑语言资源。可以对块宣布本地变量,在块中使用这些变量,可在它们应用的块中特别地处理错误条件(叫做Exceptions)
基于SQL的存储过程定义
1
2
3
4
5
CREATE OR REPLACE FUNCTION add(a INTEGER, b NUMERIC)
RETURNS NUMERIC
AS $$
SELECT a+b;
$$ LANGUAGE SQL;

包含输入输出

1
2
3
4
5
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;
$$ LANGUAGE SQL;

在函数定义中,可以编写任意合法SQL语句,不一定是select ,但最后一条SQL必须是select语句,并且该sql的结果将作为该函数的输出结果

1
2
3
4
5
6
7
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;
INSERT INTO test VALUES('test1');
SELECT a-b, a+b;
$$ LANGUAGE SQL;

结果如下:

1
2
3
4
5
SELECT * FROM plus_and_minus(5,3);
c | d
---+---
2 | 8
(1 row)
基于pl/pgsql存储过程定义

PL/pgsql是一个块结构语言,函数定义的所有文本都必须是一个块。

1
2
3
4
5
6
[ <<label>> ]
[ DECLARE
declaration ]
BEGIN
statements
END [label];
  • 中括号部分为可选部分
  • 块中的每一个declaration和每一条statement都由一个分号终止
  • 块支持嵌套,嵌套时子块的END后面必须跟一个分号,最外层的块END后可不跟分号
  • BEGIN后面不必也不能跟分号
  • END后跟的label名必须和块开始时的标签名一致
  • 所有关键字都不区分大小写。标识符被隐含地转换成小写字符,除非被双引号包围
  • 声明的变量在当前块及其子块中有效,子块开始前可声明并覆盖(只在子块内覆盖)外部块的同名变量
  • 变量被子块中声明的变量覆盖时,子块可以通过外部块的label访问外部块的变量
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE FUNCTION somefunc() RETURNS integer AS $$
DECLARE
quantity integer := 30; -- 赋值;
BEGIN
-- Prints 30
RAISE NOTICE 'Quantity here is %', quantity;
quantity := 50;
-- Create a subblock
DECLARE
quantity integer := 80;
BEGIN
-- Prints 80
RAISE NOTICE 'Quantity here is %', quantity;
-- Prints 50
RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;
END;
-- Prints 50
RAISE NOTICE 'Quantity here is %', quantity;
RETURN quantity;
END;
$$ LANGUAGE plpgsql;
返回多列

PostgreSQL除了支持自带类型外,还支持用户创建自定义类型。在这里可以自定义一个复合类型,并在函数中返回一个该复合类型的值,从而实现返回一行多列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TYPE compfoo AS (col1 INTEGER, col2 TEXT);
CREATE OR REPLACE FUNCTION getCompFoo
(in_col1 INTEGER, in_col2 TEXT)
RETURNS compfoo
AS $$
DECLARE result compfoo;
BEGIN
result.col1 := in_col1 * 2;
result.col2 := in_col2 || '_result';
RETURN result;
END;
$$ LANGUAGE PLPGSQL;
SELECT * FROM getCompFoo(1,'1');
col1 | col2
------+----------
2 | 1_result
(1 row)
使用SETOF返回多行记录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TYPE compfoo AS (col1 INTEGER, col2 TEXT);  -- 也可以返回一列;

CREATE OR REPLACE FUNCTION getSet(rows INTEGER)
RETURNS SETOF compfoo
AS $$
BEGIN
RETURN QUERY SELECT i * 2, i || '_text'
FROM generate_series(1, rows, 1) as t(i);
END;
$$ LANGUAGE PLPGSQL;
SELECT col1, col2 FROM getSet(2);
col1 | col2
------+--------
2 | 1_text
4 | 2_text
(2 rows)
使用return table返回多行多列
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE OR REPLACE FUNCTION getTable(rows INTEGER)
RETURNS TABLE(col1 INTEGER, col2 TEXT)
AS $$
BEGIN
RETURN QUERY SELECT i * 2, i || '_text'
FROM generate_series(1, rows, 1) as t(i);
END;
$$ LANGUAGE PLPGSQL;
SELECT col1, col2 FROM getTable(2);
col1 | col2
------+--------
2 | 1_text
4 | 2_text
(2 rows)
多态SQL函数

SQL函数可以声明为接受多态类型(anyelement 和 anyarray )的参数或返回多态类型的返回值。

  • 函数参数和返回值均为多态类型,其调用方式和调用其他类型的SQL函数完全相同,只是在传递字符串类型的参数时,需要显示转换到目标类型,否则会被认为unknown类型。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    CREATE OR REPLACE FUNCTION get_array(anyelement, anyelement)
    RETURNS anyarray
    AS $$
    SELECT ARRAY[$1, $2];
    $$ LANGUAGE SQL;
    SELECT get_array(1,2), get_array('a'::text,'b'::text);
    get_array | get_array
    -----------+-----------
    {1,2} | {a,b}
    (1 row)
  • 函数参数为多态类型,而返回值为基本类型

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    CREATE OR REPLACE FUNCTION is_greater(anyelement, anyelement)
    RETURNS BOOLEAN
    AS $$
    SELECT $1 > $2;
    $$ LANGUAGE SQL;
    SELECT is_greater(7.0, 4.5);
    is_greater
    ------------
    t
    (1 row)
    SELECT is_greater(2, 4);
    is_greater
    ------------
    f
    (1 row)
  • 输入输出参数均为多态类型。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    CREATE OR REPLACE FUNCTION get_array
    (IN anyelement, IN anyelement, OUT anyelement, OUT anyarray)
    AS $$
    SELECT $1, ARRAY[$1, $2];
    $$ LANGUAGE SQL;
    SELECT get_array(4,5), get_array('c'::text, 'd'::text);
    get_array | get_array
    -------------+-------------
    (4,"{4,5}") | (c,"{c,d}")
    (1 row)
函数重载

在Postgresql中,多个函数可共用一个函数名,但它们的参数必须得不同。与面向对象的函数重载类似。 因此在Posgresql删除函数时,必须显示指定参数列表。

1
DROP FUNCTION get_array(anyelement, anyelement);


参考

Postgresql 9.5 用户定义函数

Postgresql 系统函数分析记录

欣赏此文? 求鼓励,求支持!