Return to Revisions

CREATE FUNCTION samplefunc() 
 RETURNS TABLE(ntested int, rawscore int, growth int) AS
$func$
SELECT count(DISTINCT r.student_id) -- AS NTested
 ,avg(r.raw_score)::int -- AS RawScore
 ,avg(r.growth)::int -- AS Growth
FROM reports_results r
WHERE r.test_type_id = 1
AND r.test_id = '201403MAME04'
$func$ LANGUAGE sql;

• The clause to return a table is RETURNS TABLE.

• Carefully avoid conflicts between OUT parameters and column names. (I had such a conflict in my first draft). Table-qualify columns to disambiguate. All field names in RETURNS TABLE are effectively OUT parameters and visible inside the function (almost) everywhere.

Also:

• avg(growth) would result in a type mismatch with the declared return type int. You need to cast that, too. Using the short Postgres-specific syntax ::type, btw.
  Better yet: return numeric or a floating point number to preserve fractional digits in your avg numbers.

• column aliases are only visible inside the function. If you are not going to reference them inside the function, they are just documentation.

• What's with the capitalization? Unquoted identifiers are cast to lower case in Postgres automatically.

If the query is guaranteed to return a single row, you might want to combine OUT parameters with RETURNS record:

CREATE FUNCTION samplefunc(OUT ntested int, OUT rawscore int, OUT growth int) 
 RETURNS record AS ...

The subtle difference: This way you get a single row with NULL values if nothing is found, where the first form would return nothing / no row.

Add IN parameters (request in comment)

CREATE FUNCTION samplefunc(_test_type_id int, _test_id text) 
 RETURNS TABLE(ntested int, rawscore int, growth int) AS
$func$
SELECT count(DISTINCT r.student_id)
 ,avg(r.raw_score)::int
 ,avg(r.growth)::int
FROM reports_results r
WHERE r.test_type_id = 1ドル -- or: = _test_type_id in Postgres 9.2+
AND r.test_id = 2ドル -- or: = _test_id
$func$ LANGUAGE sql;

Many related answers here on SO with more code examples. Like:

• PostgreSQL return a function with a Custom Data Type

Try a search.