PostgreSQL Views and Functions

The functions and views published in this repository are some of those, which I regularly need in my daily job.

As these procedures are sort of essential, at least to me, I install them in the public schema. That way they are available for everyone without having to know a schema name.

All functions and views are covered by tests. The tests are done in simple SQL statements. Each test does raise division by zero if it fails.

The tests have been done with PostgreSQL 12 up to 17.

All examples have been tested with PostgreSQL 17, differences in the behavior of previous versions are noted.

Repositories

The main repository is now on GitLab. A mirror will stay on GitHub.

If you discover any issue, please file them on https://gitlab.com/sjstoelting/pgsql-tweaks/-/issues.

Building the extension

Each function and test is organized into its own separate file for easy management. The shell script create_sql.sh is responsible for assembling these files into the appropriate SQL scripts needed for extension creation.

Configuration

Copy the example configuration file build.cfg.example to build.cfg. Modify build.cfg to include the database connection information of your environment.

Running the Script

The script automatically generates the necessary SQL scripts by combining the function and test files. It is assumed that you have a .pgpass file in your home directory, containing the database credentials matching the configuration. This file simplifies the login process during script execution. Otherwise you have to login with credentials for each execution.

The test results are generated with messages and captions in your local language settings. In addition the test/sql/out/pgsql-tweaks-test-[version number].out file has timing on, you see the execution time for each test.

Releasing

The script accepts an optional argument to control whether a zip file for PGXN (PostgreSQL Extension Network) is created. PGXN is also the prefered installation method.

You have to pass 'y' as the first argument to the script. The language of the test results will be in English UTF8 en_EN. In addition a zip archive is generated ready for submission to PGXN.

The test/sql/out/pgsql-tweaks-test-[version number].out file is generated without timing, otherwise the test results would not be comparable.

Example:

./create_sql.sh y

This will create a zip file of the extension in the $HOME/tmp directory with the pattern pgsql-tweaks-[version number].zip. When the directory $HOME/tmp does not exist, it is created by the scirpt. If no argument is provided, or if the argument is not 'y', the PGXN zip file is not created.

Installation

You may either, install all functions as a package, or install single functions of your choice.

Install the package from source

Get the source by either, download the code as ZIP file, or by git clone.

cd pgsql_tweaks
make install

Afterwards you are able to create the extension in a database:

CREATE EXTENSION pgsql_tweaks;

Install the package with PGXN

pgsql_tweaks is now available over the PostgreSQL extension management, PGXN.

The installation is done with the PGXN installer and it is the prefered installation.

pgxn install pgsql_tweaks

Afterwards you are able to create the extension in a database:

CREATE EXTENSION pgsql_tweaks;

Testing

When your local language is not set LC_MESSAGES to en_EN, you have to execute the the test in the installation directory with an additional parameter to have comparable resutls.

LC_MESSAGES=en_EN psql -f test/sql/out/pgsql-tweaks-[version number].sql > pgsql-tweaks-test-[version number].out
diff pgsql-tweaks-[version number].out test/sql/out/pgsql-tweaks-test-[version number].out

Table of content

1 List of functions

1.1 Functions to check data types
1.1.1 FUNCTION is_date
1.1.2 FUNCTION is_time
1.1.3 FUNCTION is_timestamp
1.1.4 FUNCTION is_real
1.1.5 FUNCTION is_double_precision
1.1.6 FUNCTION is_numeric
1.1.7 FUNCTION is_bigint
1.1.8 FUNCTION is_integer
1.1.9 FUNCTION is_smallint
1.1.10 FUNCTION is_boolean
1.1.11 FUNCTION is_json
1.1.12 FUNCTION is_jsonb
1.1.13 FUNCTION is_empty or is_empty_b
1.1.14 FUNCTION is_hex

1.2 Functions about encryption
1.2.1 FUNCTION sha256

1.3 Functions and views to get extended system information
1.3.1 FUNCTION pg_schema_size
1.3.2 VIEW pg_db_views
1.3.3 VIEW pg_foreign_keys
1.3.4 VIEW pg_functions
1.3.4 VIEW pg_active_locks
1.3.5 VIEW pg_table_matview_infos
1.3.6 VIEW pg_object_ownership
1.3.7 VIEW pg_partitioned_tables_infos

1.4 Functions about encodings
1.4.1 FUNCTION is_encoding
1.4.2 FUNCTION is_latin1
1.4.3 FUNCTION return_not_part_of_latin1
1.4.4 FUNCTION replace_latin1
1.4.4.1 replace_latin1(s text)
1.4.4.2 replace_latin1(s text, replacement text)
1.4.4.3 replace_latin1(s text, s_search text[], s_replace text[])
1.4.5 FUNCTION return_not_part_of_encoding
1.4.6 FUNCTION replace_encoding
1.4.6.1 replace_encoding(s text, e text)
1.4.6.2 replace_encoding(s text, e text, replacement text)
1.4.6.3 replace_encoding(s text, s_search text[], s_replace text[])

1.5 User defined aggregates
1.5.1 AGGREGATE gap_fill
1.5.2 AGGREGATE array_min
1.5.3 AGGREGATE array_max
1.5.4 AGGREGATE array_avg
1.5.5 AGGREGATE array_sum

1.6 Format functions
1.6.1 FUNCTION date_de
1.6.2 FUNCTION datetime_de

1.7 Conversion functions
1.7.1 FUNCTION to_unix_timestamp
1.7.2 FUNCTION hex2bigint

1.8 Other functions
1.8.1 FUNCTION array_trim

List of functions

Functions to check data types

FUNCTION is_date

The function checks strings for being a date.
You might pass a second parameter to use a format string. Without the format, the default format of PostgreSQL is used.

There has been a behaviour change in PostgreSQL 10. A conversion is now handled strict, as in previous versions the conversion tried to calculate dates.

Example PostgreSQL < 10

SELECT is_date('31.02.2018', 'DD.MM.YYYY') AS res;
-- Result is true because the conversion would return a valid result for
SELECT to_date('31.02.2018', 'DD.MM.YYYY');

Result PostgreSQL 9.6 and previous versions:

to_date
2018-03-03

Examples

/**
 * Parameter is in PostgreSQL default format
 */
SELECT is_date('2018-01-01') AS res;

Result:

res
t

SELECT is_date('2018-02-31') AS res;

Result:

res
f

/**
 * Parameter is in PostgreSQL German format
 */
SELECT is_date('01.01.2018', 'DD.MM.YYYY') AS res;

Result:

res
t

SELECT is_date('31.02.2018', 'DD.MM.YYYY') AS res;

Result:

res
f

FUNCTION is_time

The function checks strings for being a time.
You might pass a second parameter to use a format string. Without the format, the default format of PostgreSQL is used.

There has been a behaviour change in PostgreSQL 10. A conversion is now handled strict, as in previous versions the conversion tried to calculate time.

Example PostgreSQL < 10

SELECT is_time('25.33.55,456574', 'HH24.MI.SS,US') AS res;
-- Result is true because the conversion would return a valid result for
SELECT to_timestamp('25.33.55,456574', 'HH24.MI.SS,US')::TIME;

Result PostgreSQL 9.6 and previous versions:

to_timestamp
01:33:55.456574

Examples

/**
 * Parameter is in PostgreSQL default format
 */
SELECT is_time('14:33:55.456574') AS res;

Result:

res
t

SELECT is_time('25:33:55.456574') AS res;

Result:

res
f

/**
 * Parameter is some time format
 */
SELECT is_time('14.33.55,456574', 'HH24.MI.SS,US') AS res;

Result:

res
t

SELECT is_time('25.33.55,456574', 'HH24.MI.SS,US') AS res;

Result:

res
f

FUNCTION is_timestamp

The function checks strings for being a timestamp.
You might pass a second parameter to use a format string. Without the format, the default format of PostgreSQL is used.

There has been a behaviour change in PostgreSQL 10. A conversion is now handled strict, as in previous versions the conversion tried to calculate a date.

Example PostgreSQL < 10

SELECT is_timestamp('2018-01-01 25:00:00') AS res;
-- Result is true because the conversion would return a valid result for
SELECT to_timestamp('01.01.2018 25:00:00', 'DD.MM.YYYY HH24.MI.SS')::TIMESTAMP;

Result PostgreSQL 9.6 and previous versions:

to_timestamp
2018-01-02 01:00:00

Examples

/**
 * Parameter is in PostgreSQL default format
 */
SELECT is_timestamp('2018-01-01 00:00:00') AS res;

Result:

res
t

SELECT is_timestamp('2018-01-01 25:00:00') AS res;
-- Result is false in PostgreSQL >= 10

Result:

res
f

/**
 * Parameter is in PostgreSQL German format
 */
SELECT is_timestamp('01.01.2018 00:00:00', 'DD.MM.YYYY HH24.MI.SS') AS res;

Result:

res
t

SELECT is_timestamp('01.01.2018 25:00:00', 'DD.MM.YYYY HH24.MI.SS') AS res;

Result:

res
f

FUNCTION is_real

The function checks strings for being of data type REAL.

Examples

SELECT is_real('123.456') AS res;

Result:

res
t

SELECT is_real('123,456') AS res;
-- Result is false

Result:

res
f

FUNCTION is_double_precision

The function checks strings for being of data type DOUBLE PRECISION.

Examples

SELECT is_double_precision('123.456') AS res;

Result:

res
t

SELECT is_double_precision('123,456') AS res;
-- Result is false

Result:

res
f

FUNCTION is_numeric

The function checks strings for being of data type NUMERIC.

Examples

SELECT is_numeric('123') AS res;

Result:

res
t

SELECT is_numeric('1 2') AS res;
-- Result is false

Result:

res
f

FUNCTION is_bigint

The function checks strings for being of data type BIGINT.

Examples

SELECT is_bigint('3243546343') AS res;
-- Result is true

Result:

res
t

SELECT is_bigint('123.456') AS res;
-- Result is false

FUNCTION is_integer

The function checks strings for being of data type INTEGER.

Examples

SELECT is_integer('123') AS res;
-- Result is true

Result:

res
t

SELECT is_integer('123.456') AS res;
-- Result is false

FUNCTION is_smallint

The function checks strings for being of data type SMALLINT.

Examples

SELECT is_smallint('123') AS res;
-- Result is true

Result:

res
t

SELECT is_smallint('123.456') AS res;
-- Result is false

Result:

res
f

FUNCTION is_boolean

The function checks a string variable for containing valid BOOLEAN values.

boolean strings
t
f
T
F
y
n
Y
N
true
false
TRUE
FALSE
yes
no
YES
NO
0
1

Examples

SELECT is_boolean('t') AS res;
-- Result is true

Result:

res
t

SELECT is_boolean('F') AS res;
-- Result is true

Result:

res
t

SELECT is_boolean('True') AS res;
-- Result is true

Result:

res
t

SELECT is_boolean('False');
-- Result is true

Result:

res
t

SELECT is_boolean('0') AS res;
-- Result is true

Result:

res
t

SELECT is_boolean('1') AS res;
-- Result is true

Result:

res
t

SELECT is_boolean('-1') AS res;
-- Result is false

Result:

res
f

FUNCTION is_json

The function checks a string variable for containing a valid JSON.

Examples

SELECT is_json('{"review": {"date": "1970-12-30", "votes": 10, "rating": 5, "helpful_votes": 0}, "product": {"id": "1551803542", "group": "Book", "title": "Start and Run a Coffee Bar (Start & Run a)", "category": "Business & Investing", "sales_rank": 11611, "similar_ids": ["0471136174", "0910627312", "047112138X", "0786883561", "0201570483"], "subcategory": "General"}, "customer_id": "AE22YDHSBFYIP"}') AS res;

Result:

res
t

SELECT is_json('Not a JSON') AS res;

Result:

res
f

FUNCTION is_jsonb

The function checks a string variable for containing a valid JSONB.

Example

SELECT is_jsonb('{"review": {"date": "1970-12-30", "votes": 10, "rating": 5, "helpful_votes": 0}, "product": {"id": "1551803542", "group": "Book", "title": "Start and Run a Coffee Bar (Start & Run a)", "category": "Business & Investing", "sales_rank": 11611, "similar_ids": ["0471136174", "0910627312", "047112138X", "0786883561", "0201570483"], "subcategory": "General"}, "customer_id": "AE22YDHSBFYIP"}') AS res;

Result:

res
t

SELECT is_jsonb('Not a JSONB') AS res;

Result:

res
f

FUNCTION is_empty or is_empty_b

The function checks a string variable for being either, NULL or '' and is returing a boolean result.

The extension pgtap has the same name for a function with the same purpose, but a different returning value.
Therefore the function name differs, if pgtap is installed.

The function is_empty, when pgtap is not installed.

The function is_empty_b, when pgtap is not installed. The b stands for its boolean result.

Examples

SELECT is_empty('abc') AS res;
-- Result is false

Result:

res
f

SELECT is_empty('') AS res;
-- Result is true

Result:

res
t

SELECT is_empty(NULL) AS res;
-- Result is true

Result:

res
t

FUNCTION is_hex

The function checks a string variable for being a hexadecimal number being a bigint.

❗This function needs to have hex2bigint being installed!❗

If you use the package, both functions are installed in the correct sort order.

Examples

SELECT is_hex('a1b0') AS res;
-- Result is true

Result:

res
t

SELECT is_hex('a1b0w') AS res;
-- Result is false

Result:

res
f

SELECT is_hex('a1b0c3c3c3c4b5d3') AS res;
-- Result is false (does not fit into a bigint)

Result:

res
f

Functions about encryption

FUNCTION sha256

Creates a function which returns a SHA256 hash for the given string.
The parameter has to be converted into a binary string of bytea.
❗The function needs the pgcrypto package❗

❗This function has an external dependency and is only installed, if the package pgcrypto is installed❗

Example

SELECT sha256('test-string'::bytea) AS res;

Result:

res
ffe65f1d98fafedea3514adc956c8ada5980c6c5d2552fd61f48401aefd5c00e

Functions and views to get extended system information

FUNCTION pg_schema_size

The function returns the size for schema given as parameter in bytes.

Examples

-- Returns the size of the schema public in bytes
SELECT pg_schema_size('public');

Result:

pg_schema_size
348536832

-- Returns the size of the schema public formatted
SELECT pg_size_pretty(pg_schema_size('public'));

Result:

pg_schema_size
332 MB

VIEW pg_db_views

Creates a view to get all views of the current database but excluding system views and all views which do start with "pg" or "_pg".

SELECT *
FROM pg_db_views;

view_catalog	view_schema	view_name	view_definition
chinook	public	v_json_artist_data	WITH tracks AS (
			SELECT "Track"."AlbumId" AS album_id,
			"Track"."TrackId" AS track_id,
			"Track"."Name" AS track_name,
			"Track"."MediaTypeId" AS media_type_id,
			"Track"."Milliseconds" AS milliseconds,
			"Track"."UnitPrice" AS unit_price
			FROM "Track"
			), json_tracks AS (
			SELECT row_to_json(tracks.*) AS tracks
			FROM tracks
			), albums AS (
			SELECT a."ArtistId" AS artist_id,
			a."AlbumId" AS album_id,
			a."Title" AS album_title,
			array_agg(t.tracks) AS album_tracks
			FROM ("Album" a
			JOIN json_tracks t ON ((a."AlbumId" = ((t.tracks ->> 'album_id'::text))::integer)))
			GROUP BY a."ArtistId", a."AlbumId", a."Title"
			), json_albums AS (
			SELECT albums.artist_id,
			array_agg(row_to_json(albums.*)) AS album
			FROM albums
			GROUP BY albums.artist_id
			), artists AS (
			SELECT a."ArtistId" AS artist_id,
			a."Name" AS artist,
			jsa.album AS albums
			FROM ("Artist" a
			JOIN json_albums jsa ON ((a."ArtistId" = jsa.artist_id)))
			)
			SELECT (row_to_json(artists.*))::jsonb AS artist_data
			FROM artists;

VIEW pg_foreign_keys

Creates a view to get a list of foreign keys in the database. That includes the check for an existing single index, see boolean result of column "is_indexed".

Below PostgreSQL 11 the column "enforced" is not available and therefore not part of the result.

SELECT *
FROM pg_foreign_keys;

constraint_name	is_deferrable	initially_deferred	enforced	table_schema	table_name	column_name	foreign_table_schema	foreign_table_name	foreign_column_name	is_indexed
FK_AlbumArtistId	NO	NO	YES	public	Album	ArtistId	public	Artist	ArtistId	true
FK_CustomerSupportRepId	NO	NO	YES	public	Customer	SupportRepId	public	Employee	EmployeeId	true
FK_EmployeeReportsTo	NO	NO	YES	public	Employee	ReportsTo	public	Employee	EmployeeId	true
FK_InvoiceCustomerId	NO	NO	YES	public	Invoice	CustomerId	public	Customer	CustomerId	true
FK_InvoiceLineInvoiceId	NO	NO	YES	public	InvoiceLine	InvoiceId	public	Invoice	InvoiceId	true
FK_InvoiceLineTrackId	NO	NO	YES	public	InvoiceLine	TrackId	public	Track	TrackId	true
FK_PlaylistTrackPlaylistId	NO	NO	YES	public	PlaylistTrack	PlaylistId	public	Playlist	PlaylistId	true
FK_PlaylistTrackTrackId	NO	NO	YES	public	PlaylistTrack	TrackId	public	Track	TrackId	true
FK_TrackAlbumId	NO	NO	YES	public	Track	AlbumId	public	Album	AlbumId	true
FK_TrackGenreId	NO	NO	YES	public	Track	GenreId	public	Genre	GenreId	true
FK_TrackMediaTypeId	NO	NO	YES	public	Track	MediaTypeId	public	MediaType	MediaTypeId	true

VIEW pg_functions

Creates a view to get all functions of the current database, excluding those in the schema pg_catalog and information_schema.

As there have been changes to the system tables used in this view, there are now two scripts dependend on the PostgreSQL version on which it has to be used, one for PostgreSQL 11 or newer and one for PostgreSQL 10 or older. This is handled in the script that creates the view.

SELECT *
FROM pg_functions;

schema_name	function_name	returning_data_type	parameters	function_type	function_comment
public	date_de	character varying	d date	function	Creates a function which returns the given date in German format
public	datetime_de	character varying	t timestamp without time zone	function	Creates a function which returns the given timestamp in German format

VIEW pg_active_locks

Creates a view to view all live locks with all necessary information about the connections and the query.
The view needs PostgreSQL 9.2 as minimum version. The column application_name was added in 9.2.

SELECT *
FROM pg_active_locks;

Result:

pid	state	datname	usename	application_name	client_addr	query_start	wait_event_type	wait_event	locktype	mode	query
8872	active	chinook	stefanie	psql	127.0.0.1	2018-02-18 14:45:53.943047+01			relation	AccessShareLock	SELECT * FROM pg_active_locks;
8872	active	chinook	stefanie	psql	127.0.0.1	2018-02-18 14:45:53.943047+01			virtualxid	ExclusiveLock	SELECT * FROM pg_active_locks;

VIEW pg_table_matview_infos

Creates a view with information about the size of the table/materialized view and sizes of indexes on that table/materialized view. It does also list all indexes on that table in an array.

List of supported object types

object type	<=10	>=11
PARTITIONED INDEX		X
SEQUENCE	X	X
COMPOSITE TYPE	X	X
FOREIGN TABLE	X	X
INDEX	X	X
MATERIALIZED VIEW	X	X
PARTITIONED TABLE	X	X
TABLE	X	X
VIEW	X	X
DATABASE	X	X
EXTENSION	X	X
FOREIGN DATA WRAPPER	X	X
FOREIGN SERVER	X	X
LANGUAGE	X	X
SCHEMA	X	X
OPERATOR CLASS		X
PROCEDURE		X
AGGREGATE FUNCTION		X
WINDOW FUNCTION		X
COLLATION	X	X
CONVERSION	X	X
EVENT TRIGGER	X	X
OPERATION FAMILY	X	X
PUBLICATIONS	X	X

SELECT *
FROM pg_table_matview_infos;

Result:

type	schemaname	tablename	tableowner	tablespace	indexes	table_size	indexes_size	total_relation_size	table_size_pretty	indexes_size_pretty	total_relation_size_pretty
table	public	MediaType	stefanie	[NULL]	{PK_MediaType}	8192	16384	24576	8192 bytes	16 kB	24 kB
table	public	Playlist	stefanie	[NULL]	{PK_Playlist}	8192	16384	24576	8192 bytes	16 kB	24 kB

VIEW pg_object_ownership

Creates a view with information about the ownership of objects. Since PostgreSQL 11 supports procedures, therefore there is one version vor PostgreSQL 10. This view is supported in PostgreSQL 10 or newer. Older versions are not supported.

SELECT *
FROM pg_object_ownership
WHERE owner = 'stefanie';

Result:

oid	object_schema	object_name	owner	object_type	deptype	dependency_type
17078	public	pg_object_ownership	stefanie	VIEW	n	DEPENDENCY_NORMAL
17079	public	gapfillinternal	stefanie	FUNCTION	n	DEPENDENCY_NORMAL
18028	public	gapfill	stefanie	AGGREGATE FUNCTION	n	DEPENDENCY_NORMAL
18039	public	to_unix_timestamp	stefanie	FUNCTION	n	DEPENDENCY_NORMAL
18068	public	to_unix_timestamp	stefanie	FUNCTION	n	DEPENDENCY_NORMAL

VIEW pg_partitioned_tables_infos

Creates a view to get information about partitioned tables. Since PostgreSQL 10 supports partitions, but they became usable only in later versions. A system table to identify partitions has been added in PostgreSQL 11. Therefore this view is only available on systems with PostgreSQL 11 or newer.

SELECT *
FROM pg_partitioned_tables_infos;

Result:

parent_relid	parent_schemaname	parent_tablename	parent_owner	partition_strategy	count_o_partitions	child_relid	child_schemaname	child_tablename	child_owner	child_size
16389	test	parted	stefanie	LIST	3	16396	test	parted_part_1	stefanie	0
16389	test	parted	stefanie	LIST	3	16406	test	parted_part_2	stefanie	0
16389	test	parted	stefanie	LIST	3	16416	test	parted_part_3	stefanie	0
16441	test	parted_test2	stefanie	HASH	0

Functions about encodings

FUNCTION is_encoding

The function checks if all characters are in included in a given encoding. That is especially useful, if you have to deal with exports into other encodings than the database encoding.

The function with two parameters uses UTF-8 as source encoding,
The one with three parameters uses the third parameter as source encoding.

Examples

SELECT is_encoding('Some characters', 'LATIN1') AS res;

Result:

res
f

SELECT is_encoding('Some characters, ğ is Turkish and not latin1', 'LATIN1') AS res;
-- Returns false: The Turkish character ğ is not part of latin1

Result:

res
f

SELECT is_encoding('Some characters', 'LATIN1', 'UTF8') AS res;

Result:

res
t

SELECT is_encoding('Some characters, ğ is Turkish and not latin1', 'UTF8', 'LATIN1') AS res;
-- Returns false: The Turkish character ğ is not part of latin1

Result:

res
f

FUNCTION is_latin1

The function is a shortcut for is_encoding('Some characters', 'LATIN1'), you don't have to give the target encoding.

Examples

SELECT is_latin1('Some characters') AS res;

Result:

res
t

SELECT is_latin1('Some characters, ğ is Turkish and not latin1') AS res;
-- Returns false: The Turkish character ğ is not part of latin1

Result:

res
f

FUNCTION return_not_part_of_latin1

The function returns a distinct array containing all characters, which are not defined in latin1.
The function depends on is_latin1 which is part of this repository.

Example

-- Returns an array containing the characters ğ and Ƶ each one time
SELECT return_not_part_of_latin1('ağbƵcğeƵ') AS res;

Result:

res
{ğ,Ƶ}

FUNCTION replace_latin1

The function has three implementations. All implementations depend on the function is_latin1, the function is included in this repository.

replace_latin1(s text)

The function takes one parameter with characters to be checked and replaced with an empty string, if they are not part of latin1.

Example

SELECT replace_latin1('Some characters, ğ is Turkish and not latin1') AS res;

Result:

res
Some characters, is Turkish and not latin1

replace_latin1(s text, replacement text)

The function takes a second parameter which is used to replace all characters, which are not part of latin1.

Example

SELECT replace_latin1(
  'Some characters, ğ is Turkish and not latin1 and replaced with a g',
  'g'
) AS res;

Result:

res
Some characters, g is Turkish and not latin1 and replaced with a g

replace_latin1(s text, s_search text[], s_replace text[])

The function takes as first parameter a string which may or may not have none latin1 characters. The second parameter is an arrays containing all characters, that should be replaced. The third parameter is an array, too. The characters defined in s_search are replaced with the characters in s_replace, it takes the position in the array to identify which character should be replaced by which one.

Example

-- First identify the characters which should be replaced, which are {ğ,Ƶ}
SELECT return_not_part_of_latin1('ağbƵcğeƵ') AS res;

-- The ğ will be replaced whit a g and the Ƶ with a Z}
SELECT 'ağbƵcğeƵ' AS original
  , replace_latin1(
      'ağbƵcğeƵ',
      string_to_array('ğ,Ƶ', ','),
      string_to_array('g,Z', ',')
    ) AS res;

Result:

original	res
ağbƵcğeƵ	agbZcgeZ

FUNCTION return_not_part_of_encoding

The function returns a distinct array containing all characters, which are not defined in the second parameter as encoding.
The function depends on is_encoding which is part of this repository.

Example

-- Returns an array containing the characters ğ and Ƶ each one time
SELECT return_not_part_of_encoding('ağbƵcğeƵ', 'latin1') AS res;

Result:

res
{ğ,Ƶ}

FUNCTION replace_encoding

The function has three implementations. All implementations depend on the function is_encoding, the function is included in this repository.

replace_encoding(s text, e text)

The function takes one parameter with characters to be checked and replaced with an empty string, if they are not part of the encoding given in the second parameter.

Example

SELECT replace_encoding(
  'Some characters, ğ is Turkish and not latin1',
  'latin1'
) AS res;

Result:

res
Some characters, is Turkish and not latin1

replace_encoding(s text, e text, replacement text)

The function takes a third parameter which is used to replace all characters which are not part of the encoding given in parameter 2.

Example

SELECT replace_encoding(
  'Some characters, ğ is Turkish and not latin1 and replaced with a g',
  'latin1',
  'g'
) AS res;

Result:

res
Some characters, g is Turkish and not latin1 and replaced with a g

replace_encoding(s text, s_search text[], s_replace text[])

The function takes as first parameter a string which may or may not have none latin1 characters. The second parameter is an arrays containing all characters, that should be replaced. The third parameter is an array, too. The characters defined in s_search are replaced with the characters in s_replace, it takes the position in the array to identify which character should be replaced by which one.

Example

-- First identify the characters which should be replaced, which are {ğ,Ƶ}
SELECT return_not_part_of_latin1('ağbƵcğeƵ') AS res;

-- The ğ will be replaced whit a g and the Ƶ with a Z}
SELECT 'ağbƵcğeƵ' AS original
  , replace_encoding(
      'ağbƵcğeƵ',
      string_to_array('ğ,Ƶ', ','),
      string_to_array('g,Z', ',')
  ) AS res;

Result:

original	res
ağbƵcğeƵ	agbZcgeZ

User defined aggregates

AGGREGATE gap_fill

The aggregate is used in Window Functions to show the last value in case the current value is null.

Example

BEGIN;

CREATE TABLE test_gap_fill(id INTEGER, some_value text);

INSERT INTO test_gap_fill(id, some_value) VALUES
  (1, 'value 1'),
  (1, NULL),
  (2, 'value 2'),
  (2, NULL),
  (2, NULL),
  (3, 'value 3')
;

SELECT id
  , some_value
FROM test_gap_fill
;

ROLLBACK;

Result:

id	some_value
1	value 1
1
2	value 2
2
2
3	value 3

BEGIN;

CREATE TABLE test_gap_fill(id INTEGER, some_value text);

INSERT INTO test_gap_fill(id, some_value) VALUES
  (1, 'value 1'),
  (1, NULL),
  (2, 'value 2'),
  (2, NULL),
  (2, NULL),
  (3, 'value 3')
;

-- Fill the empty rows with values
SELECT id
  , gap_fill(some_value) OVER (ORDER BY id) AS some_value
FROM test_gap_fill
;

ROLLBACK;

Result:

id	some_value
1	value 1
1	value 1
2	value 2
2	value 2
2	value 2
3	value 3

AGGREGATE array_min

Calculate minimum values from arrays.

Supported data types are SMALLINT, INTEGER, BIGINT, REAL, DOUBLE PRECISION, NUMERIC, and TEXT;

Examples

SELECT array_min(ARRAY[45, 60, 43, 99]::SMALLINT[]);

Result:

array_min
43

SELECT array_min(ARRAY[45, 60, 43, 99]::INTEGER[]);

Result:

array_min
43

SELECT array_min(ARRAY[45, 60, 43, 99]::BIGINT[]);

Result:

array_min
43

SELECT array_min(ARRAY[45.6, 60.8, 43.7, 99.3]::REAL[]);

Result:

array_min
43.7

SELECT array_min(ARRAY[45.6, 60.8, 43.7, 99.3]::DOUBLE PRECISION[]);

Result:

array_min
43.7

SELECT array_min(ARRAY[45.6, 60.8, 43.7, 99.3]::NUMERIC[]);

Result:

array_min
43.7

SELECT array_min(ARRAY['def', 'abc', 'ghi']::TEXT[]);

Result:

array_min
abc

AGGREGATE array_max

Calculate minimum values from arrays.

Supported data types are SMALLINT, INTEGER, BIGINT, REAL, DOUBLE PRECISION, NUMERIC, and TEXT;

Examples

SELECT array_max(ARRAY[45, 60, 43, 99]::SMALLINT[]);

Result:

array_max
99

SELECT array_max(ARRAY[45, 60, 43, 99]::INTEGER[]);

Result:

array_max
99

SELECT array_max(ARRAY[45, 60, 43, 99]::BIGINT[]);

Result:

array_max
99

SELECT array_max(ARRAY[45.6, 60.8, 43, 99.3]::REAL[]);

Result:

array_max
99.3

SELECT array_max(ARRAY[45.6, 60.8, 43, 99.3]::DOUBLE PRECISION[]);

Result:

array_max
99.3

SELECT array_max(ARRAY[45.6, 60.8, 43, 99.3]::NUMERIC[]);

Result:

array_max
99.3

SELECT array_max(ARRAY['def', 'abc', 'ghi']::TEXT[]);

Result:

array_max
ghi

AGGREGATE array_avg

Calculate average values from arrays.

Supported data types are SMALLINT, INTEGER, BIGINT, REAL, DOUBLE PRECISION, and NUMERIC;

Examples

SELECT array_avg(ARRAY[45, 60, 43, 99]::SMALLINT[]);

Result:

array_avg
62

SELECT array_avg(ARRAY[45, 60, 43, 99]::INTEGER[]);

Result:

array_avg
62

SELECT array_avg(ARRAY[45, 60, 43, 99]::BIGINT[]);

Result:

array_avg
62

SELECT array_avg(ARRAY[45.6, 60.8, 43, 99.3]::REAL[]);

Result:

array_avg
62.1750001907349

SELECT array_avg(ARRAY[45.6, 60.8, 43, 99.3]::DOUBLE PRECISION[]);

Result:

array_avg
62.175

SELECT array_avg(ARRAY[45.6, 60.8, 43, 99.3]::NUMERIC[]);

Result:

array_avg
62.1750000000000000

AGGREGATE array_sum

Calculate sum of values from arrays.

Supported data types are SMALLINT, INTEGER, BIGINT, REAL, DOUBLE PRECISION, and, NUMERIC;

Examples

SELECT array_sum(ARRAY[45, 60, 43, 99]::SMALLINT[]);

Result:

array_sum
247

SELECT array_sum(ARRAY[45, 60, 43, 99]::INTEGER[]);

Result:

array_sum
247

SELECT array_sum(ARRAY[45, 60, 43, 99]::BIGINT[]);

Result:

array_sum
247

SELECT array_sum(ARRAY[45.6, 60.8, 43.7, 99.3]::REAL[]);

Result:

array_sum
249.4

SELECT array_sum(ARRAY[45.6, 60.8, 43.7, 99.3]::DOUBLE PRECISION[]);

Result:

array_sum
249.4

SELECT array_sum(ARRAY[45.6, 60.8, 43.7, 99.3]::NUMERIC[]);

Result:

array_sum
249.4

Format functions

Several countries use different formats for numbers, dates, and timestamps. Therefore I needed some functions, which is easy to remember than the format codes which differ in different programming languages.

German formats

FUNCTION date_de

Creates a function which returns the given date in German format.

Example

SELECT date_de('2018-01-01') AS d_de;

Result:

d_de
01.01.2018

FUNCTION datetime_de

Creates a function which returns the given timestamp in German format.

Example

SELECT datetime_de('2018-01-01 13:30:30 GMT') AS ts_de;

Result:

ts_de
01.01.2018 14:30:30

Conversion functions

FUNCTION to_unix_timestamp

Creates two functions which returns unix timestamp for the a given timestamp or a given timestamp with time zone.

Examples

-- Timestamp without time zone, server uses German / Berlin time zone
SELECT to_unix_timestamp('2018-01-01 00:00:00') AS unix_timestamp;

Result:

unix_timestamp
1514761200

-- Timestamp with time zone
SELECT to_unix_timestamp('2018-01-01 00:00:00+01') AS unix_timestamp;

Result:

unix_timestamp
1514761200

FUNCTION hex2bigint

Creates a functions which returns a hexadecimal number given as text as bigint.

Example

SELECT hex2bigint('a1b0') AS hex_as_bigint;

Result:

hex_as_bigint
41392

Other functions

FUNCTION array_trim

Removes empty strings and null entries from a given array. In addition the function can remove duplicate entries. The function supports strings, numbers, dates, and timestamps with or without time zone.

Examples

-- Untrimmed timestamp array with time zone with duplicates
SELECT array_trim(ARRAY['2018-11-11 11:00:00 MEZ',NULL,'2018-11-11 11:00:00 MEZ']::TIMESTAMP WITH TIME ZONE[]) AS trimmed_array;

Result:

untrimmed_array
{'2018-11-11 11:00:00.000',,'2018-11-11 11:00:00.000'}

-- Timestamp array with time zone with duplicates
SELECT ARRAY['2018-11-11 11:00:00 MEZ',NULL,'2018-11-11 11:00:00 MEZ']::TIMESTAMP WITH TIME ZONE[] AS untrimmed_array;

Result:

trimmed_array
{'2018-11-11 11:00:00.000','2018-11-11 11:00:00.000'}

-- Timestamp array with time zone without duplicates
SELECT array_trim(ARRAY['2018-11-11 11:00:00 MEZ',NULL,'2018-11-11 11:00:00 MEZ']::TIMESTAMP WITH TIME ZONE[], TRUE) AS trimmed_array_distinct;

Result:

trimmed_array_distinct
{'2018-11-11 11:00:00.000'}

Name		Name	Last commit message	Last commit date
Latest commit History 321 Commits
sql		sql
test/sql		test/sql
.gitignore		.gitignore
LICENSE.md		LICENSE.md
META.json		META.json
PGXNREADME.md		PGXNREADME.md
README.md		README.md
build.cfg.example		build.cfg.example
create_html_doc.sh		create_html_doc.sh
create_pgxn_doc.sh		create_pgxn_doc.sh
create_sql.sh		create_sql.sh
help.html		help.html
makefile		makefile
pgsql_tweaks.control		pgsql_tweaks.control

License

minh123viptanvan/pgsql-tweaks

Folders and files

Latest commit

History

Repository files navigation

PostgreSQL Views and Functions

Repositories

Building the extension

Configuration

Running the Script

Releasing

Installation

Install the package from source

Install the package with PGXN

Testing

Table of content

List of functions

Functions to check data types

FUNCTION is_date

Example PostgreSQL < 10

Examples

FUNCTION is_time

Example PostgreSQL < 10

Examples

FUNCTION is_timestamp

Example PostgreSQL < 10

Examples

FUNCTION is_real

Examples

FUNCTION is_double_precision

Examples

FUNCTION is_numeric

Examples

FUNCTION is_bigint

Examples

FUNCTION is_integer

Examples

FUNCTION is_smallint

Examples

FUNCTION is_boolean

Examples

FUNCTION is_json

Examples

FUNCTION is_jsonb

Example

FUNCTION is_empty or is_empty_b

Examples

FUNCTION is_hex

Examples

Functions about encryption

FUNCTION sha256

Example

Functions and views to get extended system information

FUNCTION pg_schema_size

Examples

VIEW pg_db_views

VIEW pg_foreign_keys

VIEW pg_functions

VIEW pg_active_locks

VIEW pg_table_matview_infos

List of supported object types

VIEW pg_object_ownership

VIEW pg_partitioned_tables_infos

Functions about encodings

FUNCTION is_encoding

Examples

FUNCTION is_latin1

Examples

FUNCTION return_not_part_of_latin1

Example

FUNCTION replace_latin1

replace_latin1(s text)

Example

replace_latin1(s text, replacement text)

Example

replace_latin1(s text, s_search text[], s_replace text[])

Example

FUNCTION return_not_part_of_encoding

Example

Packages