MySQL procedure to PostgreSQL function

Question

Migrating from MySQL to PostgreSQL.

DROP PROCEDURE IF EXISTS prepend;
DELIMITER $$
CREATE PROCEDURE prepend
(
 IN inParam VARCHAR(255), 
 INOUT inOutParam INT
)
BEGIN
 DECLARE z INT;
 SET z = inOutParam + 1;
 SET inOutParam = z;
 SELECT inParam;
 SELECT CONCAT('zyxw', inParam);
END;$$
DELIMITER ;

CALL prepend('abcdefg', @inOutParam);

The MySQL procedure call output is:

abcdefg
zyxwabcdefg

Verify the output here.

Here's the original MySQL code snippet.

The corresponding PostgreSQL function is not working. Please help.

DROP FUNCTION prepend;

CREATE OR REPLACE FUNCTION prepend
(
 inParam VARCHAR, 
 INOUT inOutParam INT
)
AS $$
DECLARE z INT;
BEGIN 
 z := inOutParam + 1;
 inOutParam := z;
 SELECT inParam;
 SELECT CONCAT('zyxw', inParam);
END; $$
LANGUAGE plpgsql;

SELECT prepend('abcdefg', 0);

Pavel Stehule · Accepted Answer · 2018-10-07 09:03:19Z

PostgreSQL has not unbound queries - this technique is available on Sybase like databases (Sybase, MSSQL) and MySQL (MariaDB). Currently you can write function that can returns set of some values (tabular result) or returns scalar, composite or array (usual functions).

So most near design to your procedure is:

CREATE OR REPLACE FUNCTION prepend(inparam text)
RETURNS SETOF text AS $$
BEGIN
  RETURN NEXT inparam;
  RETURN NEXT 'zyxw' || inparam;
END;
$$ LANGUAGE plpgsql;

you can call this function with SELECT

SELECT * FROM prepend('abcdefg');

That is all what is possible. You cannot to set other out variables in this case.

This is common problem when you porting stored procedures from Sybase like systems that uses this technique to any other database (Postgres, Oracle, DB2, ...). The functionality of these systems cannot be mapped simply 1:1.

Because Postgres (plpgsql) has not unbounded queries support, then the syntax is prohibited.

BEGIN
  SELECT 1;
END;

Has not sense there. Any result of plpgsql functions can be realized by using OUT variables or by using RETURN statement.

Collectives™ on Stack Overflow

MySQL procedure to PostgreSQL function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related