[PostgreSql]PostgreSql创建函数及函数的执行

1.准备好创建函数的脚本

 

-- FUNCTION: public.dynamic_placelist_select(integer, timestamp without time zone)

-- DROP FUNCTION public.dynamic_placelist_select(integer, timestamp without time zone);

CREATE OR REPLACE FUNCTION public.dynamic_placelist_select(
    eigyousyo_idl integer,
    minversionl timestamp without time zone)
RETURNS TABLE(address1 character varying, eigyousyo_id integer, icon integer, latitude character varying, longitude character varying, place_code character varying, place_id integer, place_name character varying,
 deletef smallint, version timestamp without time zone) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$
BEGIN
-- Insert statements for procedure here
    RETURN QUERY

select mst_Place.address1, 
mst_Place.eigyousyo_id,
mst_Place.icon,
mst_Place.latitude,
mst_Place.longitude,
mst_Place.place_code,
mst_Place.place_id,
mst_Place.place_name,
mst_Place.deletef,
mst_Place.version
from mst_Place
where (mst_Place.eigyousyo_id=eigyousyo_idl and
mst_Place.version>minVersionl and mst_Place.place_type=1)
order by mst_Place.place_code;
END;

$BODY$;

ALTER FUNCTION public.dynamic_placelist_select(integer, timestamp without time zone)
    OWNER TO postgres;

 

2.在Functions上右键选择Query Tool

 

 

3.将代码粘贴到编辑面板，点击图中的1执行函数创建脚本，图中2结果表示函数创建成功

 

 

4.在Functions上刷新后可看到函数已建成功，如5所示

 

 

5.在函数上右键选择SELECT Script执行函数

 

 

6.在SELECT Scripte的编辑面板填入参数后点击运行按钮，Data Output的结果为检索结果

 

 

7.另外CREATE Script可对函数进行修改

 

转载于:https://www.cnblogs.com/vickylinj/p/9664459.html