0
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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
johnnily
  • 153
  • 2
  • 5
  • 11
  • Is this supposed to be a trigger function? If yes, provide the trigger creation script, some explanation and the table definition of involved tables. Please clarify. – Erwin Brandstetter Mar 31 '13 at 16:10

2 Answers2

2

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.

Proper function

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$
  • Don't run the SELECT statement twice. Use FOUND instead.
  • flightdate seems to be timestamp.
    An expression like 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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • What is your PostgreSQL version? – Pavel Stehule Apr 01 '13 at 13:45
  • @Pavel: Only I was notified of your comment. Also, johnnily's comment was before I added more. – Erwin Brandstetter Apr 01 '13 at 14:03
  • 1
    @Erwin: just one tiny note - and it is my personal opinion - every non void function should be finished by RETURN statement. It is optional now, but I am thinking so it increase a readability (and RETURN has +/- zero overhead). Otherwise, your code is nice example, why RETURN QUERY should not finish execution (it is nice trick). When we designed this statement, we talked about behave and one possibility was a different behave. I read a PostgreSQL doc, and there are no good example of RETURN QUERY - can you send this example as doc patch? – Pavel Stehule Apr 01 '13 at 15:28
  • @PavelStehule: I sent a mail to pgsql-docs@postgresql.org with the title [`Code examples for 39.6.1. Returning From a Function`](http://www.postgresql.org/message-id/flat/5161EE50.6090004@falter.at#5161EE50.6090004@falter.at) (including another fix), referring to this page. Now Peter Eisentraut wonders what to do with it exactly. Can you have a look? – Erwin Brandstetter Apr 22 '13 at 02:44
  • 1
    I sent a patch to mailing list – Pavel Stehule Apr 23 '13 at 06:38
2

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;
$$
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • @johnnily: That was because of the missing `;` 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. – Erwin Brandstetter Mar 31 '13 at 16:21
  • @johnnily . . . I just added a semicolon to the end of the statement, which I think is necessary in Postgres. – Gordon Linoff Mar 31 '13 at 16:22