Executing stored procedures

If you have EXECUTE privileges on a stored procedure, you can execute it with a CALL statement that specifies the procedure and its IN arguments.

Syntax

CALL stored_procedure_name();

For example, the stored procedure raiseXY() is defined as:

=> CREATE PROCEDURE raiseXY(IN x INT, y VARCHAR) LANGUAGE PLvSQL AS $$
BEGIN
    RAISE NOTICE 'x = %', x;
    RAISE NOTICE 'y = %', y;
    -- some processing statements
END
$$;

CALL raiseXY(3, 'some string');
NOTICE 2005:  x = 3
NOTICE 2005:  y = some string

For more information on RAISE NOTICE, see Errors and diagnostics.

You can execute an anonymous (unnamed) procedure with DO. This requires no privileges:

=> DO $$
BEGIN
    RAISE NOTICE '% ran an anonymous procedure', current_user();
END;
$$;

NOTICE 2005:  Bob ran an anonymous procedure

Transaction semantics

Changes made by stored procedures are automatically committed after successful execution and are rolled back otherwise.

You can also manually commit changes in the middle of a stored procedure. If execution fails after the commit, the committed changes persist even after the automatic rollback.

In this example, manual_commit() inserts two values into a table and then commits. The third insert attempts to insert a CHAR into an INT column, which causes the stored procedure to fail and triggers an automatic rollback. This rollback does not affect the first two inserts because they were manually committed:

=> CREATE TABLE numbers (n INT);

=> CREATE PROCEDURE manualcommit() AS
$$
BEGIN
    PERFORM INSERT INTO numbers VALUES(1);
    PERFORM INSERT INTO numbers VALUES(2);
    PERFORM COMMIT;
    PERFORM INSERT INTO numbers VALUES('a');
END;
$$;

=> CALL manualcommit();

ERROR 3681:  Invalid input syntax for integer: "a"
CONTEXT:  PL/vSQL procedure manualcommit line 6 at static SQL

=> SELECT * FROM numbers;
 n
---
 1
 2
(2 rows)

Session semantics

Operations that modify the session persist after execution of the stored procedure, including ALTER SESSION and SET statements.

Limiting runtime

You can set the maximum runtime of a procedure with session parameter RUNTIMECAP.

This example sets the runtime of all stored procedures to one second for duration of session and runs an anonymous procedure with an infinite loop. Vertica terminates the procedure after it runs for more than one second:

=> SET SESSION RUNTIMECAP '1 SECOND';

=> DO $$
BEGIN
    LOOP
    END LOOP;
END;
$$;

ERROR 0:  Query exceeded maximum runtime
HINT:  Change the maximum runtime using SET SESSION RUNTIMECAP

Execution security and privileges

By default, stored procedures execute with the privileges of the caller (invoker), so callers must have the necessary privileges on the catalog objects accessed by the stored procedure. You can allow callers to execute the procedure with the privileges, default roles, user parameters, and user attributes (RESOURCE_POOL, MEMORY_CAP_KB, TEMP_SPACE_CAP_KB, RUNTIMECAP) of the definer by specifying DEFINER for the SECURITY option.

For example, the following procedure inserts a value into table s1.t1. If the DEFINER has the required privileges (USAGE on the schema and INSERT on table), this requirement is waived for callers.

=> CREATE PROCEDURE insert_into_s1_t1(IN x int, IN y int)
LANGUAGE PLvSQL
SECURITY DEFINER AS $$
BEGIN
    PERFORM INSERT INTO s1.t1 VALUES(x,y);
END;
$$;

A procedure with SECURITY DEFINER effectively executes the procedure as that user, so changes to the database appear to be performed by the procedure's definer rather than its caller.

Caution

Improper use of SECURITY DEFINER can lead to the confused deputy problem and introduce vulnerabilities into your system like SQL injection.

Execution privileges for nested stored procedures

A stored procedure cannot call stored procedures that require additional privileges. For example, if a stored procedure executes with privileges A, B, and C, it cannot call a stored procedure that requires privileges C, D, and E.

For details on nested stored procedures, see Scope and structure.

Examples

In this example, this table:

records(i INT, updated_date TIMESTAMP DEFAULT sysdate, updated_by VARCHAR(128) DEFAULT current_user())

Contains the following content:

=> SELECT * FROM records;
 i |        updated_date        | updated_by
---+----------------------------+------------
 1 | 2021-08-27 15:54:05.709044 | Bob
 2 | 2021-08-27 15:54:07.051154 | Bob
 3 | 2021-08-27 15:54:08.301704 | Bob
(3 rows)

Bob creates a procedure to update the table and uses the SECURITY DEFINER option and grants EXECUTE on the procedure to Alice. Alice can now use the procedure to update the table without any additional privileges:

=> GRANT EXECUTE ON PROCEDURE update_records(int,int) to Alice;
GRANT PRIVILEGE

=> \c - Alice
You are now connected as user "Alice".

=> CALL update_records(99,1);
 update_records
---------------
             0
(1 row)

Because calls to update_records() effectively run the procedure as Bob, Bob is listed as the updater of the table rather than Alice:

=> SELECT * FROM records;
 i  |        updated_date        | updated_by
----+----------------------------+------------
 99 | 2021-08-27 15:55:42.936404 | Bob
  2 | 2021-08-27 15:54:07.051154 | Bob
  3 | 2021-08-27 15:54:08.301704 | Bob
(3 rows)

In this section

Triggers