Обсуждение: Return more than a record

create or replace function ExpensiveDepartments() returns setof table1 as 
'
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';

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.

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

On Fri, 27 Feb 2004, Kumar wrote:

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

One way would be: make a composite type that contains the columns of
activity + v_activity_start_date, make the function return that, add a
declared variable of that type, set the fields of that new variable to the
fields from rec_activity and the value of v_activity_start_date, return
next that variable.