SQL and PL/pgSQL Programming Style Guide: Unterschied zwischen den Versionen
Aus Geoinformation HSR
Stefan (Diskussion | Beiträge) K |
Stefan (Diskussion | Beiträge) K (→Style guidelines for PL/pgSQL) |
||
(15 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, | + | * File names in lowercase_with_underscores, use UTF-8 encoding |
− | * Use | + | * 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 | + | * Indent with 4 spaces (don't use tabs) |
− | * | + | * Max line-length 80 characters (especially in comments) |
− | * Else follow | + | * 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 | ||
− | |||
** All-uppercase for CONSTANT_VARIABLES | ** All-uppercase for CONSTANT_VARIABLES | ||
− | ** | + | ** 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
Inhaltsverzeichnis
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
- 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