PostGIS 存储过程返回类型

Postgresql存储过程返回值的方式有很多,在此先只记录一下自己用到过的,慢慢拓展

1、type型,这里geometry可以是任何postgresql支持的类型(integer/text/character varying.....)

CREATE OR REPLACE FUNCTION test(
    tbl character varying)
  RETURNS geometry AS
$BODY$  
declare  
    v_res geometry;--最终结果 
begin
    return v_res;  
end;  
$BODY$
  LANGUAGE plpgsql VOLATILE STRICT
  COST 100;
ALTER FUNCTION test(character varying)
  OWNER TO postgres;

2、table型,返回数据集的一种,需要自己定义返回的字段,这里用return query执行select返回

CREATE OR REPLACE FUNCTION test(
    IN tbl character varying)
  RETURNS TABLE(v_gid integer, v_res geometry) AS
$BODY$  
declare  
begin 
    --执行返回结果
    return query
    select v_uptap_gid as res_uptap_gid,v_uptap_geom as res_uptap_geom ;
end;  
$BODY$
  LANGUAGE plpgsql VOLATILE STRICT
  COST 100
  ROWS 1000;
ALTER FUNCTION test(character varying)
  OWNER TO postgres;

3、setof table/view型,返回数据集的一种,通常是返回某表查询后的数据

但是必须是表内已有字段,新增的字段不行(比如我要在返回时标识类型 'select type,a.* from tb a')

方便的地方在于不用强制定义返回字段('select * from tb' 也可以)

CREATE OR REPLACE FUNCTION test(
    IN tbl character varying)
  RETURNS SETOF table1 AS
$BODY$  
declare  
begin 
    SELECT * from table1; 
    --或者
    return query select * from table1;
end;  
$BODY$
  LANGUAGE plpgsql VOLATILE STRICT
  COST 100
  ROWS 1000;
ALTER FUNCTION test(character varying)
  OWNER TO postgres;

3、setof record型,返回数据集的一种,与setof table类似

不同的是,我setof record可以返回所有字段,供调用的人取字段

CREATE OR REPLACE FUNCTION test(
    IN tbl character varying)
  RETURNS SETOF record AS
$BODY$  
declare  
r record;
begin 
    for r in EXECUTE 'select * from tb'  loop
       return next r;
    end loop;
    return;
end;  
$BODY$
  LANGUAGE plpgsql VOLATILE STRICT
  COST 100
  ROWS 1000;
ALTER FUNCTION test(character varying)
  OWNER TO postgres;

--调用
select * from test('tb')  as member(id int, name text);

 

posted @ 2019-10-11 18:27  苍龙de链  阅读(831)  评论(0编辑  收藏  举报