postgresql FUNCTION syntax error

Question

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" ... I was searching the syntax for the function... A bit confuse why some of function need the BEGIN and some are not..

Can anyone please give me some hint..thanks so much..!

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.

Gordon Linoff · Accepted Answer · 2013-03-31 16:21:32Z

up vote 0 down vote accepted

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;
$$

edited 37 mins ago

answered 50 mins ago

Gordon Linoff
47k21726

	thanks but it said Syntax error near END IF; – johnnily 41 mins ago
	@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 36 mins ago
	@johnnily . . . I just added a semicolon to the end of the statement, which I think is necessary in Postgres. – Gordon Linoff 36 mins ago
	it work until i change the returns INTEGER to SETOF...thanks – johnnily 7 mins ago

Erwin Brandstetter · Answer 2 · 2013-03-31 16:07:03Z

up vote 1 down vote

Among other syntax errors, you are declaring it to be an SQL function, while in fact you are using PL/pgSQL syntax. Make that:

LANGUAGE plpgsql

answered 51 mins ago

Erwin Brandstetter
52.7k53776

thanks.but still the error occupied.. – johnnily 37 mins ago

asked	today
viewed	17 times
active	today

postgresql FUNCTION syntax error

2 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged sql function postgresql postgresql-9.1 or ask your own question.

postgresql FUNCTION syntax error

2 Answers

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql function postgresql postgresql-9.1 or ask your own question.

Related