Example of stored procedure in PostgreSQL 13

Attendance_Add_tr.sql

-- For comparison with
--     https://gist.github.com/DanielLoth/a272a601e38c9c6f2c002956b08d534e

-- See comp.databases.theory,
-- thread: "Stored procedure structure in RDBMS using Lock Manager for transaction isolation"
-- https://groups.google.com/g/comp.databases.theory/c/BNL-TwgMfPY

-- Excerpts about stored procedures in PostgreSQL 13
--
-- From https://www.postgresql.org/docs/current/xproc.html:
--     «CREATE PROCEDURE lacks a RETURNS clause. However, procedures can instead
--     return data to their callers via output parameters.
--     […]
--     A procedure can commit or roll back transactions during its execution (then
--     automatically beginning a new transaction), so long as the invoking CALL
--     command is not part of an explicit transaction block.»
--
-- From  https://www.postgresql.org/docs/current/plpgsql-control-structures.html
--     «A procedure does not have a return value. A procedure can therefore end
--     without a RETURN statement. If you wish to use a RETURN statement to exit
--     the code early, write just RETURN with no expression.»
--
-- From https://www.postgresql.org/docs/current/plpgsql-transactions.html:
--     «In procedures invoked by the CALL command as well as in anonymous
--     code blocks (DO command), it is possible to end transactions using
--     the commands COMMIT and ROLLBACK. A new transaction is started
--     automatically after a transaction is ended using these commands, so
--     there is no separate START TRANSACTION command.»
--
-- From https://www.postgresql.org/docs/current/sql-createprocedure.html:
--     «A CREATE PROCEDURE command is defined in the SQL standard. The PostgreSQL
--     version is similar but not fully compatible.»
--     «A SECURITY DEFINER procedure cannot execute transaction control statements
--     (for example, COMMIT and ROLLBACK […])»


--------------------------------------------------------------------------------
-- DDL
--------------------------------------------------------------------------------

start transaction;

create table Organisation (
  OrganisationId   int                not null,
  OrganisationName varchar(100)       not null,
  CreatedDtm       timestamp          not null default now(),
  UpdatedDtm       timestamp          not null default now(),

  constraint UC_Organisation_PK
     primary key (OrganisationId)
);

create table Person (
  OrganisationId int               not null,
  PersonId       int               not null,
  GivenNames     varchar(50)       not null,
  FamilyName     varchar(50)       not null,
  CreatedDtm     timestamp         not null default now(),
  UpdatedDtm     timestamp         not null default now(),

  constraint UC_Person_PK
     primary key (OrganisationId, PersonId),

  constraint Organisation_Hosts_Person_FK
     foreign key (OrganisationId)
             references Organisation (OrganisationId)
);

create table Attendance (
  OrganisationId int  not null,
  PersonId       int  not null,
  AttendanceDate date not null,

  constraint UC_Attendance_PK
     primary key (OrganisationId, PersonId, AttendanceDate),

  constraint Person_Indicates_Attendance_FK
     foreign key (OrganisationId, PersonId)
             references Person (OrganisationId, PersonId)
);

commit;


--------------------------------------------------------------------------------
-- Stored procedures
--------------------------------------------------------------------------------

create or replace procedure Attendance_Get(
  _OrganisationId int,
  _PersonId       int,
  _AttendanceDate date,
  inout _retval   bool  -- Return value; INOUT because procedures cannot have OUT arguments :(
)
language plpgsql
security invoker
as $$
begin
  set transaction isolation level read committed;

  perform OrganisationId, PersonId, AttendanceDate
    from Attendance
   where OrganisationId = _OrganisationId
     and PersonId       = _PersonId
     and AttendanceDate = _AttendanceDate;

  _retval = found;
end;
$$;

create or replace procedure Attendance_Add_tr(
  _OrganisationId int,
  _PersonId       int,
  _AttendanceDate date,
  inout _retval   int  -- Return value; INOUT because procedures cannot have OUT arguments :(
)
language plpgsql
security invoker -- “security definer” is not compatible with commit/rollback :(
as $$
begin
  ------------------------------------------------------------
  -- Validation block
  ------------------------------------------------------------
  set transaction isolation level read committed;

  if not exists (
    select 1
      from Organisation
     where OrganisationId = _OrganisationId
  ) then
    _retval = 9; -- Organisation does not exist
    return;
  end if;

  if not exists (
    select 1
      from Person
     where OrganisationId = _OrganisationId
       and PersonId       = _PersonId
  ) then
    _retval = 8; -- Person does not exist
    return;
  end if;

  if exists (
    select 1
      from Attendance
     where OrganisationId = _OrganisationId
       and PersonId       = _PersonId
       and AttendanceDate = _AttendanceDate
  ) then
    _retval = 7; -- Attendance on the given date has already been recorded.
    return;
  end if;

  commit;

  ------------------------------------------------------------
  -- Execute block
  ------------------------------------------------------------
  set transaction isolation level serializable;

  perform 1
     from Organisation
    where OrganisationId = _OrganisationId
      for update;

  if not found then
    _retval := 9;
    return;
  end if;

  perform 1
     from Person
    where OrganisationId = _OrganisationId
      and PersonId       = _PersonId
      for update;

  if not found then
    _retval := 8;
    return;
  end if;

  perform 1
     from Attendance
    where OrganisationId = _OrganisationId
      and PersonId       = _PersonId
      and AttendanceDate = _AttendanceDate;

  if found then
    _retval := 7;
    return;
  end if;

  insert into Attendance(OrganisationId, PersonId, AttendanceDate)
  values (_OrganisationId, _PersonId, _AttendanceDate);

  -- From https://www.postgresql.org/docs/11/plpgsql-statements.html
  --
  -- «UPDATE, INSERT, and DELETE statements set FOUND true if at least one row
  -- is affected, false if no row is affected.»
  if found then
    commit;
  else
    rollback;
    _retval := 6; -- Insert failed
    return;
  end if;
end;
$$;


-- Insert some data
insert into organisation(OrganisationId, OrganisationName)
values (1, 'The 20th Century'), (2, 'Victorian');

insert into Person(OrganisationId, PersonId, GivenNames, FamilyName)
values (1, 10, 'Thomas Stearns', 'Eliot'),
       (2, 20, 'Gerard Manley', 'Hopkins');

call Attendance_Add_tr(1, 10, '2021-07-02'::date, 0);
-- ┌─────────┐
-- │ _retval │
-- ├─────────┤
-- │       0 │
-- └─────────┘

call Attendance_Add_tr(1, 10, '2021-07-02'::date, 0);
-- ┌─────────┐
-- │ _retval │
-- ├─────────┤
-- │       7 │
-- └─────────┘

call Attendance_Add_tr(3, 10, '2021-07-02'::date, 0);
-- ┌─────────┐
-- │ _retval │
-- ├─────────┤
-- │       9 │
-- └─────────┘

call Attendance_Add_tr(1, 30, '2021-07-02'::date, 0);
-- ┌─────────┐
-- │ _retval │
-- ├─────────┤
-- │       8 │
-- └─────────┘

call Attendance_Get(1, 10, '2021-07-02'::date, true);
-- ┌─────────┐
-- │ _retval │
-- ├─────────┤
-- │ t       │
-- └─────────┘