Re: Return more than a record - Mailing list pgsql-sql

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
DECLAREp_cal_date                           ALIAS FOR $1;rec_activity
activities%ROWTYPE;v_activity_start_date          DATE;
 

BEGINFOR rec_activity IN SELECT *   FROM activities  WHERE
DATE(activity_start_time) <= p_cal_dateLOOP         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
RETURNnext 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@megazone.bigpanda.com>
To: "Kumar" <sgnerd@yahoo.com.sg>
Cc: "psql" <pgsql-sql@postgresql.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