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
Date: Thu, 26 Feb 2004 21:16:03 -0800 (PST)
Message-ID: <20040226211427.N71934@megazone.bigpanda.com> (raw)
In-Reply-To: <014901c3fced$6f6d0410$7502a8c0@hdsc.com>
References: <036901c3fc6f$a9159840$7502a8c0@hdsc.com>
<20040226071613.Y54753@megazone.bigpanda.com>
<014901c3fced$6f6d0410$7502a8c0@hdsc.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com, sgnerd@yahoo.com.sg
Subject: Re: Return more than a record
In-Reply-To: <20040226211427.N71934@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox