Try a preview of AI-powered search below!

Syntax

• Grammar
• Diagram

plpgsql_if_stmt ::= IF guard_expression THEN 
                    [ plpgsql_executable_stmt [ ... ] ]  
                    [ plpgsql_elsif_leg [ ... ] ]  
                    [ ELSE [ plpgsql_executable_stmt [ ... ] ] ]  END 
                    IF

plpgsql_elsif_leg ::= { ELSIF | ELSEIF } guard_expression THEN 
                      [ plpgsql_executable_stmt [ ... ] ]

Semantics

The if statement lets you specify one or several lists of executable statements so that a maximum of one of those lists will be selected. Each list is guarded by a boolean guard_expression—and each is tested in turn, in the order in which they are written. When a guard_expression evaluates to false, the point of execution immediately moves to the next guard_expression, skipping the statement list that it guards. As soon as a guard_expression evaluates to true, the statement list that it guards is executed; and on completion of that list, control passes to the first statement after the end if of the if statement—skipping the evaluation of any remaining guard_expressions. This economical testing of the guard_expressions is common to many programming languages. It is a particular example of so-called short-circuit evaluation.

Here is the template of the simplest if statement:

if <guard_expression> then
  <guarded statement list>
end if;

While you're typing in your code, you might like to omit the statement list altogether. Doing so causes neither a syntax error nor a runtime error. The omission is equivalent to the single "null;" statement.

The simplest if statement pattern, above, is very common and useful. The guarded statement list is either executed—or skipped. But consider an example when there are two or more alternative statement lists, like this:

if <guard_expression 1> then
  <guarded statement list 1>
elsif <guard_expression 2> then
  <guarded statement list 2>
end if;

There are two possibilities here:

• either the set of guard_expressions jointly covers all possibilities
• or it might not cover all possibilities.

However, it's generally hard for the reader to work out whether or not all possibilities are covered. Therefore it's hard for the reader to know whether the programmer intended:

• either to handle every possibility explicitly, but simply forgot one or several possibilities
• or to do nothing for the possibilities without an explicit test.

The better approach, therefore, is always to include the bare else leg in an if statement that has two or more guard expressions. The intention is advertised clearly by writing the null statement (or maybe by raising a user-defined exception) in the else leg.

Try this:

\c :db :u
drop schema if exists s cascade;
create schema s;

create function s.f(i in int)
  returns table(z text)
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
begin
  if i < 0 then
    z := 'i < 0'; return next;
  elsif i > 0 then
    z := 'i > 0'; return next;
  elsif i = 0 then
    z := 'i = 0'; return next;
  else
    raise exception using
      errcode := 'YB123',
      message := 'Unexpected. "i" must not be "null".';
  end if;
end;
$body$;

select s.f(1);
select s.f(-1);
select s.f(0);

These are the results. First:

 i > 0

And then:

 i < 0

And then:

 i = 0

Now try the negative test:

select s.f(null);

It causes the user-defined YB123 error:

Unexpected. "i" must not be "null".
CONTEXT:  PL/pgSQL function s.f(integer) line 10 at RAISE

create function s.f_alt(i in int)
  returns table(z text)
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
begin
  case
    when  i < 0 then
      z := 'i < 0'; return next;
    when i > 0 then
      z := 'i > 0'; return next;
    when i = 0 then
      z := 'i = 0'; return next;
  end case;
end;
$body$;

select s.f_alt(1);
select s.f_alt(-1);
select s.f_alt(0);

select s.f_alt(null);

case not found
HINT:  CASE statement is missing ELSE part.
CONTEXT:  PL/pgSQL function s.f_alt(integer) line 3 at CASE

case
  when  i < 0 then
    z := 'i < 0'; return next;
  when i > 0 then
    z := 'i > 0'; return next;
  when i = 0 then
    z := 'i = 0'; return next;
  when i is null then
    <appropriate action for this case>
end case;