Executing external procedures

After you define a procedure using the CREATE PROCEDURE statement, you can use it as a meta command in a SELECT statement.

Enterprise Mode only

After you define a procedure using the CREATE PROCEDURE (external) statement, you can use it as a meta command in a SELECT statement. OpenText™ Analytics Database does not support using procedures in more complex statements or in expressions.

The following example runs a procedure named helloplanet:

=> SELECT helloplanet('earthlings');
 helloplanet
-------------
           0
(1 row)

Procedures are executed on the initiating node. The database runs the procedure by forking and executing the program. Each procedure argument is passed to the executable file as a string. The parent fork process waits until the child process ends.

If the child process exits with status 0, the database reports the operation took place by returning one row as shown in the helloplanet example. If the child process exits with any other status, the database reports an error similar to the following:

ERROR 7112: Procedure reported: Procedure execution error: exit status = code

To stop execution, cancel the process by sending a cancel command (for example, CTRL+C) through the client. If the procedure program exits with an error, an error message with the exit status is returned.

Permissions

To execute an external procedure, the user needs:

  • EXECUTE privilege on procedure

  • USAGE privilege on schema that contains the procedure