Thread: returning a record from a function?

returning a record from a function?

From
"Jon Obuchowski"
Date:
Hello,

I am interested in returning complete records (based on existing table
structures) from a function; I know how to deal with record variables
_within_ a function (using PL/PGSQL), but am uncertain as how to instruct
the general CREATE FUNCTION syntax to return a recordset (versus a single
value corresponding to a simple or complex dataype).

My attempts to do so look like the following code (with table "foo"):
    CREATE FUNCTION get_newest_foo ()    RETURNS foo AS '         SELECT *              FROM foo
WHERE foo_id =                         (select MAX(foo_id) FROM foo);    ' LANGUAGE 'sql';
 

this compiles and executes without warnings or exceptions - but this is the
result which I get:    jono=> select get_newest_foo ();    get_newest_foo    -----------        3393448    (1 row)

I thought that this might be an oid (corresponding to the selected record),
but testing showed that this is not the case.

Any help with this is appreciated; if I am simply overlooking relevant
documentation I'd appreciate being pointed towards the relevant page.

thanks,
Jon




Re: returning a record from a function?

From
Tom Lane
Date:
"Jon Obuchowski" <jon_obuchowski@terc.edu> writes:
> Any help with this is appreciated; if I am simply overlooking relevant
> documentation I'd appreciate being pointed towards the relevant page.

This is discussed under the not-too-obvious heading of "composite types"
at
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/xfunc-sql.html

The short answer is that your function is fine, it's what to do with
the result that's problematic at the moment.
        regards, tom lane


Re: returning a record from a function?

From
Roberto Mello
Date:
On Sun, Feb 24, 2002 at 10:33:40PM -0500, Jon Obuchowski wrote:
> 
> My attempts to do so look like the following code (with table "foo"):
> 
>      CREATE FUNCTION get_newest_foo ()
>      RETURNS foo AS '
>           SELECT *
>                FROM foo
>                           WHERE foo_id =
>                           (select MAX(foo_id) FROM foo);
>      ' LANGUAGE 'sql';

In the case above, wouldn't it be better if implemented as a VIEW?

CREATE VIEW newest_foo AS 
SELECT * FROM fooWHERE foo_id =        (select MAX(foo_id) FROM foo);

SELECT * FROM newest_foo;

> Any help with this is appreciated; if I am simply overlooking relevant
> documentation I'd appreciate being pointed towards the relevant page.

I think you're looking for the "setof" return type for SQL functions? See 
the CREATE FUNCTION reference.

Also, I just sent a reply about returning rows from non-SQL functions that
you might be interested in.

-Roberto

-- 
+----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU -
http://www.brasileiro.net/      http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer    
 
*/ \*                    <- Tribbles having a swordfight