PL/pgSQL (a.k.a. "language plpgsql") subprograms

PL/pgSQL is a conventional, block-structured, imperative programming language designed to execute in the PostgreSQL server, and by extension in the YSQL server, for the specific purpose of executing SQL statements and dealing with the outcomes that they produce. It executes in the same process as SQL itself. And it uses the same underlying implementation primitives. This has these hugely valuable consequences:

• The identical set of data types, with identical semantics, is available in both top-level SQL and in PL/pgSQL.
• Expression syntax and semantics are identical in both top-level SQL and in PL/pgSQL.
• All of the SQL built-in functions are available, with the same semantics, in PL/pgSQL.

PL/pgSQL's basic syntax conventions and repertoire of simple and compound statements seem to be inspired by Ada. Here are some examples:

• a := b + c;
• return a + d;
• declare... begin... exception... end;
• if... then... elsif... then... else... end if;
• case... when... then... else... end case;

However, PL/pgSQL lacks very many of Ada's features. Here are a couple of notable missing Ada features:

• packages
• the ability to define functions and procedures within declare sections

On the other hand, PL/pgSQL extends Ada with a wealth of language features that target its specific use for implementing user-defined subprograms that are stored in, and that execute within, a RDBMS. Here are some examples:

if some_boolean then
  insert into s.t(v) values(some_local_variable);
end if;

and:

foreach val in array values_array loop
  insert into s.t(v) values(val) returning k into new_k;
  new_ks_array := new_ks_array||new_k;
end loop;

You choose PL/pgSQL as the implementation language for a user-defined subprogram by including language plpgsql in the subprogram's header. Its Ada-like features make language plpgsql subprograms very much more expressive and generally useful than language sql subprograms. See the example that shows how to insert a master row together with its details rows in the section SQL (a.k.a. "language sql") subprograms. A language sql procedure cannot meet the requirement because it has no mechanism that allows the autogenerated new masters row's primary key value to be used as the new details rows' foreign key value. The language plpgsql procedure manages the task trivially because you can populate a local variable when you insert into the masters table thus:

insert into s.masters(mv) values(new_mv) returning mk into new_mk;

And then you can reference the local variable in the next insert statement, into the details table, thus:

insert into s.details(mk, dv)
select new_mk, u.v
from unnest(dvs) as u(v);

Here's another example procedure that shows various ways to return the result set from a select statement—either directly or by using a loop that allows you to intervene with arbitrary processing. Notice too the difference between so-called static SQL where you write the statement as direct embedded constructs in PL/pgSQL that you fix when at subprogram creation time or as a text value that you can assemble and submit at run time.

create schema s;
create table s.t(k serial primary key, v int not null);
insert into s.t(v) select g.v from generate_series(11, 100, 11) as g(v);

create function s.f(v_min in int, mode in text = 'static qry')
  returns table(val int)
  set search_path = pg_catalog, pg_temp
  security definer
  language plpgsql
as $body$
begin
  case mode
    when 'static qry' then
      return query select v from s.t where v > v_min order by v;

    when 'static loop' then
      declare
        x s.t.v%type not null := 0;
      begin
        for x in (select v from s.t where v > v_min order by v) loop
          val := x + 1;
          return next;
        end loop;
      end;

    when 'dynamic qry' then
      return query execute format('select v from s.%I where v > $1 order by v', 't') using v_min;

    when 'dynamic loop' then
      declare
        x s.t.v%type not null := 0;
      begin
        for x in execute format('select v from s.%I where v > $1 order by v', 't') using v_min loop
          val :=
            case
              when x < 85 then x + 3
              else             x + 7
            end;
          return next;
        end loop;
     end;
  end case;
end;
$body$;

Test the static and the dynamic qry variants first. Take advantage of the default value for the mode formal argument just for the demonstration effect:

select s.f(v_min=>40);
select s.f(v_min=>40, mode=>'dynamic qry');

Each produces the same result, thus:

 44
 55
 66
 77
 88
 99

Next test the static loop variant:

select s.f(v_min=>40, mode=>'static loop');

It produces this result:

  45
  56
  67
  78
  89
 100

Finally, test the dynamic loop variant:

select s.f(v_min=>40, mode=>'dynamic loop');

It produces this result:

  47
  58
  69
  80
  95
 106

create function s.f2(v_min in int)
  returns table(val int)
  set search_path = pg_catalog, pg_temp
  security definer
  language plpgsql
as $body$
begin
  return query execute format('
      select
        case
          when v < 85 then v + 3
          else             v + 7
        end
      from s.%I
      where v > $1
      order by v',
   't') using v_min;
end;
$body$;

select s.f2(v_min=>40);

select s.f(v_min=>40, mode=>'dynamic loop');