Обсуждение: My error or parser bug? re stored function [SEC=UNCLASSIFIED]

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.

"Pilling, Michael" <Michael.Pilling@dsto.defence.gov.au> writes:
> Hi, I'm new to postgres but not to SQL, and have not been able to this stored function to compile:

FWIW, I get completely different errors than you do when I paste these
examples into psql:

ERROR:  syntax error at or near "voice_spelling"
LINE 5:  IF EXISTS( voice_spelling( $1, $2, $3, $4  ) ) THEN
                    ^

ERROR:  syntax error at or near "voice_spelling"
LINE 5:  IF EXISTS( voice_spelling( abbreviation, context, tts_engin...
                    ^

These errors make sense to me because the argument of EXISTS() has to
be a sub-SELECT.  I'm not sure what you're expecting EXISTS to do with a
function call.

As far as the errors you actually got, it's difficult to say for sure
without any context like what PG version this is or what client-side
software you were using.  The formatting you show for the errors sure
doesn't look like psql, though.  First guess is that you were passing
the SQL code through something that thinks it knows SQL syntax but
doesn't cope well with dollar-quoted strings, or something like that.

            regards, tom lane