Search code examples
sqldatabasepostgresqlreturn-type

ERROR: return type mismatch in function declared to return

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"

Solution

  • 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