postgreSQL存储过程函数的使用方法与常见问题解决

PL/pgSQL是一种块结构的语言，基本方式如下：
CREATE OR REPLACE FUNCTION 函数名(参数1，[整型 int4,整型数组 _int4,…])
RETURNS 返回值类型 AS
$BODY$
DECLARE
变量声明
BEGIN
函数体
END;
$BODY$
LANGUAGE ‘plpgsql’ VOLATILE;

功能描述：从查询的结果中利用表名查询所需内容。

create or replace function fff(userid int) 
returns setof record as   --返回类型，结果集
    $$
    declare
        sql varchar; --定义变量
	   asql varchar;
        re record;
	fav record;
    begin
        sql = 'select DISTINCT type as tablename from bmap_favorite where type<>'''||'0'|| '''and userid='''|| userid ||'''';
	-----sql
        for re in execute sql loop     --循环执行后的结果集
           
		asql = 'select f.gid,f.favtime,a.name,a.city,a.district,a.province,a.address,st_x(a.geom) as x,st_y(a.geom) as y
 from '|| re.tablename ||' a,bmap_favorite f where a.gid = cast(f.gid as INTEGER) and f.userid=2 and f.type='''|| re.tablename ||'''';
		for fav in execute asql loop
			return next fav;
		end loop;
        end loop;
        
        return;
    end
    $$
language plpgsql;
 
<h2 class="entry_title" style="margin: 0px; padding: 0px; border: 0px; line-height: 22px; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"><pre name="code" class="sql"><span style="font-family:FangSong_GB2312;font-size:14px;font-weight: normal;"><span style="background-color: rgb(255,255,255);">1.</span><span style="background-color: rgb(255,255);">执行存储过程中，出现“set-valued function called in context that cannot accept a set”的错误</span></span>

，在数据库里执行函数，可以写“select funciton();”也可以写“select * from function()”

select * from fff(2) as f(gid varchar,favtime varchar,name VARCHAR,city varchar,district varchar,provice varchar,address varchar,x float,y float) --执行存储过程