pgsql中wtih语句和存储过程

with

with 后做临时表，可以查询

WITH AS短语，也叫做子查询部分，定义一个SQL片断后，该SQL片断可以被整个SQL语句所用到。有的时候，with as是为了提高SQL语句的可读性，减少嵌套冗余。

with as的用法可以通俗点讲是，讲需要频繁执行的slq片段加个别名放到全局中，后面直接调用就可以，这样减少调用次数，优化执行效率。

with cte1 as
 ( select * from table1 where name like 'abc%'
 ),
cte2 as
(select * from table2 where id > 20
),
 cte3 as
 (select * from table3 where price < 100
 )
 select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id

存储过程

create or replace function functionaa(relevant_employee_id integer,relevant_period_id integer)
	--定义函数名和接收参数类型
returns TABLE (value double precision) as $$
--定义返回类型
declare value double precision;   --定义变量数据类型
begin
	-- 返回的结果，可以使用其它数据类型
 RETURN QUERY WITH attendance_data_wizard AS (
	SELECT
		adwl.float12 absenteeism,
		adwl.need_attendance_days,
		adwl.boolean5 continuity_compassionate_leave_than_30,
		adwl.boolean6 continuity_compassionate_leave_than_90,
		adwl.boolean3 continuity_sick_than_183,
		adwl.employee_id,
		adw.period_id,
	( CASE WHEN adwl.float12 < adwl.salary_days and adwl.float12 < adwl.need_attendance_days THEN adwl.salary_days ELSE adwl.need_attendance_days END ) base_attendance_days 
FROM
			dy_salary_monthly_attendance_data adw,
			dy_salary_monthly_attendance_data_line adwl
			LEFT JOIN hr_employee he ON he.ID = adwl.employee_id 
		WHERE
			adwl.attendance_line_id = adw.ID 
			AND adw.STATE = 'frozen' 
		) ,
-- 		
jcxjx_amount_wizard AS (
		SELECT
                             COALESCE( dsii.amount, 0 ) basic_performance,
                             he.ID employee_id,
                             dsp.id period_id
                        FROM
                            dy_salary_item_info dsii,
                            dy_salary_item dsi,
                            dy_salary_period dsp,
                                   hr_employee he
                        WHERE 1=1
                        AND dsi.ID = dsii.salary_item_id
                        AND dsii.employee_id = he.id
                        AND dsi.code = 'jcxjx'
                        AND dsp.id = 3
                        AND dsii.start_date <= dsp.start_of_period AND (dsii.end_date IS NULL or dsii.end_date >= dsp.end_of_period)
		) 
 select amount from jcxjx_deduction_amount_wizard ;
 	END;
	$$ LANGUAGE plpgsql;
	
	调用函数
	select functionaa(3443,3)
	删除函数
	DROP FUNCTION compute_attendance_deduction(integer,integer)
	

教程：https://www.modb.pro/db/13908