A plpgsql unidentifiable problem.

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

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

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