From sgnerd@yahoo.com.sg Thu Feb  26 13:51:16 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 90BFCD1DF4D
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 26 Feb 2004 13:51:16 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 22899-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 26 Feb 2004 09:51:15 -0400 (AST)
Received: from smtp014.mail.yahoo.com (smtp014.mail.yahoo.com
 [216.136.173.58])
	by svr1.postgresql.org (Postfix) with SMTP id 69528D1DD5F
	for <pgsql-sql@postgresql.org>; Thu, 26 Feb 2004 09:51:10 -0400 (AST)
Received: from unknown (HELO IVESIA67) (sgnerd@203.196.190.138 with login)
	by smtp014.mail.yahoo.com with SMTP; 26 Feb 2004 13:51:11 -0000
Message-ID: <036901c3fc6f$a9159840$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "psql" <pgsql-sql@postgresql.org>
Subject: Return more than a record
Date: Thu, 26 Feb 2004 19:21:37 +0530
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0366_01C3FC9D.C11BDE80"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/362
X-Sequence-Number: 17052

This is a multi-part message in MIME format.

------=_NextPart_000_0366_01C3FC9D.C11BDE80
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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=
=20
'
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 :=3D CAST(r.totalsalary * 1.75 as int8);
        else
            r.totalsalary :=3D CAST(r.totalsalary * 1.5 as int8);
        end if;

        if (r.totalsalary > 100000) then
            return next r.departmentid;
        end if;

    end loop;
    return;
end
'=20
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 th=
e calculated  date along with that row of the
table1. How to do that. Please shed some light.

------=_NextPart_000_0366_01C3FC9D.C11BDE80
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY style=3D"COLOR: #0000ff; FONT-FAMILY: Arial" bgColor=3D#ffffff>
<DIV><FONT size=3D2>Dear Friends,</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Postgres 7.3.4 on RH Linux 7.2.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Get the following from the groups</FONT></DIV><PRE clas=
s=3Dcode>create or replace function ExpensiveDepartments() returns setof ta=
ble1 as=20
'
declare
    r table1%rowtype;
begin
    for r in select departmentid, sum(salary) as totalsalary
        from GetEmployees() group by departmentid loop

        if (r.totalsalary &gt; 70000) then
            r.totalsalary :=3D CAST(r.totalsalary * 1.75 as int8);
        else
            r.totalsalary :=3D CAST(r.totalsalary * 1.5 as int8);
        end if;

        if (r.totalsalary &gt; 100000) then
            return next r.departmentid;
        end if;

    end loop;
    return;
end
'=20
language 'plpgsql';
</PRE>
<DIV class=3Dcode><FONT size=3D2>Is possible for me to return a variable al=
ong with=20
that 'return' statement? Because the table 'table1' contains some=20
date</FONT></DIV>
<DIV class=3Dcode><FONT size=3D2>column. I have done some calculation on th=
ose=20
columns and want to return the calculated  date along with that row of=20
the</FONT></DIV>
<DIV class=3Dcode><FONT size=3D2>table1. How to do that. Please shed some=
=20
light.</FONT></DIV>
<P></P></BODY></HTML>

------=_NextPart_000_0366_01C3FC9D.C11BDE80--




From sszabo@megazone.bigpanda.com Thu Feb  26 15:29:36 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 0000AD1BA9F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 26 Feb 2004 15:29:36 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 68392-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 26 Feb 2004 11:29:33 -0400 (AST)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id 9422FD1B97B
	for <pgsql-sql@postgresql.org>; Thu, 26 Feb 2004 11:29:29 -0400 (AST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id D9CB4357E6; Thu, 26 Feb 2004 07:29:27 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id D849A3570E; Thu, 26 Feb 2004 07:29:27 -0800 (PST)
Date: Thu, 26 Feb 2004 07:29:27 -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: <036901c3fc6f$a9159840$7502a8c0@hdsc.com>
Message-ID: <20040226071613.Y54753@megazone.bigpanda.com>
References: <036901c3fc6f$a9159840$7502a8c0@hdsc.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/367
X-Sequence-Number: 17057

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.



From sgnerd@yahoo.com.sg Fri Feb  27 04:51:55 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 8901BD1D8C8
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 27 Feb 2004 04:51:55 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 27340-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 27 Feb 2004 00:51:36 -0400 (AST)
Received: from smtp011.mail.yahoo.com (smtp011.mail.yahoo.com
 [216.136.173.31])
	by svr1.postgresql.org (Postfix) with SMTP id E5AEAD1D132
	for <pgsql-sql@postgresql.org>; Fri, 27 Feb 2004 00:51:34 -0400 (AST)
Received: from unknown (HELO IVESIA67) (sgnerd@203.196.190.138 with login)
	by smtp011.mail.yahoo.com with SMTP; 27 Feb 2004 04:51:33 -0000
Message-ID: <014901c3fced$6f6d0410$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
Cc: "psql" <pgsql-sql@postgresql.org>
References: <036901c3fc6f$a9159840$7502a8c0@hdsc.com>
	<20040226071613.Y54753@megazone.bigpanda.com>
Subject: Re: Return more than a record
Date: Fri, 27 Feb 2004 10:21:31 +0530
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/370
X-Sequence-Number: 17060

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




From sszabo@megazone.bigpanda.com Fri Feb  27 05:16:07 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 58100D1DEB3
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 27 Feb 2004 05:16:07 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 23082-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 27 Feb 2004 01:16:05 -0400 (AST)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id 1494FD1D26F
	for <pgsql-sql@postgresql.org>; Fri, 27 Feb 2004 01:16:05 -0400 (AST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id E2B9E3575A; Thu, 26 Feb 2004 21:16:03 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id DF9EB35602; Thu, 26 Feb 2004 21:16:03 -0800 (PST)
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
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/371
X-Sequence-Number: 17061

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.