postgresql下存储过程的写法

在当前这个项目里（empress)，我主要应用到了两种服务端编程语言：扩展SQL中的查询语言函数（SQL）和PL/pgSQL SQL过程语言。前者仅包括了简单的sql语句，后者还包括了一些条件、循环控制等。 这些函数都可以存放在文本中，使用/i 命令导入到db中即可。 首先是SQL的例子： create or replace function set_relation_done(integer,integer) returns void AS $$ update relations set done=TRUE where userid=$1 and friendid=$2; $$ language SQL; 这是一个没有返回值的函数，第一行和第三行是很格式化的东西，中间的才是正文，实现update的功能，可以看出，它和直接的sql语句没有太大的区别。 create or replace function get_relation_is_done(integer,integer,OUT done boolean) AS $$ select done from relations where userid=$1 and friendid=$2; $$ language SQL; drop type tag_count_t CASCADE; create type tag_count_t AS (tag text,count integer); create or replace function get_my_tags(integer,integer) returns setof tag_count_t AS $$ select tag,use_count from friend_tags where friendid=$1 and use_count>0 order by use_count DESC LIMIT $2; $$ language SQL; 也可以构造有返回值的函数，如果是简单类型的返回值，可以很方便的放在参数的OUT修饰符后面（当然也可以returns），如果是较复杂的setof等，就要放在returns后面了。这里，不需要显式的return语句，最后一条有返回值的sql语句的结果将被作为结果返回。这时，需要保证返回值的存在和类型匹配。 然后是plpgsql语言的例子： create or replace function insert_friend_tags(id integer,to_tags text) returns void AS $$ declare to_tags_a text[]; begin to_tags_a := string_to_array(to_tags,','); for i in array_lower(to_tags_a,1)..array_upper(to_tags_a,1) loop insert into friend_tags (friendid,tag) values(id,to_tags_a[i]::text); end loop; end; $$ language plpgsql; 这是一个没有返回值的例子，使用了string_to_array系统函数，将传入的逗号分割的文本处理成数组（貌似不能直接传数组，谁知道可以或者怎样更方便的话，请留言，谢谢）。然后利用for循环处理每条内容。 create or replace function get_unrelation_friend_ids(integer,text) returns setof tag_ids_t as $$ declare friend_ids integer[]; num integer; un_friend_ids tag_ids_t; begin friend_ids := string_to_array($2,'); for i in array_lower(friend_ids,1)..array_upper(friend_ids,1) loop perform friendid from relations where userid=$1 and friendid=friend_ids[i]::integer; if found then continue; else select into un_friend_ids friend_ids[i]::integer; return next un_friend_ids; end if; end loop; return; end; $$ language plpgsql; 有返回值的时候，需要return next和return配合使用。if found可以用来检验上一条语句的执行状态，不仅针对select、perform，甚至update、insert等都可以。