Thread: 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
And the semi-colon should be removed after the END Philippe Philippe wrote: > 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 >
Yes, that was it. The other QUITE similar language I've used didn't require the END IF if it was a one-liner. You can bet I won't forget that one again - or longer than it takes to goof up and rediscover it! Thanks! Ralph ============================================ On Jun 19, 2008, at 12:49 PM, Philippe Grégoire wrote: > 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 >>
Easy: you've got 3 Ifs without "END IF":
IF uppergt = 'BOD' THEN RETURN 0 ;
IF uppergt = 'MOD' THEN RETURN 86400/2 ;
IF uppergt = 'EOD' THEN RETURN 86399 ;
Igor
-----Original Message-----
From: Ralph Smith [mailto:smithrn@washington.edu]
Sent: Thursday, June 19, 2008 3:19 PM
To: pgsql-general@postgresql.org
Subject: 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