CODEWITHSUNDEEP
sqlpostgresql
valt
valt

Reputation: 29

How this function can return a boolean in SQL with extract

I create a function to return true if I can't insert a note for a student if he is minor.

CREATE OR REPLACE FUNCTION mineur (note REAL, numeutu INT)
RETURNS BOOLEAN AS $$
SELECT $1 IS NULL OR 
SELECT * FROM studiant 
WHERE student.numetu = $2 AND 
      extract(YEAR FROM AGE(birthdate)) > 18;
       $$ LANGUAGE SQL;

I write this function but postgresql but I get:

ERROR: Syntax error near SELECT (About the select * from etudiant)

Upvotes: 0

Views: 157

Answers (1)

Gary - Stand with Ukraine
Gary - Stand with Ukraine

Reputation: 7561

The problem is you are using a second select on the OR, but SELECTs aren't boolean expressions which is confusing it a bit.

SELECT $1 IS NULL OR 
SELECT * FROM studiant

The first bit is confusing. I'm assuming that if the note is null you want to return TRUE. So a query like:

CREATE OR REPLACE FUNCTION mineur (note REAL, numeutu INT)
RETURNS BOOLEAN AS 
$$
SELECT 
  ($1 IS NULL) OR 
  EXISTS(SELECT * FROM studiant 
    WHERE studiant.numetu = $2 AND 
          extract(YEAR FROM AGE(birthdate)) > 18)
$$ 
LANGUAGE SQL;

Here I'm using EXISTS to determine if a row exists that matches the criteria.

The effects of this are:

  • return true if "note" is null.
  • return true if a record exists for a studiant record where the birthdate is more than 18 years ago.
  • return false otherwise.

There is a sqlfiddle at http://sqlfiddle.com/#!15/a32ee/2

Upvotes: 1

Related Questions