PL/pgSQL

概述

PL/pgSQL是PstgreSQL数据库系统的一个可加载的过程语言，可以：

用于创建函数和触发器过程
为SQL语言增加控制结构
执行复杂的计算
继承所有用户定义类型、函数、操作符
定义为被服务器信任的语言

使用PL/pgSQL的优点

通过PL/pgSQL，可以把运算块和一系列命令在数据库服务器内部组成一个块，这样就拥有了过程语言的能力并且简化 SQL 的使用，因而节约了大量的时间，因为不需要进行客户端/服务器通讯。

忽略了客户端和服务器端之间的额外往返行程。
客户端不需要的中间结果无需在服务器端和客户端来回传递。
不需要多次语法分析步骤。

支持的参数和结果数据类型

PL/pgSQL的结构

[ <<label>> ]
[DECLARE
    declarations]
BEGIN
    statements
END [ label ]；

块中的每个声明和每条语句都是用一个分号终止的，如果一个子块在另外一个块里，那么END后面必须有个分号；
如果你想标记出在EXIT声明中的block，或者描述在block中所声明的变量名字，此时，可以选择使用label。如果是在END之后给出一个标签，那么，它必须与block开始时定义的标签相匹配；
所有的关键字都是不区分大小写的；
在语句的最后，通过一个双破折号(—)来开始一条行注释。而块注释是成对出现的，通过/和/来定义；
块语句段里的任何语句都可以是一个子块。子块可以用于逻辑分组或者把变量局部化为作用于一个较小的语句组。为了子块的持续时间任何同样命名的外部子块的变量在子块中声明变量，但是如果你符合它们的名字和它们子块标签，无论如何你可以访问外部变量。

CREATE FUNCTION somefunc() RETURNS integer AS $$
<<outerblock>>
DECLARE
    quantity integer := 30;
BEGIN
    RAISE NOTICE 'Quantity here is %', quantity;  -- 在这里的数量是30
    quantity := 50;
    -- 创建一个子块
    <<innerblock>>
    DECLARE
        quantity integer := 80;
    BEGIN
        RAISE NOTICE 'Quantity here is %', quantity;  -- 在这里的数量是80
        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- 在这里的数量是50
    END;
    RAISE NOTICE 'Quantity here is %', quantity;  -- 在这里的数量是50
    RETURN quantity;
END;
$$ LANGUAGE plpgsql;

声明

所有在块里使用的变量都必须在一个块的声明段里声明。

$$的作用

$中间可以包含可选的标签（也可以为空），但是标签要成对出现，且大小写敏感 * 每对$$中间类似代码块，包含的代码中不用书写转义字符，取代了使用“'”来包含代码段，下列代码段等价： <pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">-- 代码1 create or replace function somefunc() returns varchar as$

declare
name varchar := ‘wangzhen’;
begin
return name;
end

$language plpgsql ；</code></pre> <pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">--代码2 create or replace function somefunc() returns varchar as ' declare name varchar := ''wangzhen''; begin return name; end ' language plpgsql ;</code></pre> * 中间的子代码块也可以通过$$包含，下列代码段等价 <pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">--代码3 create or replace function somefunc() returns varchar as $body$ declare name varchar := $tag$wangzhen'blog$tag$; begin return name; end $body$ language plpgsql ;</code></pre> <pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">--代码4 create or replace function somefunc() returns varchar as$

declare
name varchar := ‘wangzhen’’blog’;
begin
return name;
end

declare
name varchar := newname;
begin
return name;
end;

$language plpgsql; </code></pre> <pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">create or replace function somefunc(varchar,varchar) returns varchar as$

declare
— $1代表第一个参数，$2代表第二个参数
newname alias for $1;
name varchar := newname;
name2 varchar := $2;
begin
return name;
end;

declare
var_sql varchar := ‘insert into test1 values (0,’||quote_literal(‘admin’)||‘)’;
begin
execute var_sql;
execute format(‘insert into %I values (%L,%L)’,‘test1’,2,‘xiaoniu’);
end;

begin
return 100;
end

begin
v1 := 100;
end

$language plpgsql;</code></pre> 3. 返回单行record复合类型 <pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">create or replace function somefunc4(id integer,mc varchar) returns record as$

declare
r record;
begin
r := row(id,mc);
return r;
end

begin
create table test1 (n_id integer,c_mc varchar(300));
insert into test1 (n_id,c_mc) values (1,‘wangzhen’);
end;

declare
id int;
begin
for id in select n_id from test1
loop
return next id;
end loop;
return;
end

$language plpgsql;</code></pre> 2. 通过out参数返回，returns setof record可以省略 <pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">create or replace function somefunc6(out id integer,out name varchar) returns setof record as$

declare
r record;
begin
for r in select n_id,c_mc from test1
loop
id := r.n_id;
name := r.c_mc;
return next;
end loop;
end

$language plpgsql;</code></pre> 3. 通过return next返回自定义类型 <pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">create type test_rs as (id int,mc varchar); create or replace function somefunc7() returns setof test_rs as$

declare
r test_rs%rowtype;
begin
for r in select n_id,c_mc from test1
loop
return next r;
end loop;
return;
end

declare
r test_rs%rowtype;
begin
return query select n_id,c_mc from test1;
return;
end

$$ language plpgsql;