跳到主要内容

实用函数

标量实用函数​

下面这些函数难以归类到某一特定类别，但在实践中普遍有用。

名称说明
alias(column)返回the name of the column.
can_cast_implicitly(source_value, target_value)Whether or not we can implicitly cast from the types of the source value to the target value.
checkpoint(database)Synchronize WAL with file for (optional) database without interrupting transactions.
coalesce(expr, ...)返回the first expression that evaluates to a non-NULL value. Accepts 1 or more parameters. Each expression can be a column, literal value, function result, or many others.
constant_or_null(arg1, arg2)If arg2 is NULL, return NULL. Otherwise, return arg1.
count_if(x)Aggregate function; rows contribute 1 if x is true or a non-zero number, else 0.
create_sort_key(parameters...)Constructs a binary-comparable sort key based on a set of input parameters and sort qualifiers.
current_catalog()返回the name of the currently active catalog. Default is memory.
current_database()返回the name of the currently active database.
current_query()返回the current query as a string.
current_schema()返回the name of the currently active schema. Default is main.
current_schemas(boolean)返回list of schemas. Pass a parameter of true to include implicit schemas.
current_setting('setting_name')返回the current value of the configuration setting.
currval('sequence_name')返回the current value of the sequence. Note that nextval must be called at least once prior to calling currval.
error(message)Throws the given error message.
equi_width_bins(min, max, bincount, nice := false)返回the upper boundaries of a partition of the interval [min, max] into bin_count equal-sized subintervals (for use with, e.g., histogram). If nice = true, then min, max and bincount may be adjusted to produce more aesthetically pleasing results.
force_checkpoint(database)Synchronize WAL with file for (optional) database interrupting transactions.
gen_random_uuid()返回a random UUID similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687.
getenv(var)返回the value of the environment variable var. 仅在命令行客户端中可用。
hash(value)返回a UBIGINT with a hash of value. The used hash function may change across Goose versions.
icu_sort_key(string, collator)Surrogate sort key used to sort special characters according to the specific locale. Collator parameter is optional. Only available when the ICU extension is installed.
if(a, b, c)Ternary conditional operator.
ifnull(expr, other)A two-argument version of coalesce.
is_histogram_other_bin(arg)返回true when arg is the "catch-all element" of its datatype for the purpose of the histogram_exact function, which is equal to the "right-most boundary" of its datatype for the purpose of the histogram function.
md5(string)返回the MD5 hash of the string as a VARCHAR.
md5_number(string)返回the MD5 hash of the string as a UHUGEINT.
md5_number_lower(string)返回the lower 64-bit segment of the MD5 hash of the string as a UBIGINT.
md5_number_upper(string)返回the upper 64-bit segment of the MD5 hash of the string as a UBIGINT.
nextval('sequence_name')返回the following value of the sequence.
nullif(a, b)返回NULL if a = b, else return a. Equivalent to CASE WHEN a = b THEN NULL ELSE a END.
pg_typeof(expression)返回the lower case name of the data type of the result of the expression. For PostgreSQL compatibility.
query(query_string)表函数：parses and executes the query defined in query_string. Only constant strings are allowed. Warning: this function allows invoking arbitrary queries, potentially altering the database state.
query_table(tbl_name)表函数：returns the table given in tbl_name.
query_table(tbl_names, [by_name])表函数：returns the union of tables given in tbl_names. If the optional by_name parameter is set to true, it uses UNION ALL BY NAME semantics.
read_blob(source)返回the content from source (a filename, a list of filenames, or a glob pattern) as a BLOB. See the read_blob guide 了解更多细节。
read_text(source)返回the content from source (a filename, a list of filenames, or a glob pattern) as a VARCHAR. The file content is first validated to be valid UTF-8. If read_text attempts to read a file with invalid UTF-8 an error is thrown suggesting to use read_blob instead. See the read_text guide 了解更多细节。
sha1(string)返回a VARCHAR with the SHA-1 hash of the string.
sha256(string)返回a VARCHAR with the SHA-256 hash of the string.
stats(expression)返回a string with statistics about the expression. Expression can be a column, constant, or SQL expression.
txid_current()返回the current transaction's identifier, a BIGINT value. It will assign a new one if the current transaction does not have one already.
typeof(expression)返回the name of the data type of the result of the expression.
uuid()返回a random UUID (UUIDv4) similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687.
uuidv4()返回a random UUID (UUIDv4) similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687.
uuidv7()返回a random UUIDv7 similar to this: 81964ebe-00b1-7e1d-b0f9-43c29b6fb8f5.
uuid_extract_timestamp(uuidv7)提取TIMESTAMP WITH TIME ZONE from a UUIDv7 value.
uuid_extract_version(uuid)提取UUID version (4 or 7).
version()返回the currently active version of Goose in this format.

alias(column)​

| 说明 | 返回the name of the column. | | 示例 | alias(column1) | | 结果 | column1 |

can_cast_implicitly(source_value, target_value)​

| 说明 | Whether or not we can implicitly cast from the types of the source value to the target value. | | 示例 | can_cast_implicitly(1::BIGINT, 1::SMALLINT) | | 结果 | false |

checkpoint(database)​

| 说明 | Synchronize WAL with file for (optional) database without interrupting transactions. | | 示例 | checkpoint(my_db) | | 结果 | success Boolean |

coalesce(expr, ...)​

| 说明 | 返回the first expression that evaluates to a non-NULL value. Accepts 1 or more parameters. Each expression can be a column, literal value, function result, or many others. | | 示例 | coalesce(NULL, NULL, 'default_string') | | 结果 | default_string |

constant_or_null(arg1, arg2)​

| 说明 | If arg2 is NULL, return NULL. Otherwise, return arg1. | | 示例 | constant_or_null(42, NULL) | | 结果 | NULL |

count_if(x)​

| 说明 | Aggregate function; rows contribute 1 if x is true or a non-zero number, else 0. | | 示例 | count_if(42) | | 结果 | 1 |

create_sort_key(parameters...)​

| 说明 | Constructs a binary-comparable sort key based on a set of input parameters and sort qualifiers. | | 示例 | create_sort_key('abc', 'ASC NULLS FIRST'); | | 结果 | \x02bcd\x00 |

current_catalog()​

| 说明 | 返回the name of the currently active catalog. Default is memory. | | 示例 | current_catalog() | | 结果 | memory |

current_database()​

| 说明 | 返回the name of the currently active database. | | 示例 | current_database() | | 结果 | memory |

current_query()​

| 说明 | 返回the current query as a string. | | 示例 | current_query() | | 结果 | SELECT current_query(); |

current_schema()​

| 说明 | 返回the name of the currently active schema. Default is main. | | 示例 | current_schema() | | 结果 | main |

current_schemas(boolean)​

| 说明 | 返回list of schemas. Pass a parameter of true to include implicit schemas. | | 示例 | current_schemas(true) | | 结果 | ['temp', 'main', 'pg_catalog'] |

current_setting('setting_name')​

| 说明 | 返回the current value of the configuration setting. | | 示例 | current_setting('access_mode') | | 结果 | automatic |

currval('sequence_name')​

| 说明 | 返回the current value of the sequence. Note that nextval must be called at least once prior to calling currval. | | 示例 | currval('my_sequence_name') | | 结果 | 1 |

error(message)​

| 说明 | Throws the given error message. | | 示例 | error('access_mode') |

equi_width_bins(min, max, bincount, nice := false)​

| 说明 | 返回the upper boundaries of a partition of the interval [min, max] into bin_count equal-sized subintervals (for use with, e.g., histogram). If nice = true, then min, max and bincount may be adjusted to produce more aesthetically pleasing results. | | 示例 | equi_width_bins(0.1, 2.7, 4, true) | | 结果 | [0.5, 1.0, 1.5, 2.0, 2.5, 3.0] |

force_checkpoint(database)​

| 说明 | Synchronize WAL with file for (optional) database interrupting transactions. | | 示例 | force_checkpoint(my_db) | | 结果 | success Boolean |

gen_random_uuid()​

| 说明 | 返回a random UUID (UUIDv4) similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687. | | 示例 | gen_random_uuid() | | 结果 | various |

getenv(var)​

| 说明 | 返回the value of the environment variable var. 仅在命令行客户端中可用。 | | 示例 | getenv('HOME') | | 结果 | /path/to/user/home |

hash(value)​

| 说明 | 返回a UBIGINT with the hash of the value. The used hash function may change across Goose versions. | | 示例 | hash('🦆') | | 结果 | 2595805878642663834 |

icu_sort_key(string, collator)​

| 说明 | Surrogate sort key used to sort special characters according to the specific locale. Collator parameter is optional. Only available when the ICU extension is installed. | | 示例 | icu_sort_key('ö', 'DE') | | 结果 | 460145960106 |

if(a, b, c)​

| 说明 | Ternary conditional operator; returns b if a, else returns c. Equivalent to CASE WHEN a THEN b ELSE c END. | | 示例 | if(2 > 1, 3, 4) | | 结果 | 3 |

ifnull(expr, other)​

| 说明 | A two-argument version of coalesce. | | 示例 | ifnull(NULL, 'default_string') | | 结果 | default_string |

is_histogram_other_bin(arg)​

| 说明 | 返回true when arg is the "catch-all element" of its datatype for the purpose of the histogram_exact function, which is equal to the "right-most boundary" of its datatype for the purpose of the histogram function. | | 示例 | is_histogram_other_bin('') | | 结果 | true |

md5(string)​

| 说明 | 返回the MD5 hash of the string as a VARCHAR. | | 示例 | md5('abc') | | 结果 | 900150983cd24fb0d6963f7d28e17f72 |

md5_number(string)​

| 说明 | 返回the MD5 hash of the string as a UHUGEINT. | | 示例 | md5_number('abc') | | 结果 | 152195979970564155685860391459828531600 |

md5_number_lower(string)​

| 说明 | 返回the lower 8 bytes of the MD5 hash of string as a UBIGINT. | | 示例 | md5_number_lower('abc') | | 结果 | 8250560606382298838 |

md5_number_upper(string)​

| 说明 | 返回the upper 8 bytes of the MD5 hash of string as a UBIGINT. | | 示例 | md5_number_upper('abc') | | 结果 | 12704604231530709392 |

nextval('sequence_name')​

| 说明 | 返回the following value of the sequence. | | 示例 | nextval('my_sequence_name') | | 结果 | 2 |

nullif(a, b)​

| 说明 | 返回NULL if a = b, else return a. Equivalent to CASE WHEN a = b THEN NULL ELSE a END. | | 示例 | nullif(1+1, 2) | | 结果 | NULL |

pg_typeof(expression)​

| 说明 | 返回the lower case name of the data type of the result of the expression. For PostgreSQL compatibility. | | 示例 | pg_typeof('abc') | | 结果 | varchar |

query(query_string)​

| 说明 | 表函数：parses and executes the query defined in query_string. Only constant strings are allowed. Warning: this function allows invoking arbitrary queries, potentially altering the database state. | | 示例 | query('SELECT 42 AS x') | | 结果 | 42 |

query_table(tbl_name)​

| 说明 | 表函数：returns the table given in tbl_name. | | 示例 | query_table('t1') | | 结果 | (the rows of t1) |

query_table(tbl_names, [by_name])​

| 说明 | 表函数：returns the union of tables given in tbl_names. If the optional by_name parameter is set to true, it uses UNION ALL BY NAME semantics. | | 示例 | query_table(['t1', 't2']) | | 结果 | (the union of the two tables) |

read_blob(source)​

| 说明 | 返回the content from source (a filename, a list of filenames, or a glob pattern) as a BLOB. See the read_blob guide 了解更多细节。 | | 示例 | read_blob('hello.bin') | | 结果 | hello\x0A |

read_text(source)​

| 说明 | 返回the content from source (a filename, a list of filenames, or a glob pattern) as a VARCHAR. The file content is first validated to be valid UTF-8. If read_text attempts to read a file with invalid UTF-8 an error is thrown suggesting to use read_blob instead. See the read_text guide 了解更多细节。 | | 示例 | read_text('hello.txt') | | 结果 | hello\n |

sha1(string)​

| 说明 | 返回a VARCHAR with the SHA-1 hash of the string. | | 示例 | sha1('🦆') | | 结果 | 949bf843dc338be348fb9525d1eb535d31241d76 |

sha256(string)​

| 说明 | 返回a VARCHAR with the SHA-256 hash of the string. | | 示例 | sha256('🦆') | | 结果 | d7a5c5e0d1d94c32218539e7e47d4ba9c3c7b77d61332fb60d633dde89e473fb |

stats(expression)​

| 说明 | 返回a string with statistics about the expression. Expression can be a column, constant, or SQL expression. | | 示例 | stats(5) | | 结果 | '[Min: 5, Max: 5][Has Null: false]' |

txid_current()​

| 说明 | 返回the current transaction's identifier, a BIGINT value. It will assign a new one if the current transaction does not have one already. | | 示例 | txid_current() | | 结果 | various |

typeof(expression)​

| 说明 | 返回the name of the data type of the result of the expression. | | 示例 | typeof('abc') | | 结果 | VARCHAR |

uuid()​

| 说明 | 返回a random UUID (UUIDv4) similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687. | | 示例 | uuid() | | 结果 | various |

uuidv4()​

| 说明 | 返回a random UUID (UUIDv4) similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687. | | 示例 | uuidv4() | | 结果 | various |

uuidv7()​

| 说明 | 返回a random UUIDv7 similar to this: 81964ebe-00b1-7e1d-b0f9-43c29b6fb8f5. | | 示例 | uuidv7() | | 结果 | various |

uuid_extract_timestamp(uuidv7)​

| 说明 | 提取TIMESTAMP WITH TIME ZONE from a UUIDv7 value. | | 示例 | uuid_extract_timestamp(uuidv7()) | | 结果 | 2025-04-19 15:51:20.07+00 |

uuid_extract_version(uuid)​

| 说明 | 提取UUID version (4 or 7). | | 示例 | uuid_extract_version(uuidv7()) | | 结果 | 7 |

version()​

| 说明 | 返回the currently active version of Goose in this format. | | 示例 | version() | | 结果 | various |

实用表函数​

表函数用于在 FROM 子句中替代表。

名称说明
glob(search_path)返回filenames found at the location indicated by the search_path in a single column named file. The search_path may contain glob pattern matching syntax.
repeat_row(varargs, num_rows)返回a table with num_rows rows, each containing the fields defined in varargs.

glob(search_path)​

| 说明 | 返回filenames found at the location indicated by the search_path in a single column named file. The search_path may contain glob pattern matching syntax. | | 示例 | glob('*') | | 结果 | (table of filenames) |

repeat_row(varargs, num_rows)​

| 说明 | 返回a table with num_rows rows, each containing the fields defined in varargs. | | 示例 | repeat_row(1, 2, 'foo', num_rows = 3) | | 结果 | 3 rows of 1, 2, 'foo' |