Обсуждение: Select Into help

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!

On Jan 18, 2007, at 11:08 AM, brian stapel wrote:

> Below is my current version of the function.  It compiles and
> executes.  How ever, it returns only one record - the last select
> into statement


This is because you only call return next one time. You need to call
it multiple times in a loop if you expect to return a set of records.
This article is somewhat old, but has some detailed examples:

http://www.postgresql.org/docs/techdocs.17






John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL