SQL and PL/pgSQL Programming Style Guide: Unterschied zwischen den Versionen

Version vom 1. Juni 2020, 20:18 Uhr

Style guidelines for SQL and in general

• File names in lowercase_with_underscores, encoding UTF-8
• Use Free Online version of the PostgreSQL SQL syntax beautifier pgFormatter
• Indent with 4 spaces (don't use tags)
• Max line-length 80 characters (especially in comments)
• IMPORTANT: Put keywords, datatypes and functions all-lowercase - opposite e.g. to Joe Celko’s - because they are _not_ the main content!
• Else follow Sqlstyle.guide which is compatible with Joe Celko’s SQL Programming Style book

Style guidelines for PL/pgSQL

• Same as above plus (inspired partially by PEP 8):
• Naming conventions:
  • Function names in lowercase_with_underscores (handle ST_-functions insensitive).
  • Function parameter names in lowercase_with_underscores
  • Local variables and functions start with _underscore
  • All-uppercase for CONSTANT_VARIABLES
• Whitespace and newlines:
  • Two blank lines before top-level functions
  • Use blank lines sparingly
  • Spaces around = for assignment and in mathematical operators
  • No spaces around = for default parameter values
  • Multiple statements on the same line are discouraged
• Comments:
  • Use inline comments sparingly & avoid obvious comments
  • Write in whole sentence and in plain and easy-to-follow English
  • Add space after line comment (- a comment); no space for commented-out code (-raise notice)
  • Keep comments up to date - incorrect comments are worse than no comments
  • All "public" functions should have docstrings (""")
  • In docstrings, list each argument on a separate line
  • Docstrings should have a blank line after first line and before the final """

Best Practices for PL/pgSQL

• Add COMMENT ON FUNCTION: see e.g. https://github.com/pgRouting/pgrouting/blob/master/sql/withPoints/withPoints.sql
• Do Unit Testing with PGUnit: https://github.com/adrianandrei-ca/pgunit (minimal introduction from Prof. Keller available).

Learning PL/pgSQL

• Book "PostgreSQL Server Programming", Packt. e.g. "Summary of RETURN SETOF variants" => See PDF ("grey copy")

http://index-of.co.uk/Database/SQL%20Database/PostgreSQL%20Server%20Programming%20-%20Second%20Edition%20by%20Usama%20Dar.pdf

• "Introduction to PL/pgSQL Development", by Jim Mlodgenski from AWS, given at Percona Conference 2019: https://www.percona.com/live/19/sites/default/files/slides/Introduction%20to%20PL_pgSQL%20Development%20-%20FileId%20-%20187790.pdf1.

General:

• How to write an Extension: See https://github.com/dimitri/base36