Creating External Procedures
Once you have installed an external procedure, you need to make Vertica aware of it. To do so, use the CREATE PROCEDURE statement.
Only a superuser can create an external procedure. Initially, only superusers can execute an external procedure. However, a superuser can grant the right to execute a stored procedure to a user on the operating system. (See GRANT (Procedure).)
Once created, a procedure is listed in the V_CATALOG.USER_PROCEDURES
system table. Users can see only those procedures that they have been granted the privilege to execute.
Example
This example creates a procedure named helloplanet
for the helloplanet.sh
external procedure file. 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';
This example creates a procedure named proctest
for the copy_vertica_database.sh
script. This script copies a database from one cluster to another, and it is included in the server RPM located in the /opt/vertica/scripts
directory.
=> 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 as long as they have a different signature (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 the above statement, your database catalog will have two external procedures named helloplanet
—one that accepts a VARCHAR argument and one that accepts an integer. When you call the external procedure, Vertica determines which procedure to call based on the arguments you passed in the procedure call.