Postgres store procedure BEGIN and END in IF condition

Question

Hi I am new in postgres i have mysql store procedure that which i converting into postgres store procedure.

CREATE PROCEDURE saveData(
IN name character varying(200),
IN mobile character varying(15), 
IN City character varying(100),
INOUT _message character varying(100) = Null
)
LANGUAGE plpgsql 
AS $$
DECLARE 
    Message character varying(15)
BEGIN
    If <some condition> THEN
            IF <some condition> THEN
                    <insertData in table>
            END IF;
            set Message='success'
    ELSE
        <insertIntoHistoryTable query>
        IF <some condition> THEN
            <query>
        END IF;
        set Message='success'
    END IF;
    RETURNING  Message
    INTO  _message;
    COMMIT;
END
$$;

After running this query i am getting below error

ERROR:  syntax error at or near "RETURNING"
LINE 41:     RETURNING Message
             ^
SQL state: 42601
Character: 1856

I have bolded text where i am getting error. I don't know what i am doing please help me.

Your $$ after END close the quote. You don't want them. The syntax for PLPGSQL if-then-else doesn't work the way you have it, either. You have superfluous BEGINs and ENDs. https://www.postgresql.org/docs/current/plpgsql-control-structures.html — Andrew Lazarus, Jul 23 '20 at 04:03
@AndrewLazarus I have updated my question thax for that help but now i am getting new error can you help me out with this error — Dexter, Jul 23 '20 at 04:10
I usually use FUNCTIONs and not PROCEDUREs (which are a recent addition to PostgreSQL). I suspect, however, that since `Message` has been set and is an INOUT parameter, you don't have any returning clause. Note, BTW, your `COMMIT` doesn't have a corresponding `BEGIN TRANSACTION`. — Andrew Lazarus, Jul 23 '20 at 04:16
@AndrewLazarus Can you help me to convert this into function so i can accept your answer — Dexter, Jul 23 '20 at 05:18
You can't do transactions inside a FUNCTION, that was the big reason to add PROEDURE. All you have to do is remove the entire RETURNING clause. You set the in-out parameter to the value you wanted. As for the COMMIT, I think you want a BEGIN TRANSACTION right after the first BEGIN. (I have never used a transaction inside a procedure.) — Andrew Lazarus, Jul 23 '20 at 06:00

Postgres store procedure BEGIN and END in IF condition

0 Answers0