Re: A plpgsql unidentifiable problem.

I think that the IF clauses need END IF.

  IF uppergt = 'BOD' THEN RETURN 0; END IF;
  IF uppergt = 'MOD' THEN RETURN 86400/2; END IF;
  IF uppergt = 'EOD' THEN RETURN 86399; END IF;

This should solve the problem.

Philippe Gregoire
Information Manager
www.boreal-is.com



Ralph Smith wrote:
> I'm baffled and have tried various variations but still nogo.
>
> From PgAdmin III I get:
> -------------------------------------------
> ********** Error **********
>
> ERROR: syntax error at or near ";"
> SQL state: 42601
> Character: 19001
>
> -- referring to the semi-colon after the 'END' statement.
>
>
> *******************************
> When I check this out in PgAdmin III.
> -------------------------------------------
> CREATE OR REPLACE FUNCTION time_to_utime(given_time varchar) RETURNS
> INT AS
> $$
>
> DECLARE
>
>   uppergt   varchar ;
>   colon1    int ;
>   colon2    int ;
>   digitsA   varchar ;
>   digitsB   varchar ;
>   digitsC   varchar ;
>   numberA   int ;
>   numberB   int ;
>   numberC   int ;
>   result    int ;
>
> BEGIN
>
>   IF check_time(given_time) = FALSE THEN
>     RAISE NOTICE 'The time passed into function time_to_utime is not
> in a valid format.' ;
>   END IF ;
>
>   -- ----------------------------------------
>
>   uppergt := upper(given_time) ;
>
>   IF uppergt = 'BOD' THEN RETURN 0 ;
>   IF uppergt = 'MOD' THEN RETURN 86400/2 ;
>   IF uppergt = 'EOD' THEN RETURN 86399 ;
>
>   -- ----------------------------------------
>
>   colon1:=strpos(invar,':') ;
>   colon2:=colon1+strpos(substring(invar from colon1+1),':') ;
>
>   digitsA := split_part(invar,':',1) ;
>   numberA := to_number(digitsA,'99') ;
>
>   digitsB := split_part(invar,':',2) ;
>   numberB := to_number(digitsB,'99') ;
>
>   digitsC := split_part(invar,':',3) ;
>   numberC := to_number(digitsC,'99') ;
>
>   result := 3600*numberA + 60*numberB + numberC  ;
>
>   RETURN result ;
>
> END ;
> $$ LANGUAGE PLPGSQL ;    /* time_to_utime   */
> ********************************************
>
> Any clues?
>
> THANKS!
>
> Ralph Smith
>
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG.
> Version: 7.5.524 / Virus Database: 270.4.0/1507 - Release Date: 6/18/2008 7:09 AM
>