![User-defined subprograms and anonymous blocks [YSQL] User-defined subprograms and anonymous blocks [YSQL]](/images/section_icons/api/ysql.png)
User-defined subprograms and anonymous blocks—"language SQL" and "language plpgsql"
This topic area is often referred to as "stored procedures". This is definitely sloppy because, for example:
- Anonymous blocks are not stored.
- The distinct SQL keywords procedure and function express different meanings.
- You often hear "stored procedures" used interchangeably with "stored procedures and triggers". But triggers are their own distinct phenomenon and this major section doesn't mention them.
Moreover, SQL and PL/pgSQL are not the only implementation languages.
This is why the precise, but more longwinded, wording is used for this major section's title and in the explanations that it presents. Nevertheless, when users say "stored procedures" in an informal context, there is very rarely any confusion.
User-defined subprograms
YSQL supports user-defined functions and user-defined procedures. Each of the words function
and procedure
is a YSQL keyword. The term of art subprogram will be used as an umbrella term that denotes either a function or a procedure. It is not a YSQL keyword. A user-defined subprogram has an owner, a name, and lives in a schema. Its source code definition, and its various attributes, are persisted in the catalog.
Not every programming language distinguishes between functions and procedures with different keywords. But the distinction between the two kinds of subprogram is the same in PostgreSQL and YSQL as it is in other languages.
Functions
A function is invoked by writing it as a term within a surrounding expression of arbitrary complexity—and this is the only way to invoke a function. The degenerate case is that the function invocation is the entirety of the expression. You evaluate an expression, in SQL, by writing it as the argument of a bare select
or at one of the many syntax spots in a more complex SQL statement where it's legal to write a placeholder in a prepare
statement. An expression is evaluated in PL/pgSQL source code just as it would be in other languages—as the argument of an explicit or implicit assignment. (Invoking a subprogram using an expression to provide the value for one of its arguments provides an example of implicit assignment).
A function is a syntactic peer of a variable in PL/plSQL or a column in SQL. The overwhelmingly common convention is to name variables and columns with a noun or noun phrase. (It would be very odd to see a variable called get_time.) Stylists argue, therefore, that functions should also be named with a noun or noun phrase to denote the value that invocation produces.
(Notwithstanding this, there are lots of SQL built-in functions with imperative names like generate_series() or gen_random_uuid() rather than, say, generated_series() or generated_random_uuid().)
Procedures
The purpose of a function is to do something. The syntax of create [or replace] procedure
statement therefore does not allow specifying returns
. A procedure can be invoked only as the argument of a call
statement—both in top-level SQL and in PL/pgSQL source code.
Stylists argue, therefore, that procedures should be named with an imperative verb or an imperative verb phrase to denote the action that the invocation performs.
A procedure can have an argument whose mode is inout
. Use this if you want to pass back, say, a success/failure status to the caller. See the subsection Example with 'inout' arguments in the call
statement account.
Procedures were first supported in PostgreSQL Version 11
PostgreSQL Version 10, and earlier versions, did not support procedures. Therefore, the critical distinction explained above was not supported:
- A function is invoked as a term in an expression and names a computed value (and ideally has no side-effects).
- A procedure does something (i.e. its raison d'être is to have side effects) and is invoked using the dedicated
call
statement.
Therefore, in Version 10 and earlier, functions allowed formal arguments with the out
and inout
mode; and the returns
clause was optional. PostgreSQL is duty-bound to allow application code that ran in an older version to work in the same way in a newer version. This means that even in the current version of PostgreSQL, a function can still be used where procedure is the proper choice.
Respect the intended distinction between functions and procedures.
Yugabyte recommends that you ignore the possibility to use a function for the purpose that a procedure is intended by regarding a function'sreturns
clause as mandatory and avoiding the use of out
and inout
arguments.
Invocation syntax
call_procedure
fn_invocation
subprogram_arg
call_procedure ::= CALL subprogram_name ( [ subprogram_arg [ , ... ] ]
)
fn_invocation ::= subprogram_name ( [ subprogram_arg [ , ... ] ] )
subprogram_arg ::= [ arg_name => ] expression
Anonymous blocks
You can also execute a so-called anonymous block. This is a procedure that's defined only by its source code—in other words, has no name and isn't persisted in the catalog. You simply execute it immediately using the do
SQL statement. An anonymous block differs from statements like insert
, update
, and delete
in that it cannot be the object of a prepare
statement. (However, any DML SQL statements that an anonymous block issues are implicitly prepared. And you take advantage of the preparation by repeatedly executing the same do
statement.)
YSQL inherits, from PostgreSQL, the restriction that the implementation language for an anonymous block must be PL/pgSQL; and there are no plans for PostgreSQL to be enhanced to support other languages for anonymous blocks. The defining text of an anonymous block is governed by the grammar for the plpgsql_block_stmt—a particular kind of PL/pgSQL compound statement. Notice that plpgsql_block_stmt denotes a rule in the YSQL Grammar. When the context has established the intended meaning, the prose equivalent "block statement" will be used instead.
This major section, so far, describes only user-defined subprograms and anonymous blocks that are implemented in SQL or PL/plSQL.
A subsection that describes user-defined subprograms that are implemented using C will be added in a later version of this major section.Creating, altering, and dropping subprograms
These are the relevant SQL statements: