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

Aus Geoinformation HSR
Wechseln zu: Navigation, Suche
(Die Seite wurde neu angelegt: «== SQL and general == * File names in lowercase_with_underscores, encoding UTF-8 * Use pgFormatter - online http://sqlformat.darold.net/ * Indent with 4 spaces…»)
 
K (→‎Style guidelines for PL/pgSQL)
 
(16 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt)
Zeile 1: Zeile 1:
−
== SQL and general ==
+
== Style guidelines for SQL and in general ==
−
* File names in lowercase_with_underscores, encoding UTF-8  
+
* File names in lowercase_with_underscores, use UTF-8 encoding
−
* Use pgFormatter - online http://sqlformat.darold.net/
+
* Use [http://sqlformat.darold.net/ Free Online version] of the PostgreSQL SQL syntax beautifier [https://github.com/darold/pgFormatter pgFormatter]
−
* Indent with 4 spaces (don't use tags)
+
* Indent with 4 spaces (don't use tabs)
−
* Keywords, datatypes and functions should be all-lowercase (opposite to Joe Celko’s!)
+
* Max line-length 80 characters (especially in comments)
−
* Else follow this guideline https://www.sqlstyle.guide (compatible with Joe Celko’s SQL Programming Style book)
+
* IMPORTANT: '''Put keywords, datatypes and functions all-lowercase''' - opposite e.g. to Joe Celko’s - because they are _not_ the main content!  
 +
* Else follow [https://www.sqlstyle.guide Sqlstyle.guide] which is compatible with Joe Celko’s SQL Programming Style book
  
−
== PL/pgSQL (inspired partially by [https://tandysony.com/2018/02/14/pep-8.html PEP 8]):  
+
== Style guidelines for PL/pgSQL ==
 +
* Same as above plus (inspired partially by [https://tandysony.com/2018/02/14/pep-8.html PEP 8]):  
 
* Naming conventions:
 
* Naming conventions:
 
** Function names in lowercase_with_underscores (handle ST_-functions insensitive).
 
** Function names in lowercase_with_underscores (handle ST_-functions insensitive).
 
** Function parameter names in lowercase_with_underscores
 
** Function parameter names in lowercase_with_underscores
−
** Local variables and functions start with _underscore
 
 
** All-uppercase for CONSTANT_VARIABLES
 
** All-uppercase for CONSTANT_VARIABLES
−
** Max line-length 80 characters (especially in comments)
+
** Local variables and functions start with _underscore (=> tbd.)
 
* Whitespace and newlines:
 
* Whitespace and newlines:
 
** Two blank lines before top-level functions
 
** Two blank lines before top-level functions
Zeile 27: Zeile 28:
 
** In docstrings, list each argument on a separate line
 
** In docstrings, list each argument on a separate line
 
** Docstrings should have a blank line after first line and before the final """
 
** 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).
 +
* Prefer triggers over rules.
 +
 +
== Learning PL/pgSQL ==
 +
* Book "PostgreSQL Server Programming", Packt Publishing.
 +
* "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
 +
 +
 +
[[Kategorie:PostgreSQL]] [[Kategorie:SQL]] [[Kategorie:PL/pgSQL]] [[Kategorie:Programmieren]]

Aktuelle Version vom 1. Juni 2020, 20:43 Uhr

Style guidelines for SQL and in general

  • File names in lowercase_with_underscores, use UTF-8 encoding
  • Use Free Online version of the PostgreSQL SQL syntax beautifier pgFormatter
  • Indent with 4 spaces (don't use tabs)
  • 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
    • All-uppercase for CONSTANT_VARIABLES
    • Local variables and functions start with _underscore (=> tbd.)
  • 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

Learning PL/pgSQL

General:

Von «https://giswiki.hsr.ch/index.php?title=SQL_and_PL/pgSQL_Programming_Style_Guide&oldid=67790»