Thread: returning a record from a function?

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

"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

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