Re: create function problem

Hello

you badly use a IF statement. It's not C. Every IF must to finish with END IF

this is

IF .. THEN
ELSEIF .. THEN ..
ELSE
END IF

Regards

Pavel Stehule

2010/12/30 Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>:
> Hi folks,
>
> I'm writing my first plpsql function in ages and I'm going blind trying to see
> why it won't create. The error message and the code are below. I'm guessing
> it's something blindingly obvious, but can someone please point it out to me.
>
> ta
>
> Gary
>
> The actual line number is the LANGUAGE line at the end of the file.
>
> goole=# \i project_delivery_date.sql
> psql:project_delivery_date.sql:42: ERROR:  syntax error at or near ";"
> LINE 37: END;
>            ^
> goole=# \q
> [root(at)stan t-cards]# cat project_delivery_date.sql
> -- vim: ft=sql et ai ic
> --
> -- project_delivery_date() - project delivery date from existing dates
>
> CREATE FUNCTION project_delivery_date(date,date,date,date) RETURNS date AS $$
> DECLARE
>  eta_dealer ALIAS FOR $1;
>  eta_customer ALIAS FOR $2;
>  req_date ALIAS FOR $3;
>  act_date ALIAS FOR $4;
>  eta date;
>
> BEGIN
>  IF act_date IS NOT NULL THEN
>    return act_date;
>  END IF;
>  IF eta_dealer IS NOT NULL AND eta_customer IS NULL THEN
>    eta := eta_dealer;
>  ELSE IF eta_dealer IS NULL AND eta_customer IS NOT NULL THEN
>    eta := eta_customer;
>  ELSE IF eta_dealer IS NULL AND eta_customer IS NULL THEN
>    eta := NULL;
>  ELSE IF eta_dealer > eta_customer THEN
>    eta := eta_dealer;
>  ELSE
>    eta := eta_customer;
>  END IF;
>  IF eta IS NOT NULL AND req_date IS NULL THEN
>    RETURN eta;
>  END IF;
>  IF eta IS NULL AND req_date IS NOT NULL THEN
>    RETURN req_date;
>  END IF;
>  IF eta IS NULL AND req_date IS NULL THEN
>    RETURN NULL;
>  END IF;
>  IF eta > req_date THEN
>    RETURN eta;
>  END IF;
>  RETURN req_date;
> END;
> $$ LANGUAGE 'plpgsql';
>
> [root(at)stan t-cards]#
>
> --
> Gary Stainburn
> I.T. Manager
> Ringways Garages
> http://www.ringways.co.uk
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>