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.

See Also