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.