Hi, I'm new to postgres but not to SQL, and have not been able to this stored function to compile:
The code I've been using is:
CREATE OR REPLACE FUNCTION best_spelling(IN abbreviation varchar(40), IN context INT, IN tts_engine varchar(40), IN voice varchar(40))
RETURNS TABLE( t SpellingType, s varchar(40) ) AS
$BODY$
BEGIN
IF EXISTS( voice_spelling( $1, $2, $3, $4 ) ) THEN
RETURN voice_spelling( $1, $2, $3, $4 );
ELSIF EXISTS( accent_spelling( $1, $2, $3, $4 ) ) THEN
RETURN accent_spelling( $1, $2, $3, $4 );
ELSE
RETURN context_spelling( $1, $2 );
END IF;
END
$BODY$ LANGUAGE 'plpgsql';
Gives the following error message:
An error occurred when executing the SQL command:
ELSIF EXISTS( accent_spelling( $1, $2, $3, $4 ) ) THEN
RETURN accent_spelling( $1, $2, $3, $4 )
ERROR: syntax error at or near "ELSIF"
Position: 1 [SQL State=42601]
I've tried replacing $1 with abbreviation etc. like so
and a different error occurs:
CREATE OR REPLACE FUNCTION best_spelling(IN abbreviation varchar(40), IN context INT, IN tts_engine varchar(40), IN voice varchar(40))
RETURNS TABLE( t SpellingType, s varchar(40) ) AS
$BODY$
BEGIN
IF EXISTS( voice_spelling( abbreviation, context, tts_engine, voice ) ) THEN
RETURN voice_spelling( abbreviation, context, tts_engine, voice );
ELSIF EXISTS( accent_spelling( abbreviation, context, tts_engine, voice ) ) THEN
RETURN accent_spelling( abbreviation, context, tts_engine, voice );
ELSE
RETURN context_spelling( abbreviation, context );
END IF;
END
$BODY$ LANGUAGE 'plpgsql';
An error occurred when executing the SQL command:
CREATE OR REPLACE FUNCTION best_spelling(IN abbreviation varchar(40), IN context INT, IN tts_engine varchar(40), IN voice varchar(40))
RETURNS TAB...
ERROR: unterminated dollar-quoted string at or near "$BODY$
BEGIN
IF EXISTS( voice_spelling( abbreviation, context, tts_engine, voice ) ) THEN
RETURN voice_spelling( abbreviation, context, tts_engine, voice )"
Position: 192 [SQL State=42601]
The functions voice_spelling, accent_spelling and context spelling all compile OK and return tables of the type given for this function to return.
I'd be greatful for any help, this has been driving me nuts for days.
Regards,
Michael
IMPORTANT: This email remains the property of the Department of Defence and is subject to the jurisdiction of section 70 of the Crimes Act 1914. If you have received this email in error, you are requested to contact the sender and delete the email.