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 use exception level that raises an error and stops the current transaction. We will discuss the raise exception later in the next section.

error通知client

To raise an error, you use the exception level after the raise statement. Note that raise statement uses the exception level by default.

Besides raising an error, you can add more information by using the following additional clause:

using option = expression
Code language: SQL (Structured Query Language) (sql)
The option can be:

message: set error message
hint: provide the hint message so that the root cause of the error is easier to be discovered.
detail: give detailed information about the error.
errcode: identify the error code, which can be either by condition name or directly five-character SQLSTATE code. Please refer to the table of error codes and condition names.
The expression is a string-valued expression. The following example raises a duplicate email

assert

assert condition [, message];

PostgreSQL provides the plpgsql.check_asserts configuration parameter to enable or disable assertion testing. If you set this parameter to off, the assert statement will do nothing.

Use the assert statement to add debugging checks to the PL/pgSQL code.
The assert statement evaluates a condition that is expected to be true and issues an error in case the condition is false or null.
Use the assert statement for detecting bugs only. For reporting ordinary messages and errors, use the raise statement instead.

if

if condition_1 then
  statement_1;
elsif condition_2 then
  statement_2
...
elsif condition_n then
  statement_n;
else
  else-statement;
end if;

do $$
declare
  selected_film film%rowtype;
  input_film_id film.id%type := 1;
begin  

  select * from film
  into selected_film
  where film.id = input_film_id;
  
  if not found then
     raise notice'The film % could not be found', 
	    input_film_id;
  else
     raise notice 'The film name is %', selected_film.name;
  end if;
end $$

The found is a global variable that is available in PL/pgSQL procedure language. If the select into statement sets the found variable if a row is assigned or false if no row is returned.

case when

case search-expression
   when expression_1 [, expression_2, ...] then
      when-statements
  [ ... ]
  [else
      else-statements ]
END case;

do $$
declare 
	rate   film.rental_rate%type;
	price_segment varchar(50);
begin
    -- get the rental rate
    select rental_rate into rate 
    from film 
    where film_id = 100;
	
	-- assign the price segment
	if found then
		case rate
		   when 0.99 then
              price_segment =  'Mass';
		   when 2.99 then
              price_segment = 'Mainstream';
		   when 4.99 then
              price_segment = 'High End';
		   else
	    	  price_segment = 'Unspecified';
		   end case;
		raise notice '%', price_segment;  
    end if;
end; $$

do $$ 
declare
    total_payment numeric; 
    service_level varchar(25) ;
begin
     select sum(amount) into total_payment
     from Payment
     where customer_id = 100; 
	 
	 if found then
	    case 
		   when total_payment > 200 then
               service_level = 'Platinum' ;
           when total_payment > 100 then
	           service_level = 'Gold' ;
           else
               service_level = 'Silver' ;
        end case;
		raise notice 'Service Level: %', service_level;
     else
	    raise notice 'Customer not found';
	 end if;
end; $$ 

loop

<<outer>>
loop 
   statements;
   <<inner>>
   loop
     /* ... */
     exit <<inner>>
   end loop;
end loop;

do $$
declare
   n integer:= 10;
   fib integer := 0;
   counter integer := 0 ; 
   i integer := 0 ; 
   j integer := 1 ;
begin
	if (n < 1) then
		fib := 0 ;
	end if; 
	loop 
		exit when counter = n ; 
		counter := counter + 1 ; 
		select j, i + j into i,	j ;
	end loop; 
	fib := i;
    raise notice '%', fib; 
end; $$

select into可以同时设置多个变量, 另一个例子:

do $$ 
<<first_block>>
declare
  film_id film.id%type;
  film_name film.name%type;
begin
   -- get the number of films
   select id, name
   into film_id, film_name
   from film limit 1;
   -- display a message
   raise notice 'The id of films is %', film_id;
   raise notice 'The name of films is %', film_name;
end first_block $$;

while

do $$
declare 
   counter integer := 0;
begin
   while counter < 5 loop
      raise notice 'Counter %', counter;
	  counter := counter + 1;
   end loop;
end$$;

for

[ <<label>> ]
for loop_counter in [ reverse ] from.. to [ by step ] loop
    statements
end loop [ label ];

do $$
begin
   for counter in 1..5 loop
	raise notice 'counter: %', counter;
   end loop;
end; $$

但是最有用的还是遍历result set

[ <<label>> ]
for row in execute query_expression [ using query_param [, ... ] ] 
loop
    statements
end loop [ label ];

do $$
declare
    -- sort by 1: title, 2: release year
    sort_type smallint := 1; 
	-- return the number of films
	rec_count int := 10;
	-- use to iterate over the film
	rec record;
	-- dynamic query
    query text;
begin
		
	query := 'select title, release_year from film ';
	
	if sort_type = 1 then
		query := query || 'order by title';
	elsif sort_type = 2 then
	  query := query || 'order by release_year';
	else 
	   raise 'invalid sort type %s', sort_type;
	end if;

	query := query || ' limit $1';

	for rec in execute query using rec_count
        loop
	     raise notice '% - %', rec.release_year, rec.title;
	end loop;
end;
$$

sql里面的字符串拼接是||

begin
   for rec in select 
			film_id, 
			title, 
            (first_name || ' ' || last_name)::varchar
		from film
		inner join film_actor using(film_id)
		inner join actor using (actor_id)
		where film_id = p_fiml_id
		order by title
	loop
        return next rec;
	end loop;
	
	return;
end;

exit

exit [label] [when boolean_expression]

<<block_label>>
BEGIN
    -- some code
    EXIT [block_label] [WHEN condition];
    -- some more code
END block_label;

还可以用来跳出block

do
$$
begin
  
  <<simple_block>>  
   begin
  	 exit simple_block;
         -- for demo purposes
	 raise notice '%', 'unreachable!';
   end;
   raise notice '%', 'End of block';
end;
$$

continue

continue [loop_label] [when condition]

function

create [or replace] function function_name(param_list)
   returns return_type 
   language plpgsql
  as
$$
declare 
-- variable declaration
begin
 -- logic
end;
$$

select get_film_count(40,90);
select get_film_count(
    len_from => 40, 
     len_to => 90
);

out 参数

create or replace function get_film_stat(
    out min_len int,
    out max_len int,
    out avg_len numeric) 
language plpgsql
as $$
begin
  
  select min(length),
         max(length),
		 avg(length)::numeric(5,1)
  into min_len, max_len, avg_len
  from film;

end;$$

procedure

A drawback of user-defined functions is that they cannot execute transactions. In other words, inside a user-defined function, you cannot start a transaction, and commit or rollback it.

procedure就是真正意义上的sql脚本了，可以开启和关闭transaction。
A stored procedure does not return a value. You cannot use the return statement with a value inside a store procedure like this:
procedure没有返回值。

create or replace procedure transfer(
   sender int,
   receiver int, 
   amount dec
)
language plpgsql    
as $$
begin
    -- subtracting the amount from the sender's account 
    update accounts 
    set balance = balance - amount 
    where id = sender;

    -- adding the amount to the receiver's account
    update accounts 
    set balance = balance + amount 
    where id = receiver;

    commit;
end;$$

使用
call stored_procedure_name(argument_list);

exception

<<label>>
declare
begin
    statements;
exception
    when condition [or condition...] then
       handle_exception;
   [when condition [or condition...] then
       handle_exception;]
   [when others then
       handle_other_exceptions;
   ]
end;

triggers

A trigger is a special user-defined function associated with a table. To create a new trigger, you define a trigger function first, and then bind this trigger function to a table. The difference between a trigger and a user-defined function is that a trigger is automatically invoked when a triggering event occurs.
注意trigger是function不是procedure，因为trigger是要有返回值的

The main drawback of using a trigger is that you must know the trigger exists and understand its logic to figure it out the effects when data changes.
不要在trigger里面实现太多业务逻辑，尽量只保存数据完整性的逻辑。

• PostgreSQL fires trigger for the TRUNCATE event.
• PostgreSQL allows you to define the statement-level trigger on views.
• PostgreSQL requires you to define a user-defined function as the action of the trigger, while the SQL standard allows you to use any SQL commands.

trigger function

A trigger function is similar to a regular user-defined function. However, a trigger function does not take any arguments and has a return value with the type trigger.

CREATE FUNCTION trigger_function() 
   RETURNS TRIGGER 
   LANGUAGE PLPGSQL
AS $$
BEGIN
   -- trigger logic
END;
$$

没有参数，返回值是trigger
A trigger function receives data about its calling environment through a special structure called TriggerData which contains a set of local variables.
trigger function可以获得很多特殊的variable，并且也是通过这些variable跟外界通信。

CREATE TRIGGER trigger_name 
   {BEFORE | AFTER} { event }
   ON table_name
   [FOR [EACH] { ROW | STATEMENT }]
       EXECUTE PROCEDURE trigger_function

虽然叫EXECUTE PROCEDURE，但是是trigger函数

trigger example

1, 创建persons table

CREATE TABLE persons(
   id BIGSERIAL PRIMARY KEY,
   first_name VARCHAR(40) NOT NULL,
   last_name VARCHAR(40) NOT NULL,
   sha CHAR(32) NOT NULL
);

CREATE TABLE persons_history(
   id BIGSERIAL PRIMARY KEY,
   op VARCHAR,
   persons_id BIGINT NOT NULL,
   first_name VARCHAR(40) NOT NULL,
   last_name VARCHAR(40) NOT NULL,
   sha CHAR(32) NOT NULL,
   created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION calc_sha_for_persons_func()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
	NEW.sha = md5(NEW.first_name || NEW.last_name);
	RETURN NEW;
END;
$$;

CREATE TRIGGER calc_sha_for_persons_trigger
  BEFORE INSERT OR UPDATE
  ON persons
  FOR EACH ROW
  EXECUTE PROCEDURE calc_sha_for_persons_func();
  
insert into persons(first_name, last_name) values('parker', 'cheng');

-- sha is automatically generated


CREATE OR REPLACE FUNCTION log_persons_history_func()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
	IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
		insert into persons_history(op, persons_id, first_name, last_name, sha, created_at) values (TG_OP, NEW.id, NEW.first_name, NEW.last_name, NEW.sha, now());
	ELSE
		insert into persons_history(op, persons_id, first_name, last_name, sha, created_at) values (TG_OP, OLD.id, OLD.first_name, OLD.last_name, OLD.sha, now());
	END IF;
	RETURN null;
END;
$$;

CREATE TRIGGER log_persons_history_trigger
  AFTER INSERT OR UPDATE OR DELETE
  ON persons
  FOR EACH ROW
  EXECUTE PROCEDURE log_persons_history_func();