pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReturn more than a record
4+ messages / 2 participants
[nested] [flat]
* Return more than a record
@ 2004-02-26 13:51 Kumar <sgnerd@yahoo.com.sg>
0 siblings, 1 reply; 4+ messages in thread
From: Kumar @ 2004-02-26 13:51 UTC (permalink / raw)
To: pgsql-sql
Dear Friends,
Postgres 7.3.4 on RH Linux 7.2.
Get the following from the groups
create or replace function ExpensiveDepartments() returns setof table1 as
'
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Return more than a record
@ 2004-02-26 15:29 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Kumar <sgnerd@yahoo.com.sg>
0 siblings, 1 reply; 4+ messages in thread
From: Stephan Szabo @ 2004-02-26 15:29 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; +Cc: pgsql-sql
On Thu, 26 Feb 2004, Kumar wrote:
> Get the following from the groups
> create or replace function ExpensiveDepartments() returns setof table1 as
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
int8);
create or replace function ExpensiveDepartments() returns setof holder2 as
'
declare
r holder%rowtype;
s holder2%rowtype;
begin
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);
else
s.totalsalary := CAST(r.totalsalary * 1.5 as int8);
end if;
if (s.totalsalary > 100000) then
return next s;
end if;
end loop;
return;
end
'
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Return more than a record
@ 2004-02-27 04:51 Kumar <sgnerd@yahoo.com.sg>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Kumar @ 2004-02-27 04:51 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
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.
How could I achieve this.
Please shed some light.
Thanks
Kumar
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: "Kumar" <sgnerd@yahoo.com.sg>
Cc: "psql" <pgsql-sql@postgresql.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
as
>
> 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
> int8);
>
> create or replace function ExpensiveDepartments() returns setof holder2 as
> '
> declare
> r holder%rowtype;
> s holder2%rowtype;
> begin
> 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);
> else
> s.totalsalary := CAST(r.totalsalary * 1.5 as int8);
> end if;
>
> if (s.totalsalary > 100000) then
> return next s;
> end if;
>
> end loop;
> return;
> end
> '
> 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?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Return more than a record
@ 2004-02-27 05:16 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Kumar <sgnerd@yahoo.com.sg>
0 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2004-02-27 05:16 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-02-27 05:16 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-26 13:51 Return more than a record Kumar <sgnerd@yahoo.com.sg>
2004-02-26 15:29 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-27 04:51 ` Kumar <sgnerd@yahoo.com.sg>
2004-02-27 05:16 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
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