文档章节

PostgreSQL【PL/pgSql】

SibylY
 SibylY
发布于 2015/12/07 17:39
字数 2250
阅读 47
收藏 3

PL/pgSQL

所有 SQL 语句都必须由数据库服务器独立地执行,这就意味着你的客户端应用必须把每条命令发送到数据库服务器, 等待它处理这个命令,接收结果,做一些运算,然后给服务器发送另外一条命令。 所有这些东西都会产生进程间通讯,并且如果你的客户端在另外一台机器上甚至还会导致网络开销。

如果使用了PL/pgSQL,那么你可以把一块运算和一系列命令在数据库服务器里面组成一个块, 这样就拥有了过程语言的力量并且简化 SQL 的使用,因而节约了大量的时间,因为你用不着付出客户端/服务器通讯的过热。 这样可能产生明显的性能提升。

PL/pgSQL引号

PL/pgSQL函数的代码都是在CREATE FUNCTION里以一个字串文本的方式声明的。如果你用两边包围单引号的常规方式写字串文本,那么任何函数体内的单引号都必须写双份;类似的是反斜杠也必须双份。 双份引号非常乏味,在更复杂的场合下,代码可能会让人难以理解, 因为你很容易发现自己需要半打甚至更多相连的引号。 我们建议你用"美元符包围"的字串文本来写函数体。 使用美元符包围的时候,你从不需要对任何引号写双份, 只需要为每层引号包围嵌套选择一个不同的美元符号包围分隔符即可。 比如,你可能这么写CREATE FUNCTION命令.

声明

name[ CONSTANT ]type[ NOT NULL ] [ { DEFAULT | := }expression];
user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.fieldname%TYPE;
arow RECORD;
函数别名

传递给函数的参数都是用$1,$2,等等这样的标识符。 为了增加可读性,我们可以为$n参数名声明别名。

CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
同下面:
CREATE FUNCTION sales_tax(REAL) RETURNS real AS $$
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

拷贝类型:

user_id users.user_id%TYPE;

行类型:

nametable_name%ROWTYPE; namecomposite_type_name;
一个复合类型变量叫做 变量(或者 row-type变量)。 这样的一个变量可以保存一次SELECT或者FOR命令结果的完整一行,只要命令的字段集匹配该变量声明的类型。 行数值的独立的字段是使用常用的点表示法访问的,比如rowvar.field。
CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
DECLARE
    t2_row table2%ROWTYPE;
BEGIN
    SELECT * INTO t2_row FROM table2 WHERE ... ;
    RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$ LANGUAGE plpgsql;

SELECT merge_fields(t.*) FROM table1 t WHERE ... ;
记录类型:

表达式:

执行一个没有结果的表达式或者命令

计算一个表达式或者一个命令,但是却丢弃其结果(通常因为我们经常调用一些存在有用的副作用但是不存在有用结果值的函数)。 要在 PL/pgSQL 里干这件事, 你可以使用PERFORM语句:

PERFORM query;

执行动态命令

每次执行的时候都会涉及不同表或不同数据类型的命令.

在使用动态命令的时候,你经常需要逃逸单引号。我们建议包围你的函数体内固定文本的方法是美元符包围。要插入到构造出来的查询中的动态数值也需要特殊的处理, 因为他们自己可能包含引号字符。

EXECUTE 'UPDATE tbl SET '
        || quote_ident(columnname)
        || ' = '
        || quote_literal(newvalue)
        || ' WHERE ...';
为了安全,包含字段和表标识符的变量应该传递给函数quote_ident。 那些包含数值的变量,如果其值在构造出来态命令字串里应外是文本字串,那么应该传递给quote_literal。 它们俩都会采取合适的步骤把输入文本包围在单或双引号里并且对任何嵌入其中的特殊字符进行合适的逃逸处理。

获取结果状态

第一个方法是使用GET DIAGNOSTICS,它的形式如下:

GET DIAGNOSTICSvariable=item[ , ... ];

另外一个判断命令效果的方法是一个类型为boolean的特殊变量FOUND。FOUND在每个 PL/pgSQL 函数里开始都为假。它被下列语句设置:

  • 一个SELECT INTO语句如果返回一行则设置FOUND为真,如果没有返回行则设置为假。

  • 一个PERFORM语句如果生成(或抛弃)一行,则设置FOUND为真,如果没有生成行则为假。

  • 如果至少影响了一行,那么UPDATE,INSERT,和DELETE语句设置FOUND为真,如果没有行受影响则为假。

  • FETCH语句如果返回行则设置FOUND为真, 如果不返回行则为假。

  • 一个FOR语句如果迭代了一次或多次,则设置FOUND为真,否则为假。这个规律适用于所有FOR语句的三种变体 (整数FOR循环,记录集的FOR循环,

控制结构

从函数返回

RETURN expression;

带表达式的RETURN是用于终止函数, 然后expression的值返回给调用者。

RETURN NEXT expression;
条件

IF语句让你可以根据某种条件执行命令。 PL/pgSQL有五种形式的IF:

  • IF ... THEN

  • IF ... THEN ... ELSE

  • IF ... THEN ... ELSE IF

  • IF ... THEN ... ELSIF ... THEN ... ELSE

  • IF ... THEN ... ELSEIF ... THEN ... ELSE

IF boolean-expression THEN     statements [ ELSIF boolean-expression THEN     statements [ ELSIF boolean-expression THEN     statements...]]
[ ELSE statements]
END IF;
简单循环
[ <<label>> ]
LOOP     statements END LOOP [ label];
LOOP
    -- 一些计算
    IF count > 0 THEN
        EXIT;  -- exit loop
    END IF;
END LOOP;
LOOP
    -- 一些计算
    EXIT WHEN count > 100;
    CONTINUE WHEN count < 50;
    -- 一些在 count 数值在 [50 .. 100] 里面时候的计算
END LOOP;
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- 可以在这里做些计算
END LOOP;
FOR i IN 1..10 LOOP
  -- 这里可以放一些表达式
    RAISE NOTICE 'i IS %', i;
END LOOP;

FOR i IN REVERSE 10..1 LOOP
    -- 这里可以放一些表达式
END LOOP;
CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
DECLARE
     mviews RECORD;

BEGIN
     PERFORM cs_log('Refreshing materialized views...');

     FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP

         -- 现在 "mviews" 里有了一条来自 cs_materialized_views 的记录

        PERFORM cs_log('Refreshing materialized view ' || quote_ident(mviews.mv_name) || ' ...');
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
        EXECUTE 'INSERT INTO  ' ||  quote_ident(mview.mv_name) || ' ' || mview.mv_query;
     END LOOP;

     PERFORM cs_log('Done refreshing materialized views.');
     RETURN 1;
END;
$$ LANGUAGE plpgsql;
捕获错误
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
BEGIN
    UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
    x := x + 1;
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
    RAISE NOTICE 'caught division_by_zero';
    RETURN x;
END;
声明游标

所有在 PL/pgSQL 里对游标的访问都是通过游标变量实现的, 它总是特殊的数据类型refcursor。创建一个游标变量的一个方法是把它声明为一个类型为refcursor的变量。 另外一个方法是使用游标声明语法,通常是下面这样:

name CURSOR [ (arguments) ] FOR query;
DECLARE
    curs1 refcursor;
    curs2 CURSOR FOR SELECT * FROM tenk1;
    curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
所有这三个变量都是类型为refcursor, 但是第一个可以用于任何命令,而第二个已经 绑定 了一个声明完整的命令,最后一个是绑定了一个带参数的命令。 (key将在游标打开的时候被代换成一个整数。)

打开游标

在你使用游标检索行之前,你必需宪打开它。

OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
该游标变量打开,并且执行给出的查询。游标不能是已经打开的, 并且它必需是声明为一个未绑定的游标。查询必须是一条SELECT,或者其它返回行的东西(比如EXPLAIN)。 查询是和其它在 PL/pgSQL 里的 SQL 命令平等对待的: 先代换 PL/pgSQL 的变量名,而且执行计划为将来可能的复用缓存起来。
OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);

打开游标变量并且执行给出的查询。游标不能是已打开的,并且必须声明为一个未绑定的游标。命令是用和那些用于EXECUTE命令一样的方法声明的字串表达式, 这样,我们就有了命令可以在两次运行间发生变化的灵活性。

打开一个绑定的游标

OPEN curs2;
OPEN curs3(42);
使用游标
FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH从游标中检索下一行到目标中,目标可以是一个行变量,一个记录变量, 或者是一个逗号分隔的普通变量的列表。
CLOSE cursor;
CLOSE关闭支撑在一个打开的游标下面的信使。 这样我们就可以在事务结束之前施放资源,或者释放掉该游标变量,用于稍后再次打开。
CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
       OPEN $1 FOR SELECT col FROM test;
       RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;



所有在块里使用的变量都必须在一个块的声明段里声明。 (唯一的例外是一个FOR循环里的循环变量是在一个整数范围内迭代的,被自动声明为整数变量。



© 著作权归作者所有

上一篇: curl命令
下一篇: 【端口】
SibylY
粉丝 30
博文 439
码字总数 360908
作品 0
海淀
程序员
私信 提问
‘PostgreSQL用户会’携手‘机工社华章公司’发起以下赠书活动

活动参与方式: QQ空间分享此帖:并将分享截图发布到此帖下面,即有机会获赠精美图书; 活动时间:11.12—11.19 活动奖品: 《PostgreSQL服务器编程》,总10本。结束后,由机工社华章公司统一...

YuanyuanL
2014/11/12
0
2
启用zhparser插件时一直报Permission denied

如下 postgres=# CREATE EXTENSION zhparser; ERROR: could not open file "/usr/pgsql-9.6/share/tsearchdata/qcdict_postgres.txt" for writing: Permission denied CONTEXT: SQL statemen......

dragon_tech
04/23
0
0
PostgreSQL 数据库集群和PL/Proxy配置安装指南

PL/Proxy和PostgreSQL集群的结构关系可以用下图清楚地表示,对PL/Proxy和PostgreSQL集群还不太了解的朋友可以看Skype Plans for PostgreSQL to Scale to 1 Billion Users这篇文章。 以下操作...

红薯
2009/01/12
1K
1
Migration Oracle to PostgreSQL "百家"文档集

标签 PostgreSQL , Oracle 背景 2002 Porting from Oracle to PostgreSQL 《PDF Download》 Agenda SQL Syntax, Functions, Sequences, Etc. Database Server General Characteristics Data......

德哥
2018/05/06
0
0
PostgreSQL cheat functions - (内存上下文\planner内容\memory context等常用函数)

标签 PostgreSQL , memory context , plan , pid signal , ... 背景 https://github.com/MasaoFujii/pgcheatfuncs 这个插件汇集了常用的PG扩展函数(将来也许会引入到PG社区中),比如 1、查...

德哥
04/14
0
0

没有更多内容

加载失败,请刷新页面

加载更多

0.01-Win10安装linux子系统

一、安装Debian子系统 -1、控制面板设置: -1.1、打开“控制面板” —— “程序” —— “启用或关闭Windows功能” —— 勾选 “适用于Linux的Windows子系统” -2、设置: -2.1、打开“设置”...

静以修身2025
昨天
2
0
init 0-6 (启动级别:init 0,1,2,3,4,5,6)

启动级别: init 0,1,2,3,4,5,6 这是个很久的知识点了,只是自己一直都迷迷糊糊的,今天在翻出来好好理解下。。 0: 停机 1:单用户形式,只root进行维护 2:多用户,不能使用net file system...

圣洁之子
昨天
2
0
Android Camera HAL浅析

1、Camera成像原理介绍 Camera工作流程图 Camera的成像原理可以简单概括如下: 景物(SCENE)通过镜头(LENS)生成的光学图像投射到图像传感器(Sensor)表面上,然后转为电信号,经过A/D(模数转...

天王盖地虎626
昨天
2
0
聊聊Elasticsearch的ProcessProbe

序 本文主要研究一下Elasticsearch的ProcessProbe ProcessProbe elasticsearch-7.0.1/server/src/main/java/org/elasticsearch/monitor/process/ProcessProbe.java public class ProcessProb......

go4it
昨天
3
0
mysql PL(procedure language)流程控制语句

在MySQL中,常见的过程式SQL语句可以用在存储体中。其中包括IF语句、CASE语句、LOOP语句、WHILE语句、ITERATE语句和LEAVE语句,它们可以进行流程控制。 IF语句相当于Java中的if()...else if(...

edison_kwok
昨天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部