postgresql 存储过程简单应用实例

functions

postgresql functions又可以成为Stored Procedures,也就是我们常说的存储过程。这个名字很奇怪，因为
pg的sql函数也叫function，不知道外国人怎么区分这个东西。
基本语法：

CREATE FUNCTION somefunc(integer, text) RETURNS integer
AS '过程body'
LANGUAGE plpgsql;

存储过程的语法很简单，包括了存储过程的名字，参数，返回类型等信息。真正复杂的是过程body的定义。
body的范例如下：

CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
    quantity integer := 30;
BEGIN
    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30
    quantity := 50;
    --
    -- Create a subblock
    --
    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;

过程定义body需要用$$包裹，而不是单引号或双引号。因为单引号或双引号包裹，则body中就需要将所有的单双引号转义。
如上面所示，body必须是一个完整的块，这个块用begin和end包裹。

[ DECLARE
    declarations  ]
BEGIN
    statements
END [ ];

如上例，块是可以嵌套的，可以有子块。

cursor

游标可以在声明的时候，定义sql：

DECLARE
    curs2 CURSOR FOR SELECT * FROM tenk1;

也可以先定义变量，在open的时候关联sql：

DECLARE
    curs1 refcursor;
begin
    open curs1 FOR SELECT * FROM tenk1;

循环访问的两种方式：

FOR recordvar IN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] LOOP
    statements
END LOOP

declare 
    ff3_list refcursor;
    ff3_item record;
begin
    loop 
        fetch ff3_list into ff3_item;
        if not found then exit;
        end if;
    end loop;
end;

insert

通过select插入

INSERT INTO films SELECT * FROM tmp_films

SELECT * INTO films_recent FROM films

select

将查询结果赋值给变量：

select  ff.id into fabric_id_o from public.fabric_fabric where...

服务器端打印日志

raise notice 'new ff3 id: %', ff3_id_new;

代码实例

declare中声明变量，实例中使用了三种：cursor， record和普通变量。

create or replace
function public.createdata(l1supercategory int, nameLike text, l2supercategory int) returns int4  as $$ 
declare 
ff3_list refcursor;
ff3_item record;
ff3_id int default 1;
ff3_id_new int ;
fabric_id_o int;
fabric_id_new int;
titles text default '';
begin 
open ff3_list for
select
	ff3.id,
	ff3.name,
	ff3."valid",
	ff3."orderValue" ,
	ff3."level" ,
	l2supercategory as supercategory_id ,
	ff3."uploadCount"
from
	public.fabric_fabriccategory ff3
inner join public.fabric_fabriccategory ff2 on
	ff3.supercategory_id = ff2.id
	and ff2.supercategory_id = 1596
where
	ff3."name" like nameLike
limit 1;
loop 
fetch ff3_list into ff3_item;
if not found then exit;
end if;
titles := ff3_item.id || ',' || ff3_item."name";
raise notice 'values: %', titles;
ff3_id := ff3_item.id;

ff3_id_new = nextval('fabric_fabriccategory_id_seq'::regclass);
raise notice 'new ff3 id: %', ff3_id_new;
insert into public.fabric_fabriccategory (id, "name" , "valid" ,"orderValue","level", supercategory_id, "uploadCount")
values(ff3_id_new, ff3_item."name", ff3_item."valid",ff3_item."orderValue", ff3_item."level", ff3_item.supercategory_id, ff3_item."uploadCount" );

select  ff.id into fabric_id_o from public.fabric_fabric ff where ff.category_id  = ff3_id and ff."valid" =true;
fabric_id_new = nextval('fabric_fabric_id_seq'::regclass);

insert into public.fabric_fabric ( 
select fabric_id_new as id, ff."name" ,ff."simulationCount" ,ff."valid" ,ff.user_id ,ff3_id_new as category_id ,ff."orderValue" 
,ff.image, ff."mappingImage",ff.thumb ,ff.dpi ,ff.width, ff.height , ff.belonging_id 
from public.fabric_fabric ff where ff.id = fabric_id_o);

raise notice 'new fabric id: %', fabric_id_new;
raise notice 'old fabric id: %', fabric_id_o;

INSERT INTO public.fabric_fabricinformation(
select nextval('fabric_fabricinformation_id_seq'::regclass) as id, fabric_id_new as fabric_id , fff.code , fff.weight , fff.width ,fff.composition ,fff.price ,fff."oneRepeatSize" , fff."text" 
from public.fabric_fabricinformation fff where fff.fabric_id =fabric_id_o 
);

insert  into public.fabric_detailimage (
select nextval('fabric_detailimage_id_seq'::regclass) as id, fabric_id_new as fabric_id ,image ,thumb from public.fabric_detailimage fd 
where fd.fabric_id = fabric_id_o
order by fd.id asc
);
end loop;

close ff3_list;
return ff3_id_new;
end

$$language plpgsql;