CREATE PROCEDURE (External)

Enterprise Mode only

Adds an external procedure to Vertica. See Implementing External Procedures for more information on creating external procedures.

Syntax

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

Parameters

IF NOT EXISTS

If an object with the same name exists, do not create it and proceed. If you omit this option and the object exists, Vertica generates a ROLLBACK error message. In both cases, the object is not created if it already exists.

The IF NOT EXISTS clause is useful for SQL scripts where you want to create an object if it does not already exist.

For related information, see ON_ERROR_STOP.

This option cannot be used with OR REPLACE.
[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.
procedure

Specifies a name for the external procedure, where procedure-nameconforms 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
executable The name of the executable program in the procedures directory.
OS-user

The owner of the file, where OS-user:

  • Cannot be root
  • Must have execute privileges on the executable

Privileges

Superuser

System Security

  • The 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 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 (External) 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