postgres 功能函数示例

生成 UUID

CREATE OR REPLACE FUNCTION "public"."uuid_generate_v4"()
  RETURNS "pg_catalog"."uuid" AS '$libdir/uuid-ossp', 'uuid_generate_v4'
  LANGUAGE c VOLATILE STRICT
  COST 1

生成GUID

create or replace function "atmap"."sys_guid"()
  returns "pg_catalog"."varchar" as $body$
begin
    return replace(cast(uuid_generate_v1() as varchar(100)), '-', '');
end;
$body$
  language plpgsql volatile
  cost 100

根据父级查询所有子级 连级如:北京市水务局 > 河湖管理处 > 第一管理所

with recursive cte as(
	select a.atid,cast(a.name as varchar(200)) from app_aqxj_equipment a
	where a.atid='9bf93d4ce4c74c8ab2ba3064e23a99f2'
	union all
	select k.atid,cast(c.name||' > '||k.name as varchar(200)) as name from app_aqxj_equipment k
	inner join cte c on c.atid = k.parent_id
	--where k.type_id <> 'xunjianxiang'
)select atid,name from cte

根据自己查询父级

with recursive r as(
	select atid,name,parent_id from app_aqxj_equipment where atid = 'e22c5b75c3b840018e8d9303563e3857'
	union ALL
	select app_aqxj_equipment.atid,app_aqxj_equipment.name,app_aqxj_equipment.parent_id from app_aqxj_equipment, r 
	where app_aqxj_equipment.atid = r.parent_id
)
select atid,concat_ws(' > ',name,'') as name,parent_id from r

对应的fun函数

create or replace function "public"."fun_getParentNamebyChild"(in "id" varchar, out "o_area" text) 
	returns "pg_catalog"."text" as $body$
declare
 v_rec_record record;
begin
 o_area = '';
 for v_rec_record in (
	with recursive r as(
		select atid,name,parent_id from app_aqxj_equipment where atid = "id"
		union ALL
		select app_aqxj_equipment.atid,app_aqxj_equipment.name,app_aqxj_equipment.parent_id from app_aqxj_equipment, r 
		where app_aqxj_equipment.atid = r.parent_id
	)
	select atid,concat_ws(' > ',name,'') as name,parent_id from r
 ) loop
 o_area := o_area || v_rec_record.name;
 
 end loop;
 o_area := btrim(o_area,' > ')
 return;
end;
$body$
  language plpgsql volatile
  cost 100

通过开始时间结束时间获得中间多小时多分钟多分秒

create or replace function getMedianTime(starttime TIMESTAMP,endtime TIMESTAMP)
returns varchar as $$
declare
	p_day integer;
	p_hour integer;
	p_minute integer;
	p_second integer;
	sumTime varchar;
begin

	sumTime := '';
	p_day := (select extract(day FROM (age(endtime, starttime))));
	if p_day <> 0 then
		sumTime := sumTime || p_day || '天';
	else
		sumTime := sumTime || '0天';
	end if;
	
	p_hour := (select extract(hour FROM (age(endtime, starttime))));
	if p_hour <> 0 then
		sumTime := sumTime || p_hour || '时';
	else
		sumTime := sumTime || '0时';
	end if;
	
	p_minute := (select extract(minute FROM (age(endtime, starttime))));
	if p_minute <> 0 then
		sumTime := sumTime || p_minute || '分钟';
	else
		sumTime := sumTime || '0分钟';
	end if;
	
	p_second := (select extract(second FROM (age(endtime, starttime))));
	if p_second <> 0 then
		sumTime := sumTime || p_second || '秒';
	else
		sumTime := sumTime || '0秒';
	end if;
 return sumTime;
end; $$
language plpgsql;

select getMedianTime('2021-09-04 06:16:41'::TIMESTAMP,'2021-09-04 07:20:42'::TIMESTAMP)
来做第一个评论吧!~

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇