PL/pgSQL styleguide

Question

There are a lot of styleguides on the Internet for different languages such as CSS, Javascript, Ruby, Rails etc.

But where can I find find good modern styleguide for procedural language of the PostgreSQL database system - PL/pgSQL?

I would also appreciate automatic code analyzer like a rubocop for ruby.

Answer 1

I found this http://www.sqlstyle.guide:

These guidelines are designed to be compatible with Joe Celko’s SQL Programming Style book to make adoption for teams who have already read that book easier. This guide is a little more opinionated in some areas and in others a little more relaxed. It is certainly more succinct where Celko’s book contains anecdotes and reasoning behind each rule as thoughtful prose.

Answer 2

I'm not aware of any official style guide. (There are coding conventions for Postgres itself but this is for the core code which is written in C.)

That said, do take a look at the style used by core developers in the PGXN source code:

https://github.com/pgxn/pgxn-manager/tree/master/sql

Here's a simple wrapper around an insert statement to illustrate:

CREATE OR REPLACE FUNCTION insert_user(
    nickname   LABEL,
    password   TEXT,
    full_name  TEXT   DEFAULT '',
    email      EMAIL  DEFAULT NULL,
    uri        URI    DEFAULT NULL,
    twitter    CITEXT DEFAULT NULL,
    why        TEXT   DEFAULT NULL
) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
/*
    % SELECT insert_user(
        nickname  := 'theory',
        password  := '***',
        full_name := 'David Wheeler',
        email     := '[email protected]',
        uri       := 'http://justatheory.com/',
        twitter   := 'theory',
        why       := 'Because I’m a bitchin’ Pg developer, yo.'
    );
     insert_user 
    ─────────────
     t
Inserts a new user into the database. The nickname must not already exist or
an exception will be thrown. The password must be at least four characters
long or an exception will be thrown. The status will be set to "new" and the
`set_by` set to the new user's nickname. The other parameters are:
full_name
: The full name of the user.
email
: The email address of the user. Must be a valid email address as verified by
  [Email::Valid](http://search.cpan.org/perldoc?Email::Valid).
uri
: Optional URI for the user. Should be a valid URI as verified by
  [Data::Validate::URI](http://search.cpan.org/perldoc?Data::Validate::URI).
twitter
: Optional Twitter username. Case-insensitive. A leading "@" will be removed.
why
: Optional text from the user explaining why she should be allowed access.
Returns true if the user was inserted, and false if not.
*/
BEGIN
    IF char_length(password) < 4 THEN
       RAISE EXCEPTION 'Password must be at least four characters long';
    END IF;
    INSERT INTO users (
        nickname,
        password,
        full_name,
        email,
        uri,
        twitter,
        why,
        set_by
    )
    VALUES (
        insert_user.nickname,
        crypt(insert_user.password, gen_salt('des')),
        COALESCE(insert_user.full_name, ''),
        insert_user.email,
        insert_user.uri,
        COALESCE(trim(leading '@' FROM insert_user.twitter), ''),
        COALESCE(insert_user.why, ''),
        insert_user.nickname
    );
    RETURN FOUND;
END;
$$;