I am using the record type as follows in my code.
CREATE OR REPLACE FUNCTION fn_daily_calendar(date)
RETURNS SETOF activities AS
p_cal_date ALIAS FOR $1;
FOR rec_activity IN SELECT * FROM activities WHERE
DATE(activity_start_time) <= p_cal_date
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
v_activity_start_date := v_activity_start_date +
IF v_activity_start_date = p_cal_date THEN
RETURN next rec_activity;
v_activity_start_date > p_cal_date + (1
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.
----- Original Message -----
From: "Stephan Szabo" <sszabo(at)megazone(dot)bigpanda(dot)com>
To: "Kumar" <sgnerd(at)yahoo(dot)com(dot)sg>
Cc: "psql" <pgsql-sql(at)postgresql(dot)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
> 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
> create or replace function ExpensiveDepartments() returns setof holder2 as
> r holder%rowtype;
> s holder2%rowtype;
> 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);
> s.totalsalary := CAST(r.totalsalary * 1.5 as int8);
> end if;
> if (s.totalsalary > 100000) then
> return next s;
> end if;
> end loop;
> 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?
In response to
pgsql-sql by date
|Next:||From: Stephan Szabo||Date: 2004-02-27 05:16:03|
|Subject: Re: Return more than a record|
|Previous:||From: Michael Chaney||Date: 2004-02-26 16:37:02|
|Subject: Re: Last day of month|