PL/pgSQL简单上手

前言
PL/pgSQL是Postgres的sql实现，借鉴了PL/SQL，后者是Oracle的sql实现

语法

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

例：

CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
    quantity integer := 30;
BEGIN
    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30
    quantity := 50;
    --
    -- 创建一个子块
    --
    DECLARE
        quantity integer := 80;
    BEGIN
        RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 80
        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- Prints 50
    END;

    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 50

    RETURN quantity;
END;
$$ LANGUAGE plpgsql;

注意：

1. $$ 或者 $xxx$ 是函数代码段的起止符
2. :name 动态入参，运行时会求填参；（在Oracle里是&name）
3. do $$ ... $$ 可以不定义直接运行函数段

实例：

DO $body$
declare
i integer;
K integer;
num integer;
t timestamp;
tablename varchar; 
t1 timestamp;
begin    
num :=3218101;
i := 1;
t:=:t;    
t1:=t;
<<goto_label>>
WHILE i<=17280 loop
INSERT INTO :tablename
("number", transducertype, siteid, value, "maxvalue", maxvaluetime, "minvalue", minvaluetime, changecount, lasttime)
VALUES(num, 1, 10, null, 0, null, 0, null, 0, t);
 i:=i+1;
t:=t+'5sec';
 IF i = 17280 then
 num:=num+1;
 IF num <= 3218151 then
 i := 1;
t:=t1;    
    CONTINUE goto_label;
     END IF;
  END IF;
END loop;
end;
$body$ LANGUAGE PLPGSQL;

---

手册：PL/pgSQL的结构