Creating external procedures
Enterprise Mode only
After you install an external procedure, you must make Vertica aware of it with CREATE PROCEDURE (external).
Only superusers can create an external procedure, and by default, only they have execute privileges. However, superusers can grant users and roles EXECUTE privilege on the stored procedure.
After you create a procedure, its metadata is stored in system table USER_PROCEDURES. Users can see only those procedures that they have been granted the privilege to execute.
Example
The following example creates a procedure named helloplanet for external procedure file helloplanet.sh. This file accepts one VARCHAR argument. The sample code is provided in Requirements for external procedures.
=> CREATE PROCEDURE helloplanet(arg1 VARCHAR) AS 'helloplanet.sh' LANGUAGE 'external'
USER 'dbadmin';
The next example creates a procedure named proctest for the script copy_vertica_database.sh. This script copies a database from one cluster to another; it is included in the server RPM located in directory
/opt/vertica/scripts.
=> CREATE PROCEDURE proctest(shosts VARCHAR, thosts VARCHAR, dbdir VARCHAR)
AS 'copy_vertica_database.sh' LANGUAGE 'external' USER 'dbadmin';
Overloading external procedures
You can create multiple external procedures with the same name if they have different signatures—that is, accept a different set of arguments. For example, you can overload the helloplanet external procedure to also accept an integer value:
=> CREATE PROCEDURE helloplanet(arg1 INT) AS 'helloplanet.sh' LANGUAGE 'external'
USER 'dbadmin';
After executing this statement, the database catalog stores two external procedures named helloplanet—one that accepts a VARCHAR argument and one that accepts an integer. When you call the external procedure, Vertica evaluates the arguments in the procedure call to determine which procedure to call.