1

I did the db migration from oracle to pgsql and got the code like below:

CREATE OR REPLACE FUNCTION PKG_UTIL_BD_LOGISTICS_getsignerinfo ( i_opCode T_MQ_LOGIC_TRACK_HEAD_LOG.LP_CODE%TYPE, i_remark T_MQ_LOGIC_TRACK_HEAD_LOG.REMARK%TYPE, i_acceptTime T_MQ_LOGIC_TRACK_HEAD_LOG.SIGNED_TIME%TYPE, i_signer T_MQ_LOGIC_TRACK_HEAD_LOG.SIGNER%TYPE, i_lpcode T_MQ_LOGIC_TRACK_HEAD_LOG.LP_CODE%TYPE, o_signer OUT T_MQ_LOGIC_TRACK_HEAD_LOG.SIGNER%TYPE, o_signerTime OUT T_MQ_LOGIC_TRACK_HEAD_LOG.SIGNED_TIME%TYPE, o_status OUT T_MQ_LOGIC_TRACK_HEAD_LOG.STATUS%TYPE ) RETURNS RECORD AS $body$ DECLARE v_signer T_MQ_LOGIC_TRACK_HEAD_LOG.SIGNER%TYPE; v_signerTime T_MQ_LOGIC_TRACK_HEAD_LOG.SIGNED_TIME%TYPE; v_status T_MQ_LOGIC_TRACK_HEAD_LOG.STATUS%TYPE; BEGIN IF i_lpcode = 'SF' THEN IF i_opCode = '8000' THEN IF POSITION('：back' in i_remark) > 0 THEN v_status := '3'; ELSE v_status := '7'; v_signerTime := i_acceptTime; v_signer := SUBSTR(i_remark, POSITION('：' in i_remark) + 1); END IF; ELSIF i_opCode = '9999' THEN v_status := '3'; ELSIF i_opCode = '80' THEN v_status := '7'; v_signerTime := i_acceptTime; ELSIF i_opCode = 70 THEN v_status := i_opCode; ELSE v_status := '1'; END IF; ELSE IF i_opCode = 'signed' THEN v_signerTime := i_acceptTime; v_signer := i_signer; v_status:='7'; ELSE v_status:='1'; END IF; END IF; o_status := v_status; o_signer := v_signer; o_signerTime := v_signerTime; RETURN 1; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION '%', 'PKG_UTIL_BD_LOGISTICS.getSignerInfo fetch parameters' || i_remark || 'value error:' || SQLERRM; END; $body$ LANGUAGE PLPGSQL SECURITY DEFINER

When I executed the code, I got the error below:

ERROR: RETURN cannot have a parameter in function with OUT parameters

Can someone help? I am new to pgsql.

Improve this question

3 Answers 3

Reset to default
6

The result of function with OUT parameters is specified by values of OUT parameters and only by these values. Although syntax of OUT parameters is similar between PostgreSQL and Oracle, a implementation is maximally different.

Oracle uses reference for OUT parameters - so you can write something like:

CREATE FUNCTION foo(a int, OUT b int) RETURN boolean IS BEGIN b := a; RETURN true; END;

This function returns boolean value and as "side" effect it modifies second parameter passed by reference.

PostgreSQL doesn't support passing parameters by reference. All parameters are passed by value only. When You use OUT parameter, then there is not passed reference, but the returned values is taken from result composite. Result composite is composed only from OUT parameters. There are no space for some any other. So code:

CREATE OR REPLACE FUNCTION foo(a INT, OUT b int) RETURNS boolean AS $$ BEGIN b := a; RETURN true; END; $$ LANGUAGE plpgsql

is invalid, because real result of foo function is scalar int value, what is in contradiction with declared boolean. RETURN true is wrong too, because result is based on OUT parameters only, and then RETURN should be without any expression.

Equivalent translation of function foo from Oracle to Postgres is:

CREATE OR REPLACE FUNCTION foo(a INT, OUT b int, OUT result boolean) RETURNS record AS $$ BEGIN b := a; result := true; RETURN; END; $$ LANGUAGE plpgsql

Easy rule - when function has OUT variables in Postgres, then RETURN statement is used only for ending execution - not for returned value specification. This values is based by OUT parameters.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Consider the following example :

CREATE OR REPLACE FUNCTION generate_taskcode(IN classIdVar character varying,IN appIdInt integer, OUT pagecoderet character varying) RETURNS character varying AS $$ DECLARE updateCode character varying; BEGIN update mytable set lastcodeused = to_char(cast(lastcodeused as INTEGER)+1, 'FM999999999999999999') where classid = classIdVar and appid= appIdInt RETURNING concat(pageName,lastcodeused) as pageName into updateCode; return updateCode; END; $$ LANGUAGE plpgsql

notice the "updatecode" in between begin and end. If you'll try to execute this you'll get the same error as you've mentioned as you can't return variable "** return updateCode**" when you've OUT in function parameters. So the correct def would be as follows :

CREATE OR REPLACE FUNCTION generate_taskcode(IN classIdVar character varying,IN appIdInt integer, OUT pagecoderet character varying) RETURNS character varying AS $$ DECLARE updateCode character varying; BEGIN update mytable set lastcodeused = to_char(cast(lastcodeused as INTEGER)+1, 'FM999999999999999999') where classid = classIdVar and appid= appIdInt RETURNING concat(pageName,lastcodeused) as pageName into updateCode; pagecoderet = updateCode; return; END; $$ LANGUAGE plpgsql
Improve this answer

Comments

0

I got the same error below:

ERROR: RETURN cannot have a parameter in function with OUT parameters

Because I returned 2 with RETURN 2; when I used an OUT parameter in a PL/pgSQL function as shown below:

CREATE FUNCTION my_func(OUT value INTEGER) AS $$ BEGIN -- ↑ Here RETURN 2; -- Here END; $$ LANGUAGE plpgsql;

So, I returned nothing with or without RETURN; as shown below, then the error was solved:

CREATE FUNCTION my_func(OUT value INTEGER) AS $$ BEGIN -- ↑ Here RETURN; -- Here END; $$ LANGUAGE plpgsql;

Or:

CREATE FUNCTION my_func(OUT value INTEGER) AS $$ BEGIN -- ↑ Here -- RETURN; -- Here END; $$ LANGUAGE plpgsql;
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.