Postgres error in creating a function

Question

I am trying to create a function in Postgres as follows:

Create function Samplefunc() 
returns resultdata( Tested int, Score int,
       Growth int) as
$BODY$
Select 
       Count(distinct student_id) Tested, 
       Cast(Avg(R.raw_score) as Int)  Score,
       Avg(R.growth) as Growth
from results R
where R.id=1 and test_id='ME04';
$BODY$
LANGUAGE sql;

But I am getting the following error:

ERROR:  syntax error at or near "int"
LINE 2: returns resultdata( NTested int, RawScore int,
                                    ^

********** Error **********

ERROR: syntax error at or near "int"
SQL state: 42601
Character: 59

Where am I going wrong?

Community · Accepted Answer · 2020-06-20 09:12:55Z

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.

hi..thanks for the answer. it worked great! how do i modify it to pass some arguments such that i dont have to define it like "where R.test_type_id=1 and test_id='201403MAME04';" anymore?

Luke · Accepted Answer · 2014-08-14 18:09:41Z

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;

Rahul · Accepted Answer · 2014-08-14 18:11:28Z

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

Lucas · Accepted Answer · 2014-08-14 17:50:23Z

0

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;

answered Aug 14, 2014 at 17:50

Lucas

1,53613 silver badges21 bronze badges

Collectives™ on Stack Overflow

Postgres error in creating a function

4 Answers 4

Add IN parameters (request in comment)

2 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Add IN parameters (request in comment)

2 Comments

Comments

Comments

Comments

Linked

Related