Re: Return more than a record

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.