Contribution Informaion

  1. Stucture
  2. Naming
  3. Header Informaion
  4. Building Informaion
  5. Documentation
    1. Column Documentation of Views
  6. License
  7. Contributor Covenant Code of Conduct
    1. The PostgreSQL Code of Conduct

Stucture

The project has to main directories, on is /sql/, where all funcions and views are stored. The other one is /test/sql/ where all tests are stored.

All source and test files are stored as with the suffix .sql.

Naming

The files a start with a prefix identifying their purposes.

Type Prefix
Aggregate Functions aggregate
Functions function
Monitoring Views view_monitoring
System Informaion Views view_pg
Statistic Views view_statistics

##Tests

The tests are pure SQL, errors are raised by a division by zero.

Each function or view has a test function, that checks the objects for existence first.

Functions are testes with all possible parameters, while views are simply executed.

Examples

Functions 

WITH test AS
	(
		SELECT COUNT(*) AS exist
			, 0 AS zero
		FROM pg_catalog.pg_proc
		WHERE proname = 'is_real'
	)
SELECT
	CASE
		WHEN 1 / test.exist = 1 THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

Views 

WITH test AS
	(
		SELECT COUNT(*) AS exist
			, 0 AS zero
		FROM pg_catalog.pg_views
		WHERE viewname = 'monitoring_wal_archiving'
	)
SELECT
	CASE
		WHEN 1 / test.exist = 1 THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

Header Informaion

Each sql file becomes a header with a short description about the function/view and the information about the author and the license.

Examples 

/**
 * Test for view monitoring_wal_archiving
 *
 * Every test does raise division by zero if it failes
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

Building Informaion

The package is build and tests with shell scripts. The main script is create_sql.sh. Each new view or function have to be added twice, once in an array, once as a DROP statement for the uninstall feature.

For further information please read the Developer Informaion.

Documentation

The documentation is hosted on pgsql-tweaks.org, it has a repository of its own on Codeberg.

Please also add a merge request containing the Markdown documentation about the feature you wnat to get merged.

Column Documentation of Views

The following query returns all columns of a view with data types and column descriptions.

SELECT c.column_name
	, c.data_type
	, COALESCE (COL_DESCRIPTION(('"' || c.table_schema || '"."' || c.table_name || '"')::regclass, c.ordinal_position), '') AS column_description
FROM information_schema.columns AS c
INNER JOIN information_schema."tables" AS t
	USING (table_catalog, table_schema, table_name)
INNER JOIN pg_catalog.pg_database AS d
	ON c.table_catalog = d.datname
LEFT OUTER JOIN pg_catalog.pg_shdescription AS de
	ON d."oid" = de.objoid
WHERE c.table_schema = 'pgsql_tweaks'
	AND c.table_name = '{THE NAME OF THE VIEW}'
ORDER BY t.table_type
	, c.table_name
	, c.ordinal_position
;

License

With a merge request you accept that your code will be published under the PostgreSQL License.

Contributor Covenant Code of Conduct

The PostgreSQL Code of Conduct

This project is using the PostgreSQL Code of Conduct in its latest version.