Select Into help
От | brian stapel |
---|---|
Тема | Select Into help |
Дата | |
Msg-id | BAY107-W6280D23D27431FB5182A8A1AA0@phx.gbl обсуждение исходный текст |
Ответы |
Re: Select Into help
(John DeSoi <desoi@pgedit.com>)
|
Список | pgsql-novice |
Thanks for your time! I've asked a number of questions recently - thanks to all who have responded. Your responses have been very helpful and appreciated. :)
I'm writing a function that generates summary information. I want to return the summary information in a single record set. How do you build a result set using variables, and then return the variables is a record set. (sorry if I'm not explainit it well)
Below is my current version of the function. It compiles and executes. How ever, it returns only one record - the last select into statement
CREATE OR REPLACE FUNCTION "public"."fnc_sg_get_emp_sg_total_type" (int_emp_id integer, int_grid_id integer, int_rev integer, int_lvl_cnt integer, dt_hiredate date) RETURNS SETOF "public"."tp_sg_get_emp_sg_total_type" AS
$body$
DECLARE
int_lvl_cntr integer;
nmr_lvl_total numeric;
int_lvl_type integer;
nmr_nrml_total numeric;
nmr_ct_total numeric;
nmr_spclty_total numeric;
nmr_sg_total numeric;
nmr_bs_wage numeric;
nmr_rp_total numeric;
rec public.tp_sg_get_emp_sg_total_type;
BEGIN
int_lvl_cntr = 1;
nmr_sg_total = 0;
nmr_nrml_total = 0;
nmr_ct_total = 0;
nmr_spclty_total = 0;
nmr_bs_wage = wg_amt from sg_bs_wg where sg_bs_wg.actv = true;
/* get level totals by skill type */
WHILE int_lvl_cntr <= int_lvl_cnt loop
nmr_lvl_total = * from fnc_sg_get_lvl_total(int_emp_id, int_grid_id, int_rev, int_lvl_cntr, dt_hiredate) ;
if nmr_lvl_total is null then
nmr_lvl_total = 0;
end if;
int_lvl_type = distinct skill_type from sg_emp_skllgrd
where emp_id = int_emp_id
AND grid_id=int_grid_id
AND rev = int_rev
AND lvl = int_lvl_cntr;
if int_lvl_type=1 THEN
nmr_nrml_total = nmr_nrml_total + nmr_lvl_total;
elseif int_lvl_type=2 THEN
nmr_ct_total = nmr_ct_total + nmr_lvl_total;
ELSE
nmr_spclty_total = nmr_spclty_total + nmr_lvl_total;
END if;
int_lvl_cntr = int_lvl_cntr + 1;
end loop;
/* get role pay total */
nmr_rp_total = Sum(rp_emp_role_pay.rwrd_amt) AS ttl
FROM rp_emp_role_pay
WHERE rp_emp_role_pay.emp_id=int_emp_id;
if nmr_rp_total is null then
nmr_rp_total = 0;
end if;
nmr_sg_total = nmr_bs_wage + nmr_nrml_total + nmr_ct_total + nmr_spclty_total + nmr_rp_total;
select into rec 'Base Wage', nmr_bs_wage;
select into rec 'Skill Block', nmr_nrml_total;
select into rec 'Cross Train', nmr_ct_total;
select into rec 'Specialy', nmr_spclty_total;
select into rec 'Role Pay', nmr_rp_total;
return NEXT rec;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Get the Live.com Holiday Page for recipes, gift-giving ideas, and more. Check it out!
I'm writing a function that generates summary information. I want to return the summary information in a single record set. How do you build a result set using variables, and then return the variables is a record set. (sorry if I'm not explainit it well)
Below is my current version of the function. It compiles and executes. How ever, it returns only one record - the last select into statement
CREATE OR REPLACE FUNCTION "public"."fnc_sg_get_emp_sg_total_type" (int_emp_id integer, int_grid_id integer, int_rev integer, int_lvl_cnt integer, dt_hiredate date) RETURNS SETOF "public"."tp_sg_get_emp_sg_total_type" AS
$body$
DECLARE
int_lvl_cntr integer;
nmr_lvl_total numeric;
int_lvl_type integer;
nmr_nrml_total numeric;
nmr_ct_total numeric;
nmr_spclty_total numeric;
nmr_sg_total numeric;
nmr_bs_wage numeric;
nmr_rp_total numeric;
rec public.tp_sg_get_emp_sg_total_type;
BEGIN
int_lvl_cntr = 1;
nmr_sg_total = 0;
nmr_nrml_total = 0;
nmr_ct_total = 0;
nmr_spclty_total = 0;
nmr_bs_wage = wg_amt from sg_bs_wg where sg_bs_wg.actv = true;
/* get level totals by skill type */
WHILE int_lvl_cntr <= int_lvl_cnt loop
nmr_lvl_total = * from fnc_sg_get_lvl_total(int_emp_id, int_grid_id, int_rev, int_lvl_cntr, dt_hiredate) ;
if nmr_lvl_total is null then
nmr_lvl_total = 0;
end if;
int_lvl_type = distinct skill_type from sg_emp_skllgrd
where emp_id = int_emp_id
AND grid_id=int_grid_id
AND rev = int_rev
AND lvl = int_lvl_cntr;
if int_lvl_type=1 THEN
nmr_nrml_total = nmr_nrml_total + nmr_lvl_total;
elseif int_lvl_type=2 THEN
nmr_ct_total = nmr_ct_total + nmr_lvl_total;
ELSE
nmr_spclty_total = nmr_spclty_total + nmr_lvl_total;
END if;
int_lvl_cntr = int_lvl_cntr + 1;
end loop;
/* get role pay total */
nmr_rp_total = Sum(rp_emp_role_pay.rwrd_amt) AS ttl
FROM rp_emp_role_pay
WHERE rp_emp_role_pay.emp_id=int_emp_id;
if nmr_rp_total is null then
nmr_rp_total = 0;
end if;
nmr_sg_total = nmr_bs_wage + nmr_nrml_total + nmr_ct_total + nmr_spclty_total + nmr_rp_total;
select into rec 'Base Wage', nmr_bs_wage;
select into rec 'Skill Block', nmr_nrml_total;
select into rec 'Cross Train', nmr_ct_total;
select into rec 'Specialy', nmr_spclty_total;
select into rec 'Role Pay', nmr_rp_total;
return NEXT rec;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Get the Live.com Holiday Page for recipes, gift-giving ideas, and more. Check it out!
В списке pgsql-novice по дате отправления: