Message-ID: <014901c3fced$6f6d0410$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
Cc: "psql" <pgsql-sql@postgresql.org>
References: <036901c3fc6f$a9159840$7502a8c0@hdsc.com>
	<20040226071613.Y54753@megazone.bigpanda.com>
Subject: Re: Return more than a record
Date: Fri, 27 Feb 2004 10:21:31 +0530
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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@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