Date: Thu, 26 Feb 2004 21:16:03 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Kumar <sgnerd@yahoo.com.sg>
Cc: psql <pgsql-sql@postgresql.org>
Subject: Re: Return more than a record
In-Reply-To: <014901c3fced$6f6d0410$7502a8c0@hdsc.com>
Message-ID: <20040226211427.N71934@megazone.bigpanda.com>
References: <036901c3fc6f$a9159840$7502a8c0@hdsc.com>
	<20040226071613.Y54753@megazone.bigpanda.com>
	<014901c3fced$6f6d0410$7502a8c0@hdsc.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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.