CREATE PROCEDURE
Adds an external procedure to Vertica. See Implementing External Procedures in Extending Vertica for more information about external procedures.
Syntax
CREATE PROCEDURE [schema.]procedure-name ( ... [ argname ] [ argtype [,...] ] ) ... AS 'exec-name' ... LANGUAGE 'language-name' ... USER 'OS-user'
Parameters
schema
|
Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example: myschema.thisDbObject |
procedure-name |
Specifies a name for the external procedure, where procedure-name conforms to conventions described in Identifiers. |
argname |
[Optional] Presents a descriptive argument name to provide a cue to procedure callers. |
argtype |
[Optional] Specifies the data type for argument(s) that will be passed to the procedure. Argument types must be one of the followingVertica type names: BIGINT, BOOLEAN, DECIMAL, DOUBLE PRECISION, FLOAT, FLOAT8, INT, INT8, INTEGER, MONEY, NUMBER, NUMERIC, REAL, SMALLINT, TINYINT, VARCHAR. |
AS |
Specifies the executable program in the procedures directory. |
LANGUAGE |
Specifies the procedure language. This parameter must be set to EXTERNAL. |
USER |
Specifies the user executed as. The user is the owner of the file. The external program must allow execute privileges for this user. The user cannot be root. |
Privileges
To create a procedure a superuser must have CREATE privilege on schema to contain procedure.
Notes
- A procedure file must be owned by the database administrator (OS account) or by a user in the same group as the administrator. (The procedure file owner cannot be root.) The procedure file must also have the set UID attribute enabled, and allow read and execute permission for the group.
- By default, only a database superuser can execute procedures. However, a superuser can grant the right to execute procedures to other users. See GRANT (Procedure).
Important: External procedures that you create with CREATE PROCEDURE are always run with Linux dbadmin privileges. If a dbadmin or pseudosuperuser grants a non-dbadmin permission to run a procedure using GRANT (Procedure), be aware that the non-dbadmin user runs the procedure with full Linux dbadmin privileges.
Example
This example illustrates how to create a procedure named helloplanet for the helloplanet.sh external procedure file. This file accepts one varchar argument.
Sample file:
#!/bin/bash echo "hello planet argument: $1" >> /tmp/myprocedure.log
Issue the following SQL to create the procedure:
=> CREATE PROCEDURE helloplanet(arg1 varchar) AS 'helloplanet.sh' LANGUAGE 'external' USER 'dbadmin';