PL/pgSQL coding guidelines

Code formatting

These are our formatting guide lines for SQL queries and PL/pgSQL code:

  • Don't use TAB characters for indenting
  • No trailing whitespace please (tabs, spaces, ..)
  • (SQL) verbs are right justified
  • Nouns are left justified
  • (PG/pl)SQL keywords are to be capitalized
  • All other code is to be written in lower case
  • PL/pgSQL blocks to be indented by 4 spaces
  • Line lengths no longer than 80 characters
  • (Try to) keep functions/queries shorter than 60 lines
    when exceeding this number, try to factor out sub queries into Common Table Expressions (CTEs: WITH-clauses)

Identifier naming

  • Double underscores only serve to separate module name prefixes from the rest of the identifier
  • Single underscores separate words in identifiers
  • No camel casing (so, no: UppercaseMultiwordIdentifier)

 

<...>

Stored procedures can be written in SQL and pl/PgSQl stored procedure languages.  Typically SQL is preferred in cases where the number of inputs is not large and the entire procedure can be expressed as a single query or small set of queries.  pl/PgSQL has a number of features very useful for cases where these are not the case.

Regardless of the language, our stored procedure framework requires that certain conventions are followed.  Argument names need to correspond to object properties, prefixed with an in_ to avoid conflicts with table and column names.  These are used by our mapping interface so that interfaces can be dynamically discovered at run-time.  This avoids a number of problems that occur in stored procedure interfaces more generally and the convention needs to be followed.

The features of pl/PgSQL make it possible however to easily write less-than-maintainable code.  In general keep your functions minimalist.  Try to ensure that the function is basically a single large query with helper logic if possible.  If that is not possible try to have a minimalist series of main queries with some extra logic. 

Example code

Error Handling

Documentation

Function naming

Variable naming

Test cases

Secure coding

There are two very specific things to keep in mind when writing pl/PgSQL functions.  The first is that security definer functions can bypass normal security checks.  Sometimes this is desirable (user__change_password, for example, allows any user to change his or her own password, an action normally reserved for superusers).  Often however, we don't want everyone to be able to run such functionality.

Because security definer functions  bypass normal table permissions they are useful when we want to do a narrow action normally prohibited (update a certain table one way only or the like).  However care must be taken to ensure that only the right users can do this.  Generally you want to  rely on one of two things:

  1. SECURITY DEFINER triggers require permission on the base table to operate.  They are generally considered safe.
  2. REVOKE EXECUTE ON FUNCTION .... FROM PUBLIC; will ensure that by default access to the function is denied.  Access can then be added back, as appropriate to the Roles.sql

The second major concern involves dynamic SQL, particularly where utility statements are involved.  Utility statements are not parameterized and so one cannot parameterize them either by calling them directly or by using parameters with EXECUTE.  These statements must be constructed by concatenating strings and then run.  This leads to the possibility of in-procedure SQL injection.

Keep non-parameterized queries to a minimum.  Please flag these functions with appropriate comments (the -- comments, not the COMMENT ON statements) in order to help code reviewers understand that these functions need extra review.

These issues very often occur together.  Most of the time when utility statements are invoked we do so as SECURITY DEFINER, and this means that if an SQL injection issue were to occur in a stored procedure, it would be possible to exploit as the database superuser which would certainly not be good.