Established coding standards for pl/pgsql code

coding-standardspostgressqlstored-procedures

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.

Best Answer

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 :-)

Related Solutions

Unit Testing – Coding Standards for Unit Tests

Off the top of my head, I can think of three differences in coding style for test code.

In naming test methods, I follow the pattern of shouldDoSomethingWhenSomeConditionHolds.

Inside the test, it is customary to follow the following spacing pattern:

@Test
shouldReturnAccountBalenceWhenGetBalenceIsCalled() {
    // Some lines 
    // of setup code
    // go here.

    // The action being tested happens after a blank line.

    // An assertion follows another blank line.
}

Some insist on only one assertion per test, but this is far from universal.

The DRY (Don't Repeat Yourself) is less of a consideration in test code than in production code. While some repeated code should be placed in a setUp method or a testUtils class, striving for zero repetition in test code will lead to tightly coupled and inflexible tests, which discourages refactoring.

Can anyone recommend coding standards for TSQL

In my experience the main things I'd look for would be:

Table and column naming - look at whether you use ID, Reference or Number for ID type columns, singular or plurals for names (plurals being common for table names - e.g. THINGS, singular for column names - e.g. THING_ID). For me the most important things here are consistency which avoids people wasting time (for instance you don't run into typos where someone has put THING as a table name because you just know intuitively that table names are never singular).
All creates should include a drop (conditional on the object existing) as part of their file. You might also want to include grant permissions, up to you.
Selects, updates, inserts and deletes should be laid out one column name, one table name and one where clause / order by clause per line so they can be easily commented out one at a time during debugging.
Prefix for object types particularly where they might be confused (so v for view being the most important). Not sure if it still applies but it used to be inefficient for stored procedures other than system procedures to begin sp_. Probably best practice to differentiate them anyway usp_ was what I've used most recently.
A standard indicating how the name of a trigger should include whether it's for update/insert/delete and the table it applies to. I have no preferred standard but this is critical information and must be easy to find.
Standard for ownership of objects in earlier versions of SQL Server or the schema it should exist in for 2005 and later. It's your call what it is but you should never be guessing who owns something/where it lives) and where possible the schema/owner should be included in the CREATE scripts to minimise the possibility of it being created wrongly.
An indicator that anyone using SELECT * will be made to drink a pint of their own urine.
Unless there is a really, really good reason (which does not include laziness on your part), have, enforce and maintain primary key / foreign key relationships from the start. This is after all a relational database not a flat file and orphaned records are going to make your support life hell at some point. Also please be aware that if you don't do it now I can promise you you'll never manage to get it implemented after the event because it's 10 times the work once you have data (which will be a bit screwed because you never enforced the relationships properly).

I'm sure I've missed something but for me they're the ones that actually offer real benefit in a decent number of situations.

But as with all standards, less is more. The longer your coding standards, the less likely people are to read and use them. Once you get past a couple of well spaced pages start looking to drop the stuff that isn't really making a practical difference in the real world because you're just reducing the chance of people doing any of it.

EDIT: two corrections - including schemas in the ownership section, removing an erroneous tip about count(*) - see comments below.

Best Answer

Related Solutions

Unit Testing – Coding Standards for Unit Tests

Can anyone recommend coding standards for TSQL

Related Topic