CREATE FUNCTION check_available_flightID(date)
RETURNS integer AS
$$
BEGIN
SELECT FlightID FROM Flight WHERE FlightDate::date = $1;
IF NEW.FlightID IS NULL THEN
RAISE EXCEPTION 'No filight in the date you enter'
END IF;
RETURN query SELECT FlightID FROM Flight WHERE FlightDate::date = $1;
$$
LANGUAGE SQL;
It said that the syntax error near "IF" ...
A bit confused why some functions need the BEGIN and some do not.
Can anyone please give me a hint?
I am not an expert in Postgres, but I think new is for triggers and not for regular functions.
I think you want something like:
CREATE FUNCTION check_available_flightID(date)
RETURNS integer AS
$$
declare newfid int;
BEGIN
SELECT FlightID into newfid FROM Flight WHERE FlightDate::date = $1;
IF newfid IS NULL THEN
RAISE EXCEPTION 'No flight in the date you enter';
END IF;
RETURN query SELECT FlightID FROM Flight WHERE FlightDate::date = $1;
end;
$$
; after the raise statement. There are more syntax errors here. But the guessing game is pointless. Please edit your question and provide more information as requested.
Among other syntax errors, you are declaring it to be an SQL function while, in fact, it's PL/pgSQL syntax. Make that LANGUAGE plpgsql.
The currently accepted answer is very inefficient. Don't use it. This should do a better job (based on assumptions where the OP is vague):
CREATE FUNCTION check_available_flightid(date)
RETURNS SETOF integer
LANGUAGE plpgsql AS
$func$
BEGIN
RETURN QUERY
SELECT flightid
FROM flight
WHERE flightdate >= $1
AND flightdate < ($1 + 1);
IF NOT FOUND
RAISE EXCEPTION 'No flight at %.', $1;
END IF;
END
$func$
SELECT statement twice. Use FOUND instead.flightdate seems to be timestamp.FlightDate::date = $1 is not sargable (cannot use a plain index). My alternative can use an index on flightdate and will be much faster. The difference grows with the size of the table.This function was later added as example in the manual.
Code examples for 39.6.1. Returning From a Function (including another fix), referring to this page. Now Peter Eisentraut wonders what to do with it exactly. Can you have a look?