CODEWITHSUNDEEP
sqldatabasepostgresqlreturn-type
user3808157
user3808157

Reputation: 33

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"

Upvotes: 2

Views: 16455

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656794

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)
  LANGUAGE sql 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$;

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. See:

Use an explicit JOIN doing the same. That's easier to read and maintain.

Upvotes: 4

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

I got the same error below:

ERROR: return type mismatch in function declared to return integer DETAIL: Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING. CONTEXT: SQL function "my_func"

Because I used START TRANSACTION; and COMMIT; to create a transaction in the SQL function as shown below:

CREATE FUNCTION my_func() RETURNS INTEGER AS $$
START TRANSACTION; -- Here
  SELECT 2;
COMMIT; -- Here
$$ LANGUAGE SQL;

So instead, I used BEGIN ATOMIC ... END statement as shown below, then the error was solved. *BEGIN ATOMIC ... END statement works only for a SQL function but not for a PL/pgSQL function according to the doc and you can unset LANGUAGE SQL when using BEGIN ATOMIC ... END statement but it is still a SQL function and the function in PostgreSQL is already in a single transaction by default so I don't need to create a transaction in them:

CREATE FUNCTION my_func() RETURNS INTEGER LANGUAGE SQL
BEGIN ATOMIC
  SELECT 2;
END;

Or:

CREATE FUNCTION my_func() RETURNS INTEGER /* LANGUAGE SQL */
BEGIN ATOMIC
  SELECT 2;
END;

Upvotes: 0

Related Questions