Executing External Procedures

Once you define a procedure through the CREATE PROCEDURE statement, you can use it as a meta command through a simple 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' );

External procedures have no direct access to database data. If available, 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.

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

See Also