Try a preview of AI-powered search below!

Subprogram attributes

The overall behavior of a user-defined function or procedure is determined by a set of characteristics that are defined with the create [or replace] and alter statements for each subprogram kind.

• The following characteristics are singletons in the categorization scheme in that they may be set only with create [or replace] and the rules that specify how they are set are not spelled with "attribute":

  • The argument list (i.e. the name, the mode, the data type, and optionally a default expression for each argument). See the arg_decl_with_dflt rule.

  • And, just for a function, the return data type.

• All of the other determining characteristics are known by the term of art attribute. This term is used in the names of rules in the YSQL Grammar for distinct subcategories of attribute, grouped according to when they may be set (only with create [or replace], only with alter, or with both) and to which kind of subprogram they apply (only to functions, or to both functions and procedures).

You can see the names of all of these rules in the grammars for create [or replace] function, create [or replace] procedure, alter function, and alter procedure, below:

• Grammar
• Diagram

create_function ::= CREATE [ OR REPLACE ] FUNCTION subprogram_name ( 
                    [ arg_decl_with_dflt [ , ... ] ] )  
                    { RETURNS data_type
                      | RETURNS TABLE ( { column_name data_type } 
                        [ , ... ] ) }  
                    { unalterable_fn_attribute
                      | alterable_fn_only_attribute
                      | alterable_fn_and_proc_attribute } [ ... ]

create_procedure ::= CREATE [ OR REPLACE ] PROCEDURE subprogram_name ( 
                     [ arg_decl_with_dflt [ , ... ] ] )  
                     { unalterable_proc_attribute
                       | alterable_fn_and_proc_attribute } [ ... ]

alter_function ::= ALTER FUNCTION subprogram_name ( 
                   [ subprogram_signature ] )  
                   { special_fn_and_proc_attribute
                     | { alterable_fn_and_proc_attribute
                         | alterable_fn_only_attribute } [ ... ] 
                       [ RESTRICT ] }

alter_procedure ::= ALTER PROCEDURE subprogram_name ( 
                    [ subprogram_signature ] )  
                    { special_fn_and_proc_attribute
                      | alterable_fn_and_proc_attribute [ ... ] 
                        [ RESTRICT ] }

Here are the different attribute rules.

Unalterable subprogram attributes

The unalterable subprogram attributes can be set only with the create [or replace] statement. Each of function and procedure has its own unalterable attributes rule. They share language and subprogram_implementation. But the status regular function or window function is meaningless for a procedure.

• Grammar
• Diagram

unalterable_fn_attribute ::= WINDOW
                             | LANGUAGE lang_name
                             | AS subprogram_implementation

unalterable_proc_attribute ::= LANGUAGE lang_name
                               | AS subprogram_implementation

Special subprogram attributes

The special subprogram attributes are set using a general syntax style with the alter statements.

• Grammar
• Diagram

special_fn_and_proc_attribute ::= RENAME TO subprogram_name
                                  | OWNER TO 
                                    { role_name
                                      | CURRENT_ROLE
                                      | CURRENT_USER
                                      | SESSION_USER }
                                  | SET SCHEMA schema_name
                                  | [ NO ] DEPENDS ON EXTENSION 
                                    extension_name

The syntax diagram shows that if you want to change any of these attributes, then you must change them one at a time by issuing alter repeatedly.

The schema and the name of a subprogram are set using dedicated explicit syntax with create [or replace]. But the owner cannot be explicitly set with create [or replace]; rather, a new subprogram's owner is implicitly set to what the current_role built-in function returns. (This will be what the session_user built-in function returns if create [or replace] is issued as a top-level SQL statement; and it will be the owner of a security definer subprogram that issues the SQL statement.)

As it happens, and just for PostgreSQL-historical reasons, if you want to specify the extension on which a new subprogram depends you can do this only by first creating the subprogram and then specifying the name of the extension using the subprogram-specific alter statement.

See the section The semantics of the "depends on extension" subprogram attribute for more information about this attribute.

Alterable subprogram attributes

These attributes are common for both functions and procedures:

• Grammar
• Diagram

alterable_fn_and_proc_attribute ::= SET run_time_parameter 
                                    { TO value
                                      | = value
                                      | FROM CURRENT }
                                    | RESET run_time_parameter
                                    | RESET ALL
                                    | [ EXTERNAL ] SECURITY 
                                      { INVOKER | DEFINER }

See the subsection Alterable subprogram attributes for the explanations of the configuration parameter and security attributes.

Alterable function-only attributes

Notice that there are no procedure-specific alterable attributes. These attributes are specific to just functions:

• Grammar
• Diagram

alterable_fn_only_attribute ::= volatility
                                | on_null_input
                                | PARALLEL parallel_mode
                                | [ NOT ] LEAKPROOF
                                | COST int_literal
                                | ROWS int_literal

See the subsection Alterable function-only attributes for the explanations of the volatility, On NULL input, parallel, leakproof, cost and rows attributes.