Error reported when creating a stored procedure in openGauss: ORA-00933

Question

I am trying to write a stored procedure in openGauss database, but I am getting an error message: ORA-00933: SQL command not properly ended. Here is the code:

CREATE OR REPLACE PROCEDURE get_employee_details

IS

BEGIN

SELECT employee_name, employee_id, salary

FROM employee;

END;

I am trying to write a stored procedure in openGauss database, but I am getting an error message: ORA-00933:

Answer 1

I tried put your stored procedure into a .sql file and ran it as follows.

gsql -p $MYPORT -d $MYDB -f test.sql 
CREATE PROCEDURE
total time: 13  ms

As you can see it was successful and the stored procedure can be listed.

openGauss=# \df
                                                  List of functions
 Schema |         Name         | Result data type | Argument data types |  Type  | fencedmode | propackage | prokind
 
--------+----------------------+------------------+---------------------+--------+------------+------------+--------
-
 public | get_employee_details | void             |                     | normal | f          | f          | p
(1 row)

You probably don't want to be using a stored procedure for this though as it will not return any rows. Try the following instead:

CREATE OR REPLACE FUNCTION func_get_employee_details
  returns setof employee
  LANGUAGE plpgsql
AS
$BODY$
BEGIN

return query
SELECT employee_name, employee_id, salary
FROM employee;

END;
$BODY$