X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id AE6CD47580B
	for <pgsql-sql@postgresql.org>; Fri, 28 Mar 2003 19:32:14 -0500 (EST)
Received: from admissionstatements.com (admissionstatements.com
	[128.121.224.177])
	by postgresql.org (Postfix) with ESMTP id CC093475458
	for <pgsql-sql@postgresql.org>; Fri, 28 Mar 2003 19:32:10 -0500 (EST)
Received: from racistnames.com (ut162-157.dsl.relia.net [207.173.157.162]) by
	admissionstatements.com (8.12.6) id h2T0W4vu045034;
	Fri, 28 Mar 2003 17:32:08 -0700 (MST)
Message-ID: <3E84E983.10206@racistnames.com>
Date: Fri, 28 Mar 2003 17:32:03 -0700
From: "Jordan S. Jones" <list@racistnames.com>
Reply-To: list@racistnames.com
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.3) Gecko/20030312
X-Accept-Language: en-us, en, ja
MIME-Version: 1.0
To: Franco Bruno Borghesi <franco@akyasociados.com.ar>,
	pgsql-sql@postgresql.org
Subject: Re: returning composite types.
References: <200303282020.25997.franco@akyasociados.com.ar>
In-Reply-To: <200303282020.25997.franco@akyasociados.com.ar>
Content-Type: multipart/alternative;
	boundary="------------070803070007030002080508"
X-Spam-Status: No, hits=-34.0 required=5.0
	tests=BAYES_01,EMAIL_ATTRIBUTION,HTML_10_20,IN_REP_TO,REFERENCES,
	REPLY_WITH_QUOTES,UPPERCASE_25_50,USER_AGENT_MOZILLA_UA
	autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200303/634
X-Sequence-Number: 12672

This is a multi-part message in MIME format.
--------------070803070007030002080508
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Here is how I have been doing mine:

    CREATE FUNCTION "myFunction" () RETURNS SETOF mytype
    AS
    '
    DECLARE
        r      mytype%ROWTYPE;
    BEGIN
        FOR r IN [SELECT STATEMENT]
        LOOP
           RETURN NEXT r;
        END LOOP;
        RETURN;
    END;
    '
    LANGUAGE 'plpgsql';

Hope this Helps..

Jordan S. Jones


Franco Bruno Borghesi wrote:

>Hi guys.
>
>I'm working with functions in my database, using plpgsql, but I reached a 
>point where I realize I'm missing a concept: how do I return composite types 
>from a function? I'll give you an example:
>
>CREATE TYPE mytype AS(
>   val1 INTEGER,
>   val2 INTEGER,
>   val3 INTEGER,
>   val4 INTEGER   
>);
>
>If I want my function to return a "mytype" type, should I declare it as:
>CREATE FUNCTION myFunction() RETURNS mytype AS ...
>or maybe
>CREATE FUNCTION myFunction() RETURNS SETOF mytype AS ...
>
>and in any case, inside the function, how should I declare the variable 
>holding the return value?:
>
>DECLARE
>   result mytype;
>BEGIN
>...
>   RETURN result;   
>END;   
>
>or maybe      
>
>DECLARE
>   result mytype%ROWTYPE;
>BEGIN
>...
>   RETURN result;   
>END;   
>
>I've read the documentation and the examples in it, but I still don't 
>understand what the right way is. If you could give an example of a function 
>filling "mytipe" and returning it, it would really help me.
>
>Thanks in advance.
>   
>

--------------070803070007030002080508
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
  <title></title>
</head>
<body>
Here is how I have been doing mine:<br>
<br>
<blockquote><tt>CREATE FUNCTION "myFunction" () RETURNS SETOF mytype<br>
AS<br>
'<br>
DECLARE<br>
&nbsp;&nbsp;&nbsp; r&nbsp;&nbsp;  &nbsp;&nbsp; mytype%ROWTYPE;<br>
BEGIN<br>
&nbsp;&nbsp;&nbsp; FOR r IN [SELECT STATEMENT]<br>
&nbsp;&nbsp;&nbsp; LOOP<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;  RETURN NEXT r;<br>
&nbsp;&nbsp;&nbsp; END LOOP;<br>
&nbsp;&nbsp;&nbsp; RETURN;<br>
END;<br>
'<br>
LANGUAGE 'plpgsql';<br>
  </tt></blockquote>
Hope this Helps..<br>
<br>
Jordan S. Jones<br>
<br>
<br>
Franco Bruno Borghesi wrote:<br>
<blockquote type="cite"
 cite="mid200303282020.25997.franco@akyasociados.com.ar">
  <pre wrap="">Hi guys.

I'm working with functions in my database, using plpgsql, but I reached a 
point where I realize I'm missing a concept: how do I return composite types 
from a function? I'll give you an example:

CREATE TYPE mytype AS(
   val1 INTEGER,
   val2 INTEGER,
   val3 INTEGER,
   val4 INTEGER   
);

If I want my function to return a "mytype" type, should I declare it as:
CREATE FUNCTION myFunction() RETURNS mytype AS ...
or maybe
CREATE FUNCTION myFunction() RETURNS SETOF mytype AS ...

and in any case, inside the function, how should I declare the variable 
holding the return value?:

DECLARE
   result mytype;
BEGIN
...
   RETURN result;   
END;   

or maybe      

DECLARE
   result mytype%ROWTYPE;
BEGIN
...
   RETURN result;   
END;   

I've read the documentation and the examples in it, but I still don't 
understand what the right way is. If you could give an example of a function 
filling "mytipe" and returning it, it would really help me.

Thanks in advance.
   </pre>
</blockquote>
</body>
</html>

--------------070803070007030002080508--