How can this postgresql query return as table?

Question

I am trying to call modules which in specific course but it returns as error: more than one row returned by a subquery used as an expression

Query:

CREATE OR REPLACE FUNCTION course_modules(_courseID integer)

RETURNS SETOF modules AS

$$

BEGIN   

RETURN QUERY SELECT * FROM modules WHERE mod_id =(SELECT module_id from coursemodules WHERE course_id = _courseID);

END

 $$

LANGUAGE 'plpgsql';

coursemodule table

 CREATE TABLE coursemodules(
 course_id integer references courses (id) ON DELETE CASCADE,
 module_id integer references modules (mod_id) ON DELETE CASCADE
 );

Modules Table

 CREATE TABLE modules(
 documents text   NOT NULL,
 mod_id serial primary key,
 content text   NOT NULL,
 title varchar(50)  NOT NULL
 );

Course Table

 CREATE TABLE courses(
 finishDate Date,
 description text  NOT NULL,
 duration varchar(50)   NOT NULL,
 startDate Date,
 id serial primary key,
 courseName varchar(50)   NOT NULL
 );

Answer 1

There is no restriction in the coursemodule table that a course could only have one module. Because of that the SELECT module_id from coursemodules WHERE course_id = _courseID subquery could return multiple lines.

If you change mod_id = (SELECT module_id from coursemodules WHERE course_id = _courseID)

to

mod_id IN (SELECT module_id from coursemodules WHERE course_id = _courseID).

It should work. Otherwise you have to add constraints to the coursemodule table.

Answer 2

This is a simple SQL syntax error. You're using

WHERE mod_id =

But you may have more than one row returning from the subquery. User IN:

WHERE mod_id IN