Postgres error in creating a function

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.

Try not specifying the details of the composite return type. However, I think the return structure must exist first.

Create table resultdata (NTested int, RawScore int, Growth int);
Create function Samplefunc() returns resultdata as
$BODY$
Select 
 Count(distinct student_id) as NTested, 
 Cast(Avg(R.raw_score) as Int) as RawScore,
 Avg(R.growth) as Growth
from reports_results R
where R.test_type_id=1 and test_id='201403MAME04';
$BODY$
LANGUAGE sql;

Or try explicitly returning a table:

Create function Samplefunc() 
 returns Table (NTested int, RawScore int, Growth int) as
$BODY$
Select 
 Count(distinct student_id) as NTested, 
 Cast(Avg(R.raw_score) as Int) as RawScore,
 Avg(R.growth) as Growth
from reports_results R
where R.test_type_id=1 and test_id='201403MAME04';
$BODY$
LANGUAGE sql;

I think you can also return a set of records using output parameters:

Create function Samplefunc(OUT NTested int, OUT RawScore int, OUT Growth int) 
 returns SetOf Record as
$BODY$
Select 
 Count(distinct student_id) as NTested, 
 Cast(Avg(R.raw_score) as Int) as RawScore,
 Avg(R.growth) as Growth
from reports_results R
where R.test_type_id=1 and test_id='201403MAME04';
$BODY$
LANGUAGE sql;

You are trying to return a record containing multiple output parameters. You should do like:

Create function Samplefunc(out NTested int, out RawScore int, out Growth int) as
$BODY$
Select 
 Count(distinct student_id) NTested, 
 Cast(Avg(R.raw_score) as Int) RawScore,
 Avg(R.growth) as Growth
from reports_results R
where R.test_type_id=1 and test_id='201403MAME04';
$BODY$
LANGUAGE sql;

You can do the same thing more verbosely with an explicitly named composite type; like

CREATE TYPE resultdata AS (NTested int, RawScore int, Growth int);
CREATE FUNCTION Samplefunc() RETURNS resultdata
 AS .......

Or using a TABLE function like

CREATE FUNCTION Samplefunc() RETURNS TABLE(NTested int, RawScore int, Growth int)
AS
......

See PostgreSQL Documentation for more information on the same

If you have not defined a type "resultset" as Luke rightly assumed, then try returning a table instead.

Create function Samplefunc() 
returns table( NTested int, RawScore int,
 Growth int) as
$BODY$
Select 
 Count(distinct student_id) NTested, 
 Cast(Avg(R.raw_score) as Int) RawScore,
 Avg(R.growth) as Growth
from reports_results R
where R.test_type_id=1 and test_id='201403MAME04';
$BODY$
LANGUAGE sql;

• sql
• postgresql
• user-defined-functions