PL/pgSQL and triggers

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