ERROR: return type mismatch in function declared to return

user3808157 Published at Dev

user3808157

Building on these tables:

create table f1_driver(
 code varchar(5) not null primary key,
 name varchar(10),
 surname varchar(20),
 dateofbirth date,
 debut integer,
 countryoforigin varchar(20),
 points integer
);

create table f1_results (
 drivercode varchar(5) not null references f1_driver,
 circuitcode varchar(5) not null references f1_circuit,
 racedate date,
 raceposition integer,
 grid integer,
 primary key (drivercode, circuitcode,  racedate)
);

I want to make a function that the user will give the circuitcode and the function will return the name and surname of the drivers that in this specific circuit the raceposition was better than the grid.

I write this:

CREATE FUNCTION get(character) RETURNS SETOF f1_driver AS
$$
SELECT  D.name, D.surname
FROM f1_driver D,f1_results R
WHERE R.circuitcode = $1
AND D.code=R.drivercode
AND R.raceposition<grid ;
$$ LANGUAGE SQL;

And I have this error:

ERROR:  return type mismatch in function declared to return f1_driver
DETAIL:  Final statement returns too few columns.
CONTEXT:  SQL function "get"

Erwin Brandstetter

The row type f1_driver does not match what you actually return. Use RETURNS TABLE to provide a matching declaration:

CREATE FUNCTION f_get_drivers(varchar)
  RETURNS TABLE(name varchar, surname varchar) AS
$func$
SELECT D.name, D.surname
FROM   f1_driver D
JOIN   f1_results R ON R.drivercode = D.code
WHERE  R.circuitcode = $1
AND    R.raceposition < R.grid;
$func$ LANGUAGE sql;

Major points

The declared return type in RETURNS must match what's actually returned.
~~character~~ was the wrong data type for your input parameter. Use text or varchar. Details:
- Any downsides of using data type "text" for storing strings?
- Compare varchar with char
Use an explicit JOIN doing the same. That's easier to read and maintain.