Dear Friends, I am using the record type as follows in my code. CREATE OR REPLACE FUNCTION fn_daily_calendar(date) RETURNS SETOF activities AS DECLARE p_cal_date ALIAS FOR $1; rec_activity activities%ROWTYPE; v_activity_start_date DATE;
BEGIN FOR rec_activity IN SELECT * FROM activities WHERE DATE(activity_start_time) <= p_cal_date LOOP v_activity_start_date := rec_activity.activity_start_time::DATE; IF rec_activity.daily_gap IS NOT NULL AND rec_activity.recurrence_end_time IS NULL THEN LOOP v_activity_start_date := v_activity_start_date + rec_activity.daily_gap; IF v_activity_start_date = p_cal_date THEN RETURN next rec_activity; END IF; EXIT WHEN v_activity_start_date > p_cal_date + (1 month')::INTERVAL; END LOOP; END IF; END LOOP; RETURN; END; See I am fetching the activity_start_time from the record, then assigning to variable and do some calculations on the variable. Now I want to return the value of v_activity_start_date for every row in activities table. How could I achieve this. Please shed some light. Thanks Kumar ----- Original Message ----- From: "Stephan Szabo" <[EMAIL PROTECTED]> To: "Kumar" <[EMAIL PROTECTED]> Cc: "psql" <[EMAIL PROTECTED]> Sent: Thursday, February 26, 2004 8:59 PM Subject: Re: [SQL] Return more than a record > On Thu, 26 Feb 2004, Kumar wrote: > > > Get the following from the groups > > create or replace function ExpensiveDepartments() returns setof table1 as > > Note that at least the example with this name in the SetReturningFunctions > guide seems to use setof int as the return type. > > > ' > > declare > > r table1%rowtype; > > begin > > for r in select departmentid, sum(salary) as totalsalary > > from GetEmployees() group by departmentid loop > > > > if (r.totalsalary > 70000) then > > r.totalsalary := CAST(r.totalsalary * 1.75 as int8); > > else > > r.totalsalary := CAST(r.totalsalary * 1.5 as int8); > > end if; > > > > if (r.totalsalary > 100000) then > > return next r.departmentid; > > end if; > > > > end loop; > > return; > > end > > ' > > language 'plpgsql'; > > Is possible for me to return a variable along with that 'return' statement? Because the table 'table1' contains some date > > column. I have done some calculation on those columns and want to return the calculated date along with that row of the > > table1. How to do that. Please shed some light. > > If you want to return a composite type, you can make another rowtype > variable that has the set of columns (and their types) to return, fill in > the values to return and then do return next with that variable. > > For example, to say return departmentid, sum(salary) and the computed > "totalsalary" from the above, you might do something like (untested so > there might be syntactic errors) > > create type holder as (departmentid int, totalsalary int8); > create type holder2 as (departmentid int, sumsalary int8, totalsalary > int8); > > create or replace function ExpensiveDepartments() returns setof holder2 as > ' > declare > r holder%rowtype; > s holder2%rowtype; > begin > for r in select departmentid, sum(salary) as totalsalary > from GetEmployees() group by departmentid loop > > s.departmentid := r.departmentid; > s.sumsalary := r.totalsalary; > > if (r.totalsalary > 70000) then > s.totalsalary := CAST(r.totalsalary * 1.75 as int8); > else > s.totalsalary := CAST(r.totalsalary * 1.5 as int8); > end if; > > if (s.totalsalary > 100000) then > return next s; > end if; > > end loop; > return; > end > ' > language 'plpgsql'; > > > The important differences here are that we've got a new rowtype variable s > of the return type and that we fill s with the values from r (the select) > plus the calculation that we're doing (rather than before where we just > overwrote the values in r.totalsalary) and then we return next s rather > than a particular field. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match