PostgreSQL 常用视图和函数工具 pgsql-tweaks 的介绍和使用示例

原创小小亮 2022-10-19

1317

本文介绍 PostgreSQL 工具 pgsql-tweaks 的功能和使用示例。

PostgreSQL视图和函数

pgsql-tweaks 存储库中发布的功能和视图是我日常工作中经常需要的一些功能和视图。

由于这些过程是必不可少的，至少对我而言，我将它们安装在公共模式中。这样，每个人都可以使用它们，而无需知道模式名称。

所有功能和视图都包含在测试中。测试是在简单的 SQL 语句中完成的。如果失败，每个测试都会将除数提高为零。

测试已在 PostgreSQL 9.6 和 15 beta 2 上完成。PostgreSQL 10 中的行为发生了一些变化，时间戳和日期函数和转换变得更加严格。

所有示例都已使用 PostgreSQL 15 beta 2 进行了测试，注意到了之前版本的行为差异。

存储库

主存储库现在位于 GitLab上。镜像将留在GitHub 上。

pgsql-tweaks Github地址：https://gitlab.com/sjstoelting/pgsql-tweaks/

如果您发现任何问题，请在 https://gitlab.com/sjstoelting/pgsql-tweaks/issues上提交。

构建扩展

所有功能和测试都位于单个文件中。
扩展文件由 shell 脚本“create-sql.sh”构建。为了能够运行脚本，您需要使用连接信息进行配置。请将“build.cfg.example”复制到“build.cfg”并更改配置以适应您的环境。

该脚本假定您有一个.pgpass文件，其中包含与配置匹配的登录信息。

安装

您可以将所有功能安装为一个包，也可以安装您选择的单个功能。

从源安装包
通过以下方式获取源代码，将代码下载为 ZIP 文件，或通过 git clone。

cd pgsql_tweaks
make install

之后，您可以在数据库中创建扩展：

CREATE EXTENSION pgsql_tweaks;

使用 PGXN 安装包

pgsql_tweaks 现在可以通过 PostgreSQL 扩展管理PGXN 使用。

使用 PGXN 安装程序完成安装。

pgxn install pgsql_tweaks

之后，您可以在数据库中创建扩展：

表中的内容

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

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

功能列表

检查数据类型的功能

FUNCTION is_date

该功能检查字符串是否为日期。
您可以传递第二个参数以使用格式字符串。如果没有格式，则使用 PostgreSQL 的默认格式。

PostgreSQL 10 中的行为发生了变化。现在严格处理转换，因为在以前的版本中，转换试图计算日期。

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.

示例 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');

结果 PostgreSQL 9.6 和以前的版本：

to_date
2018-03-03

举例

/**
 * 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

该功能检查字符串是否为时间。
您可以传递第二个参数以使用格式字符串。如果没有格式，则使用 PostgreSQL 的默认格式。

PostgreSQL 10 中的行为发生了变化。现在严格处理转换，因为在以前的版本中，转换试图计算时间。

示例 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;

结果 PostgreSQL 9.6 和以前的版本：

to_timestamp
01:33:55.456574

举例

/**
 * 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

该功能检查字符串是否为时间戳。
您可以传递第二个参数以使用格式字符串。如果没有格式，则使用 PostgreSQL 的默认格式。

PostgreSQL 10 中的行为发生了变化。现在严格处理转换，因为在以前的版本中，转换试图计算日期。

示例 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;

结果 PostgreSQL 9.6 和以前的版本：

to_timestamp
2018-01-02 01:00:00

举例

/**
 * 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

该功能检查字符串的数据类型是否为 REAL。

示例

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

该功能检查字符串的数据类型是否为 DOUBLE PRECISION。

示例

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

该功能检查字符串是否为 NUMERIC 数据类型。

示例

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

该功能检查字符串是否为 BIGINT 数据类型。

示例

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

该功能检查字符串的数据类型是否为 INTEGER。

示例

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

该功能检查字符串是否属于数据类型 SMALLINT。

示例

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

该功能检查字符串变量是否包含有效的 BOOLEAN 值。

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

示例

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

该功能检查字符串变量是否包含有效的 JSON。

示例

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

该功能检查字符串变量是否包含有效的 JSONB。

示例

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

该功能检查字符串变量是否为 NULL 或 ‘’。

示例

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

该函数检查字符串变量是否为十六进制数，即 bigint。

❗️这个函数需要安装hex2bigint！❗️

如果您使用该软件包，则这两个函数都以正确的排序顺序安装。

示例

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

关于加密的功能

FUNCTION sha256

创建一个函数，该函数返回给定字符串的 SHA256 哈希值。

该参数必须转换为bytea的二进制字符串。❗️该函数需要pgcrypto包❗️

❗️这个函数有一个外部依赖并且只有在安装了包 pgcrypto 的情况下才被安装❗️

示例

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

Result:

res
ffe65f1d98fafedea3514adc956c8ada5980c6c5d2552fd61f48401aefd5c00e

获取扩展系统信息的函数和视图

FUNCTION pg_schema_size

该函数返回作为参数给出的模式的大小（以字节为单位）。

示例

-- 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

查兰 pg_db_views

创建一个视图以获取当前数据库的所有视图，但不包括系统视图和所有以“pg”或“_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;

查看 pg_foreign_keys

创建一个视图以获取数据库中的外键列表。这包括检查现有的单个索引，请参见“is_indexed”列的布尔结果。

在 PostgreSQL 11 以下，“enforced”列不可用，因此不是结果的一部分。

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

查看 pg_functions

创建一个视图以获取当前数据库的所有功能，不包括模式 pg_catalog 和 information_schema 中的功能。

由于此视图中使用的系统表发生了变化，现在有两个脚本依赖于它必须使用的 PostgreSQL 版本，一个用于 PostgreSQL 11 或更高版本，一个用于 PostgreSQL 10 或更早版本。这是在创建视图的脚本中处理的。

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

查看 pg_active_locks

创建一个视图以查看所有活动锁以及有关连接和查询的所有必要信息。

该视图需要 PostgreSQL 9.2 作为最低版本。列 application_name 是在 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;

查看 pg_table_matview_infos

创建一个视图，其中包含有关表/物化视图的大小和该表/物化视图上的索引大小的信息。它还会在数组中列出该表上的所有索引。

支持的对象类型列表

对象类型	<=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

查看 pg_object_ownership

创建一个包含对象所有权信息的视图。由于 PostgreSQL 11 支持过程，因此 PostgreSQL 10 有一个版本。此视图在 PostgreSQL 10 或更高版本中受支持。不支持旧版本。

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

查看 pg_partitioned_tables_infos

创建一个视图以获取有关分区表的信息。由于 PostgreSQL 10 支持分区，但它们仅在以后的版本中可用。PostgreSQL 11 中添加了用于识别分区的系统表。因此，此视图仅适用于 PostgreSQL 11 或更高版本的系统。

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

关于编码的功能

FUNCTION is_encoding

该函数检查是否所有字符都包含在给定的编码中。如果您必须处理导出到数据库编码以外的其他编码，这将特别有用。

带两个参数的函数使用UTF-8作为源编码，
带三个参数的函数使用第三个参数作为源编码。

示例

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

该功能是 is_encoding(‘Some characters’, ‘LATIN1’) 的快捷方式，您不必给出目标编码。

示例

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

该功能返回一个包含所有字符的不同数组，这些字符未在 latin1 中定义。

该功能取决于 is_latin1，它是此存储库的一部分。

示例

-- 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

该功能具有三个实现。所有实现都依赖于函数 is_latin1，该函数包含在这个存储库中。

replace_latin1(s text)

该功能采用一个参数，其中包含要检查的字符并用空字符串替换，如果它们不是 latin1 的一部分。

示例

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)

该功能采用第二个参数，用于替换不属于 latin1 的所有字符。

示例

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[])

该功能将一个字符串作为第一个参数，该字符串可能没有也可能没有 latin1 字符。第二个参数是一个包含所有字符的数组，应该被替换。第三个参数也是一个数组。s_search 中定义的字符被 s_replace 中的字符替换，它在数组中的位置确定哪个字符应该被哪个替换。

示例

-- 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

该功能返回一个包含所有字符的不同数组，这些字符未在第二个参数中定义为编码。
该功能取决于 is_encoding，它是此存储库的一部分。

示例

-- 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

该功能具有三个实现。所有实现都依赖于函数 is_encoding，该函数包含在这个存储库中。

replace_encoding(s text, e text)

如果它们不是第二个参数中给出的编码的一部分，则该函数采用一个带有要检查的字符的参数并将其替换为空字符串。

示例

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)

该功能采用第三个参数，用于替换不属于参数 2 中给出的编码的所有字符。

示例

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[])

该函数将一个字符串作为第一个参数，该字符串可能没有也可能没有 latin1 字符。第二个参数是一个包含所有字符的数组，应该被替换。第三个参数也是一个数组。s_search 中定义的字符被 s_replace 中的字符替换，它在数组中的位置确定哪个字符应该被哪个替换。

示例

-- 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

用户定义的聚合

AGGREGATE gap_fill

聚合在窗口函数中用于显示最后一个值，以防当前值为空。

示例

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

从数组中计算最小值。

支持的数据类型有 SMALLINT、INTEGER、BIGINT、REAL、DOUBLE PRECISION、NUMERIC 和 TEXT；

示例

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

从数组中计算最小值。

支持的数据类型有 SMALLINT、INTEGER、BIGINT、REAL、DOUBLE PRECISION、NUMERIC 和 TEXT；

示例

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

计算数组的平均值。

支持的数据类型有 SMALLINT、INTEGER、BIGINT、REAL、DOUBLE PRECISION 和 NUMERIC；

示例

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

计算数组中值的总和。

支持的数据类型有 SMALLINT、INTEGER、BIGINT、REAL、DOUBLE PRECISION 和 NUMERIC；

示例

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

格式化功能

几个国家/地区对数字、日期和时间戳使用不同的格式。因此我需要一些函数，这些函数比不同编程语言中不同的格式代码更容易记住。

German formats

FUNCTION date_de

创建一个以German formats返回给定日期的函数。

示例

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

Result:

d_de
01.01.2018

FUNCTION datetime_de

创建一个以German formats返回给定时间戳的函数。

示例

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

Result:

ts_de
01.01.2018 14:30:30

转换功能

FUNCTION to_unix_timestamp

创建两个函数，它们返回给定时间戳或带时区的给定时间戳的 unix 时间戳。

示例

-- 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

创建一个函数，该函数返回作为 bigint 文本给出的十六进制数。

示例

SELECT hex2bigint('a1b0') AS hex_as_bigint;

Result:

hex_as_bigint
41392

其它功能

FUNCTION array_trim

从给定数组中删除空字符串和空条目。此外，该功能可以删除重复条目。该函数支持带或不带时区的字符串、数字、日期和时间戳。

示例

-- 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’}

pgsql-tweaks Github地址：https://gitlab.com/sjstoelting/pgsql-tweaks/

原文标题：pgsql-tweaks
原文作者：Stefanie Janine Stölting
原文链接：https://gitlab.com/sjstoelting/pgsql-tweaks/-/blob/main/README.md#function-replace_encoding

postgresql 墨力翻译小小亮的翻译文章

最后修改时间：2022-10-19 14:26:58

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

PostgreSQL 常用视图和函数工具 pgsql-tweaks 的介绍和使用示例

目录

PostgreSQL视图和函数

存储库

构建扩展

安装

使用 PGXN 安装包

表中的内容

功能列表

检查数据类型的功能

FUNCTION is_date

示例 PostgreSQL < 10

举例

FUNCTION is_time

示例 PostgreSQL < 10

举例

FUNCTION is_timestamp

示例 PostgreSQL < 10

举例

FUNCTION is_real

示例

FUNCTION is_double_precision

示例

FUNCTION is_numeric

示例

FUNCTION is_bigint

示例

FUNCTION is_integer

示例

FUNCTION is_smallint

示例

FUNCTION is_boolean

示例

FUNCTION is_json

示例

FUNCTION is_jsonb

示例

FUNCTION is_empty

示例

FUNCTION is_hex

示例

关于加密的功能

FUNCTION sha256

示例

获取扩展系统信息的函数和视图

FUNCTION pg_schema_size

示例

查兰 pg_db_views

查看 pg_foreign_keys

查看 pg_functions

查看 pg_active_locks

查看 pg_table_matview_infos

支持的对象类型列表

查看 pg_object_ownership

查看 pg_partitioned_tables_infos

关于编码的功能

FUNCTION is_encoding

示例

FUNCTION is_latin1

示例

FUNCTION return_not_part_of_latin1

示例

FUNCTION replace_latin1

replace_latin1(s text)

示例

replace_latin1(s text, replacement text)

示例

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

示例

FUNCTION return_not_part_of_encoding

示例

FUNCTION replace_encoding

replace_encoding(s text, e text)

示例

replace_encoding(s text, e text, replacement text)

示例

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

示例

用户定义的聚合

AGGREGATE gap_fill