pL/pgSQL function parse error??

[melody]

Hi,

I've written the following function in pL/pgSQL. Basically, I want to return a string based on the content of 2 fields - price and status. price is a numeric (8,2) field and status is int4. If status is 0, i would like the price returned. If it's 1, i'd like a string returned saying "sold out". Otherwise, if the status field is neither 0 or 1, i'd like a string returned saying "P.O.A". Anyway, here's the function:

CREATE FUNCTION format_price(numeric(8,2), int4) RETURNS text IMMUTABLE AS '
DECLARE
        price ALIAS FOR $1;
        status ALIAS FOR $2;
        textprice text;
        ret text;
BEGIN
        -- cast price as string
        textprice := price::text;

    IF ((price>"0.00") AND (status="0")) -- we can just print out the price
            THEN ret := "£" || textprice;
    ELSE IF ((price>"0.00") AND (status="1")) --currently sold out
            THEN ret := "Sold Out";
    ELSE IF ((price>"0.00") AND (status!="0")) -- same as above, but is a failsafe in case status is not 0 or 1
            THEN ret := "Sold Out";
    ELSE --the price is 0.00, it must be POA
            ret := "P.O.A.";
    END IF;

RETURN ret;

END;
' LANGUAGE 'plpgsql';

I get the message:

ERROR: parse error at or near ';' on line 22.

I can't seem to see the problem. I've tried querying the price with and without the double quotes round it ("0.00") and that makes no difference.

Can anyone help please?

Thanks
Melody

[Sxooter]

You likely need to double single quote the values in your query:

where field = ''some value''

And I'm not sure the END statement requires a semicolon after it

[melody]

Thanks for taking the time to reply Sxooter.

Thanks for the tip about double quoting - I've now double quoted everything and it's working well.

The solution however, if anyone's interested was in the "ELSE IF..." statements. Apprently if you use this structure it's counted as a nested IF statement and you have to explicitly say END IF at the end of it. I changed ELSE IF for ELSIF and it worked a dream (after you'd put me on the right track about the double single quoting that is!)

Thanks for the help

Melody

[Sxooter]

So, have you tried pl/php yet? It's quite nice as well.

It's still not a complete replacement for pl/pgsql, but it's getting there. And, if you're already a php coder, it does make life a lot easier.