What's the easiest way to return a recordset from a PostgreSQL stored procedure?
plpgsql, postgresql, sql, stored-procedures
Solution
You should be able to use output parameters, like this:
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;
Problem
I simply have a table that contains a list of countries and their ISO country codes. I'm wrapping the query in a stored procedure (aka function) such as: ``` CREATE OR REPLACE FUNCTION get_countries( ) RETURNS setof record AS $$ SELECT country_code, country_name FROM country_codes $$ LANGUAGE sql; ``` The error I am getting is: ``` ERROR: a column definition list is required for functions returning "record" ``` I know that I can define a TYPE and then loop through the recordset like a cursor, but IIRC there's a better way to do this under newer versions of PostgreSQL (I'm using 8.4.3) but I'm pulling my hair out trying to remember. Edit: This 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 doesn't seem to be the most flexible. It falls apart if I attempt to return a join of several tables.