PL/pgSQL syntax and semantics

PostgreSQL, and therefore YSQL, natively support both language sql and language plpgsql functions and procedures. But the implementation of a do statement can only be language plpgsql. PL/pgSQL source text is governed by the plpgsql_block_stmt rule. See these sections:

• create function
• create procedure
• do

The syntax diagrams in these three sections show that the PL/pgSQL source text must be enquoted. Yugabyte recommends that, for consistency, you use dollar quoting around the source text and that you spell this as $body$. Notice that PL/pgSQL's dynamic SQL feature lets you write a user-defined procedure that will create a user-defined subprogram. If you take advantage of this, then you'll have to use different enquoting syntax around the source text of the to-be-created subprogram.

This section, and its subsections, specify:

• the grammar of the plpgsql_block_stmt rule
• its decomposition down to terminal rules
• the associated semantics.

• Grammar
• Diagram

plpgsql_block_stmt ::= [ << label >> ]  
                       [ plpgsql_declaration_section ]  
                       plpgsql_executable_section  
                       [ plpgsql_exception_section ] END [ label ] ;

plpgsql_declaration_section ::= DECLARE 
                                [ plpgsql_declaration [ ... ] ]

plpgsql_executable_section ::= BEGIN 
                               [ plpgsql_executable_stmt [ ... ] ]

plpgsql_exception_section ::= EXCEPTION { plpgsql_handler [ ... ] }

The minimal PL/pgSQL source text

The executable section can include a block statement—and this implies the possibility of an arbitrarily deep nesting. It's this that underpins this characterization of PL/pgSQL at the start of this overall section on language plpgsql subprograms:

PL/pgSQL is a conventional, block-structured, imperative programming language [whose] basic syntax conventions and repertoire of simple and compound statements seem to be inspired by Ada.

The executable section is mandatory. This, therefore, is the minimal form of a PL/pgSQL source text:

$body$
begin

end;
$body$;

It's useful to know this because each of create function and create procedure, when it completes without error, inevitably creates a subprogram upon which the execute privilege has already been granted to public. See these tips in the sections that describe these two create statements:

• 'Create function' grants 'execute' to 'public'

• 'Create procedure' grants 'execute' to 'public'

Each tip recommends that you always revoke this privilege immediately after creating a subprogram. However, even this might expose a momentary security risk. Here is the watertight secure approach:

create schema s;

create procedure s.p()
  language plpgsql
as $body$
begin
  null; -- Implementation to follow.
end;
$body$;

revoke execute on procedure s.p() from public;

-- "create or replace" leaves the extant privileges on "s.p" unchanged.
create or replace procedure s.p()
  set search_path = pg_catalog, pg_temp
  security definer
  language plpgsql
as $body$
declare
  -- (Optionally) the intended declarations.
  -- ...

begin
  -- The intended implementation.
  -- ...
exception
  -- (Optionally) the intended handlers.
  -- ...
end;
$body$;

Notice that null; is a legal PL/pgSQL executable statement. Of course, it does nothing at all. You might prefer to write null; explicitly to emphasize your intention. Now you can grant execute on s.p to the role(s) that you intend.

Each section is described in a dedicated subsection:

• declaration section

• executable section

• exception section