Skip site navigation (1) Skip section navigation (2)

Select Into help

From: brian stapel <brians_224(at)hotmail(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Select Into help
Date: 2007-01-18 16:08:12
Message-ID: BAY107-W6280D23D27431FB5182A8A1AA0@phx.gbl (view raw or flat)
Thread:
Lists: 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 ttlFROM rp_emp_role_payWHERE 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.
www.live.com/?addtemplate=holiday

Responses

pgsql-novice by date

Next:From: Leticia LarrosaDate: 2007-01-18 21:47:57
Subject: Send all postgres messages to a specific folder
Previous:From: Ron ArtsDate: 2007-01-18 11:03:52
Subject: Re: only best matches with ilike or regex matching

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group