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, return without creating the object. If you do not use this directive and the object already exists, Vertica returns with an error message.

The IF NOT EXISTS clause is useful for SQL scripts where you might not know if the object already exists. The ON ERROR STOP directive can be helpful in scripts.

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 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, 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