CODEWITHSUNDEEP
sqlpostgresql
mooncow
mooncow

Reputation: 413

Argument of AND must not return a set when used with BETWEEN in a check constraint

I am encountering the error "argument of AND must not return a set" from the AND in the check constraint of the below table.

CREATE TABLE loan (
   id SERIAL PRIMARY KEY,
   copy_id INTEGER REFERENCES media_copies (copy_id),
   account_id INT REFERENCES account (id),
   loan_date DATE NOT NULL,
   expiry_date DATE NOT NULL,
   return_date DATE,

   CONSTRAINT max_student_concurrent_loans CHECK(
      CurrentStudentLoansCount() BETWEEN 1 AND 7 
   )
);

The implementation of CurrentStudentLoansCount() is shown below.

CREATE OR REPLACE FUNCTION CurrentStudentLoansCount()
RETURNS TABLE(accid BIGINT) AS $$
BEGIN
   RETURN QUERY
   SELECT COUNT(*)
   FROM loan
   WHERE account_id IN (SELECT id FROM student)
      AND return_date IS NULL
   GROUP BY account_id;
END
$$ LANGUAGE PLPGSQL;

Why am I running into this error and how can I work around it?

For context, the below figure displays my database schema. database schema

Upvotes: 1

Views: 1008

Answers (1)

user330315
user330315

Reputation:

Your function returns a table with multiple rows so you can't use that for a BETWEEN condition. Presumably you just want that value for the account_id of that row of the table (not for all account_ids).

So change the function to return a single value by passing it the account_id. And you also don't need PL/pgSQL for this:

CREATE OR REPLACE FUNCTION currentstudentloanscount(p_account_id integer)
  RETURNS bigint
as
$$
  SELECT COUNT(*)
  FROM loan
  WHERE account_id = p_account_id
    AND return_date IS NULL;
$$ LANGUAGE sql;

And change your table definition to:

CREATE TABLE loan (
   id SERIAL PRIMARY KEY,
   copy_id INTEGER REFERENCES media_copies (copy_id),
   account_id INT REFERENCES account (id),
   loan_date DATE NOT NULL,
   expiry_date DATE NOT NULL,
   return_date DATE,

   CONSTRAINT max_student_concurrent_loans 
       CHECK(currentstudentloanscount(account_id) BETWEEN 1 AND 7)
);

Upvotes: 3

Related Questions