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

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';