Redshift Procedure System Catalog

Question

In Amazon Redshift, the general syntax for creating a procedure is as follows:

```
CREATE [ OR REPLACE ] PROCEDURE sp_procedure_name  
  ( [ [ argname ] [ argmode ] argtype [, ...] ] )
[ NONATOMIC ]
AS $$
  procedure_body
$$ LANGUAGE plpgsql
[ { SECURITY INVOKER | SECURITY DEFINER } ]
[ SET configuration_parameter { TO value | = value } ]
```

I understand that in PostgreSQL, the SET configuration_parameter { TO value | = value } information is stored in the pg_proc table's proconfig column. However, in Redshift, this column is missing.

Where can I find information about the [ NONATOMIC ] option and the SET configuration_parameter { TO value | = value } settings in Amazon Redshift? Are they stored in a different system catalog, or is this information handled differently compared to PostgreSQL?"

Accepted Answer

Currently there is no system table which exposes the NONATOMIC option. So you need access to your code base or privileges to view stored procedure code. There is a feature request to expose this information but it has not gathered much traction yet.

Answer

In Redshift, unlike PostgreSQL where parameter values are stored in a system table, the origin of each parameter value is categorized as "engine-default" or "user," indicating whether it has been altered.

Stored procedures in Redshift do not directly support the NONATOMIC option or the capability to adjust parameters using the SET command. Instead, configuration should be handled through the use of parameter groups, as mentioned earlier. For more information on Redshift parameter groups and an up-to-date list of parameters that can be configured, consult the AWS documentation.

Redshift Procedure System Catalog

Relevant content