CREATE PROCEDURE (external)

Adds an external procedure to Vertica.

Enterprise Mode only

Adds an external procedure to Vertica. See External procedures for more information.

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-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 byVertica:

    • 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, a string.
OS-user
The owner of the file, a string. The owner:

  • Cannot be root

  • Must have execute privileges on 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 a procedure named helloplanet for the 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