文章目录
what is PL/pgSQL
PL/pgSQL is a procedural programming language for the PostgreSQL database system.
主要用来创建函数,触发器,是扩展服务器端功能的有力手段。
tutorial
dollar-quoted string constants
$tag$<string_constant>$tag$
在tag之间的string不会有单引号双引号escape等问题
eg
pl_pgsql=# select $$I'm a "teacher"!$$;
?column?
------------------
I'm a "teacher"!
(1 row)
所以$$通常用来包裹复杂的语句,非常适合用来定义函数和触发器;
eg: 创建函数
pl_pgsql=# create or replace function find_film_by_id(
id int
) returns film
language sql
as
$$
select * from film
where film.id = id;
$$;
CREATE FUNCTION
pl_pgsql=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-----------------+------------------+---------------------+------
public | find_film_by_id | film | id integer | func
(1 row)
block
定义
[ <<label>> ]
[ declare
declarations ]
begin
statements;
...
end [ label ];
eg:
pl_pgsql=# do $$
pl_pgsql$# <<first_block>>
pl_pgsql$# declare
pl_pgsql$# film_var film;
pl_pgsql$# begin
pl_pgsql$# -- get the number of films
pl_pgsql$# select *
pl_pgsql$# into film_var
pl_pgsql$# from film limit 1;
pl_pgsql$# -- display a message
pl_pgsql$# raise notice 'The id of films is %', film_var.id;
pl_pgsql$# raise notice 'The name of films is %', film_var.name;
pl_pgsql$# end first_block $$;
NOTICE: The id of films is 1
NOTICE: The name of films is rq
DO
do
is a function that executes anonymous block
variable
variable_name data_type [:= expression];
variable必须定义类型,并且必须在block的declare语句块定义.所有在create table使用的column的类型都可以用于变量类型。
eg:
do $$
declare
counter integer := 1;
first_name varchar(50) := 'John';
last_name varchar(50) := 'Doe';
payment numeric(11,2) := 20.5;
begin
raise notice '% % % has been paid % USD',
counter,
first_name,
last_name,
payment;
end $$;
注意numeric的参数代表整数和小数部分的宽度
经常有情况是你希望某个变量存储某个column的类型
variable_name table_name.column_name%type;
或者是另一个变量的类型
variable_name variable%type;
可以看到第一种是第二种的特殊形式
rowtype:
row_variable table_name%ROWTYPE;
row_variable view_name%ROWTYPE;
row_variable.field_name
sql
do $$
declare
selected_actor actor%rowtype;
begin
-- select actor with id 10
select *
from actor
into selected_actor
where actor_id = 10;
-- show the number of actor
raise notice 'The actor name is % %',
selected_actor.first_name,
selected_actor.last_name;
end; $$
record
a record variable can change its structure when you reassign it.
do
$$
declare
rec record;
begin
-- select the film
select film_id, title, length
into rec
from film
where film_id = 200;
raise notice '% % %', rec.film_id, rec.title, rec.length;
end;
$$
language plpgsql;
record类型特别适合循环处理过程中用来迭代
do
$$
declare
rec record;
begin
for rec in select title, length
from film
where length > 50
order by length
loop
raise notice '% (%)', rec.title, rec.length;
end loop;
end;
$$
constant
与variable类似,只是不能变而已,定义的时候有个关键字.
constant_name constant data_type := expression;
constant只代表在begin-end之间不能修改,在declare的时候是可以初始化为一个可变的值的。跟java 的final一样,只代表初始化以后就不能变,不是只能初始化为常量。
do $$
declare
start_at constant time := now();
begin
raise notice Start executing block at %', start_at;
end $$;
log
raise level format;
debug
log
notice
info
warning
exception
If you don’t specify the level, by default, the raise statement will