Stored Procedures

You can condense complex database tasks and routines into stored procedures. Unlike external procedures, stored procedures live and can be executed from inside your database; this lets them communicate and interact with your database directly to perform maintenance, execute queries, and update tables.

Best Practices

Many other databases are optimized for online transaction processing (OLTP), which focuses on frequent transactions. In contrast, Vertica is optimized for online analytical processing (OLAP), which instead focuses on storing and analyzing large amounts of data and delivering the fastest responses to the most complex queries on that data.

This architecture difference means that the recommended use cases and best practices for stored procedures in Vertica differ slightly from stored procedures in other databases.

While stored procedures in OLTP-oriented databases are often used to perform small transactions, stored procedures in OLAP-oriented databases like Vertica should instead be used to enhance analytical workloads. Vertica can handle isolated transactions, but frequent small transactions can potentially hinder performance.

Some recommended use cases for stored procedures in Vertica include information lifecycle management (ILM) activities such as extract, transform, and load (ETL), and data preparation for tasks like machine learning. For example:

  • Swapping partitions according to age
  • Exporting data at end-of-life and dropping the partitions
  • Saving inputs, outputs, and metadata from a machine learning model—who ran the model, the version of the model, how many times the model was run, and who received the results

Stored procedures in Vertica can also operate on objects that require higher privileges than that of the caller. An optional parameter allows procedures to run using the privileges of the definer, allowing callers to perform sensitive operations in a controlled way.

Known Issues and Workarounds

  • You cannot use PERFORM CREATE FUNCTION to create a SQL macro.

    Use EXECUTE to make SQL macros inside a stored procedure:

    CREATE PROCEDURE procedure_name() 
LANGUAGE PLvSQL AS $$
BEGIN
    EXECUTE 'macro';
end;
$$;

    Where macro is the creation statement for a SQL macro.

    For example, this procedure creates the argmax macro:

    => CREATE PROCEDURE make_argmax() LANGUAGE PLvSQL AS $$
BEGIN
    EXECUTE 
        'CREATE FUNCTION 
        argmax(x int) RETURN int AS 
        BEGIN 
            RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END);
        END';
END;
$$;
  • Non-error exceptions in embedded SQL statements are not reported
  • DECIMAL, NUMERIC, NUMBER, MONEY, and UUID data types cannot yet be used for arguments
  • Cursors should capture the variable context at declaration time, but they currently capture the variable context at open time.

  • DML queries on tables with key constraints cannot yet return a value.

    Rather than:

    DO $$
DECLARE
    y int;
BEGIN
    y := UPDATE tbl WHERE col1 = 3 SET col2 = 4;
END;
$$

    Instead, check the result of the DML query with SELECT:

    DO $$
DECLARE
    y int;
BEGIN
    y := SELECT COUNT(*) FROM tbl WHERE col1 = 3;
    PERFORM UPDATE tbl SET col2 = 4 WHERE col1 = 3;
END;
$$;

In This Section