Overview of stored procedures in Amazon Redshift - Amazon Redshift
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Overview of stored procedures in Amazon Redshift

Stored procedures are commonly used to encapsulate logic for data transformation, data validation, and business-specific logic. By combining multiple SQL steps into a stored procedure, you can reduce round trips between your applications and the database.

For fine-grained access control, you can create stored procedures to perform functions without giving a user access to the underlying tables. For example, only the owner or a superuser can truncate a table, and a user needs write privileges to insert data into a table. Instead of granting a user privileges on the underlying tables, you can create a stored procedure that performs the task. You then give the user privileges to run the stored procedure.

A stored procedure with the DEFINER security attribute runs with the privileges of the stored procedure's owner. By default, a stored procedure has INVOKER security, which means the procedure uses the privileges of the user that calls the procedure.

To create a stored procedure, use the CREATE PROCEDURE command. To run a procedure, use the CALL command. Examples follow later in this section.

Note

Some clients might display the following error when creating an Amazon Redshift stored procedure.

ERROR: 42601: [Amazon](500310) unterminated dollar-quoted string at or near "$$

This error occurs due to the inability of the client to correctly parse the CREATE PROCEDURE statement with semicolons delimiting statements and with dollar sign ($) quoting. This results in only a part of the statement sent to the Amazon Redshift server. You can often work around this error by using the Run as batch or Execute selected option of the client.

For example, when using an Aginity client, use the Run entire script as batch option. When you use SQL Workbench/J, we recommend version 124. When you use SQL Workbench/J version 125, consider specifying an alternate delimiter as a workaround.

CREATE PROCEDURE contains SQL statements delimited with a semicolon (;). Defining an alternate delimiter such as a slash (/) and placing it at the end of the CREATE PROCEDURE statement sends the statement to the Amazon Redshift server for processing. Following is an example.

CREATE OR REPLACE PROCEDURE test()
AS $$
BEGIN
  SELECT 1 a;
END;
$$
LANGUAGE plpgsql
;
/

For more information, see Alternate delimiter in the SQL Workbench/J documentation. Or use a client with better support for parsing CREATE PROCEDURE statements, such as the query editor in the Amazon Redshift console or TablePlus.

Topics

The following example shows a procedure with no output arguments. By default, arguments are input (IN) arguments.

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar)
AS $$
BEGIN
  RAISE INFO 'f1 = %, f2 = %', f1, f2;
END;
$$ LANGUAGE plpgsql;

call test_sp1(5, 'abc');
INFO: f1 = 5, f2 = abc
CALL

Note

When you write stored procedures, we recommend a best practice for securing sensitive values:

Don't hardcode any sensitive information in stored procedure logic. For example, don't assign a user password in a CREATE USER statement in the body of a stored procedure. This poses a security risk, because hardcoded values can be recorded as schema metadata in catalog tables. Instead, pass sensitive values, such as passwords, as arguments to the stored procedure, by means of parameters.

For more information about stored procedures, see CREATE PROCEDURE and Creating stored procedures in Amazon Redshift. For more information about catalog tables, see System catalog tables.

The following example shows a procedure with output arguments. Arguments are input (IN), input and output (INOUT), and output (OUT).

CREATE OR REPLACE PROCEDURE test_sp2(f1 IN int, f2 INOUT varchar(256), out_var OUT varchar(256))
AS $$
DECLARE
  loop_var int;
BEGIN
  IF f1 is null OR f2 is null THEN
    RAISE EXCEPTION 'input cannot be null';
  END IF;
  DROP TABLE if exists my_etl;
  CREATE TEMP TABLE my_etl(a int, b varchar);
    FOR loop_var IN 1..f1 LOOP
        insert into my_etl values (loop_var, f2);
        f2 := f2 || '+' || f2;
    END LOOP;
  SELECT INTO out_var count(*) from my_etl;
END;
$$ LANGUAGE plpgsql;


call test_sp2(2,'2019');

         f2          | column2
---------------------+---------
 2019+2019+2019+2019 | 2
(1 row)