What is the easiest way to return a recordset from a PostgreSQL stored procedure?

Question

I just have a table that contains a list of countries and their ISO country codes. I wrap the request in a stored procedure (aka function), for example:

CREATE OR REPLACE FUNCTION get_countries( ) RETURNS setof record AS $$ SELECT country_code, country_name FROM country_codes $$ LANGUAGE sql; 

The error I get is:

 ERROR: a column definition list is required for functions returning "record" 

I know that I can define TYPE and then scroll through a set of records, like a cursor, but IIRC has a better way to do this in newer versions of PostgreSQL (I use 8.4.3), but I pulled my hair while trying to remember.

---

Edit:

It works:

 CREATE OR REPLACE FUNCTION get_countries( ) RETURNS setof country_codes AS $$ SELECT country_code, country_name FROM country_codes $$ LANGUAGE sql; 

Note the "RETURNS setof [table name]". But it seems he is not the most flexible. It falls apart if I try to return a join of several tables.

Answer 1

You should be able to use output parameters, for example:

 CREATE OR REPLACE FUNCTION get_countries(country_code OUT text, country_name OUT text) RETURNS setof record AS $$ SELECT country_code, country_name FROM country_codes $$ LANGUAGE sql; 

Answer 2

There is also the possibility of using RETURNS TABLE(...) (as described in the PostgreSQL manual ), which I personally prefer:

 CREATE OR REPLACE FUNCTION get_countries() RETURNS TABLE( country_code text, country_name text ) AS $$ SELECT country_code, country_name FROM country_codes $$ LANGUAGE sql; 

This is actually the same as using SETOF tablename , but declares the structure of the inline table instead of referencing an existing object, therefore joins, etc. they will work.