CREATE PROCEDURE

Adds an external procedure to Vertica. See Implementing External Procedures in Extending Vertica for more information about external procedures.

Syntax

CREATE PROCEDURE [[database.]schema.]procedure( [ argument-list] ) 
... AS 'executable'
... LANGUAGE 'language'
... USER 'OS-user'

Parameters

[database.]schema

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

If you specify a database, it must be the current database.
procedure

Specifies a name for the external procedure, where procedure-name conforms to conventions described in Identifiers.

argument-list

A comma-delimited list of procedure arguments, where each argument is specified as follows:

[ argname ] argtype
  • argname optionally provides a descriptive name for this argument.
  • argtype must be one of the following data types supported by Vertica: BIGINT, BOOLEAN, DECIMAL, DOUBLE PRECISION, FLOAT, FLOAT8, INT, INT8, INTEGER, MONEY, NUMBER, NUMERIC, REAL, SMALLINT, TINYINT, VARCHAR.

If the procedure is defined with no arguments, supply an empty argument list.
executable The name of the executable program in the procedures directory.
language

Specifies the procedure language, set to EXTERNAL.
USER

The owner of the file. The external program must allow execute privileges for this user. The user cannot be root.

Privileges

Non-superuser: CREATE privilege on the schema to contain the procedure.

System Security

  • 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.

  • 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.

Examples

The following example shows how to create procedure helloplanet for external procedure file helloplanet.sh. This file accepts one varchar argument.

Create the file:

#!/bin/bash
echo "hello planet argument: $1" >> /tmp/myprocedure.log

Create the procedure with the following SQL: 

=> CREATE PROCEDURE helloplanet(arg1 varchar) AS 'helloplanet.sh' LANGUAGE 'external' USER 'dbadmin';

See Also