Re: Return more than a record

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" <sszabo(at)megazone(dot)bigpanda(dot)com>
To: "Kumar" <sgnerd(at)yahoo(dot)com(dot)sg>
Cc: "psql" <pgsql-sql(at)postgresql(dot)org>
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