My error or parser bug? re stored function [SEC=UNCLASSIFIED]

From: "Pilling, Michael" <Michael(dot)Pilling(at)dsto(dot)defence(dot)gov(dot)au>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: My error or parser bug? re stored function [SEC=UNCLASSIFIED]
Date: 2011-02-11 06:58:07
Message-ID: DB2FF420856DB942829BF029A7E3C19603F00D@ednex514.dsto.defence.gov.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Maximilian Tyrtania 2011-02-11 14:47:43 Re: Make pgAdmin default "Not NULL" checkbox to ON
Previous Message Pilling, Michael 2011-02-11 05:04:05 What is the difference between SET OF and TABLE? [SEC=UNCLASSIFIED]