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.

Viewing stored procedures

To view existing stored procedures, see USER_PROCEDURES.

=> SELECT * FROM USER_PROCEDURES;
 procedure_name |  owner  | language | security | procedure_arguments | schema_name
----------------+---------+----------+----------+---------------------+-------------
 raiseXY        | dbadmin | PL/vSQL  | INVOKER  | x int, y varchar    | public
 raiseXY        | dbadmin | PL/vSQL  | INVOKER  | x int, y int        | public
(2 rows)

To view the the source code for stored procedures, export them with EXPORT_OBJECTS.

To export a particular implementation, specify either the types or both the names and types of its formal parameters. The following example specifies the types:

=> SELECT EXPORT_OBJECTS('','raiseXY(int, int)');
    EXPORT_OBJECTS
----------------------

CREATE PROCEDURE public.raiseXY(x int, y int)
LANGUAGE 'PL/vSQL'
SECURITY INVOKER
AS '
BEGIN
RAISE NOTICE ''x = %'', x;
RAISE NOTICE ''y = %'', y;
-- some processing statements
END
';

SELECT MARK_DESIGN_KSAFE(0);

(1 row)

To export all implementations of the overloaded stored procedure raiseXY, export its parent schema:

=> SELECT EXPORT_OBJECTS('','public');
    EXPORT_OBJECTS
----------------------

...

CREATE PROCEDURE public.raiseXY(x int, y varchar)
LANGUAGE 'PL/vSQL'
SECURITY INVOKER
AS '
BEGIN
RAISE NOTICE ''x = %'', x;
RAISE NOTICE ''y = %'', y;
-- some processing statements
END
';

CREATE PROCEDURE public.raiseXY(x int, y int)
LANGUAGE 'PL/vSQL'
SECURITY INVOKER
AS '
BEGIN
RAISE NOTICE ''x = %'', x;
RAISE NOTICE ''y = %'', y;
-- some processing statements
END
';

SELECT MARK_DESIGN_KSAFE(0);

(1 row)

Known issues and workarounds

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

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

  Workaround
  Rather than:

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

  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;
  $$;