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. Vertica 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)

The following example runs a procedure named proctest. This procedure references the copy_vertica_database.sh script that copies a database from one cluster to another. It is installed by the server RPM in the /opt/vertica/scripts directory.

=> SELECT proctest(
    '-s qa01',
    '-t rbench1',
    '-D /scratch_b/qa/PROC_TEST' );

Note

External procedures have no direct access to database data. Use ODBC or JDBC for this purpose.

Procedures are executed on the initiating node. Vertica 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, Vertica reports the operation took place by returning one row as shown in the helloplanet example. If the child process exits with any other status, Vertica reports an error like 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