Established coding standards for pl/pgsql code

Question

I need to standardize coding practices for project that compromises, among others, of pl/pgsql database, that has some amount of nontrivial code.

I look for:

• Code formatting guidelines, especially inside procedures.
• Guidelines on what constructs are considered unsafe (if any)
• Naming conventions.
• Code documentation conventions (if this is practiced)

Any hints to documents that define good practices in pl/pgsql code? If not I'm looking for hints to practices that you consider good.

There is related question regarding TSQL: Can anyone recommend coding standards for TSQL?, which is relevant to psql as well, but I need more information on stored procedures.

Other related questions:

• https://stackoverflow.com/questions/1070275/what-indenting-style-do-you-use-in-sql-server-stored-procedures

Answer 1

A few points from our experience both in terms of maintainability and security. A lot of things like code formatting are things that can be done any number of ways as long as you are consistent and you use white space appropriately.

As for what is unsafe from a security perspective the only thing that comes to mind is combining SECURITY DEFINER, EXECUTE, and string interpolation. This leads to the possibility of SQL injection that will run as the owner of the function. This is sometimes necessary (so use quote_ident and quote_literal as appropriate) but when it is, code needs to be additionally reviewed for weaknesses. This comes up when executing DDL inside stored procs, particularly for user management.

One caution however is the possibility of search path playing issues with SECURITY DEFINER functions. You may want to look at this particularly in cases where users are going to have the ability to create relations in other schemas (or even temporary relations). See http://www.postgresql.org/docs/9.2/static/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY

A second thing I prefer to do is to add COMMENT ON FUNCTION .... after each function definition. I think this is preferable to explanatory API comments at the top because it can be pulled up by automatic documentation tools.

A third thing I highly recommend is to try to keep PLPGSQL functions to be in the form of a main db query with small amounts of supporting procedural logic. This helps with both clarity and debugging. The more you can consolidate into a single query the faster the procedure as a whole can be debugged, because there are fewer possible places for any given error to lurk.

Finally one big trap that occurs with user defined functions being called by the application is that the functions often require fixed numbers of arguments, and this can be an issue in terms of maintainability. I would recommend reading this http://ledgersmbdev.blogspot.com/2011/10/introduction-to-soda.html which is an attempt to apply what can be learned from web services to PostgreSQL stored procedures.

My blog, http://ledgersmbdev.blogspot.com, regularly features thoughts and experience on this issue so it may be helpful :-)