El contenido no se encuentra disponible en el idioma seleccionado. Estamos trabajando continuamente para agregar más idiomas. Gracias por su apoyo.

Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Situation Awareness
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
Software Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive
On this page

Show all

Functions

Updated on 2022-08-16 GMT+08:00

This section describes the built-in functions and advanced function packages added to PostgreSQL Enhanced Edition on the basis of PostgreSQL 11 open-source edition.

Table 1 Built-in functions

Built-in Function

Description

add_months(date,integer)

Returns the date plus integer months. The return type is DATE.

appendchildxml(XMLType_instance, XPath_string, value_expr[, namespace_string])

Appends the value_expr node onto XPath_string specified by XMLType_instance. The namespace_string provides namespace information for the XPath_string.

asciistr(string)

Returns an ASCII version of the string in the database character set. Non-ASCII characters are not supported.

bin_to_num(expr_list)

Converts a binary string in expr_list to its equivalent decimal number. The return type is NUMBER.

bitand(number1,number2)

Returns the bitwise 'AND' for two supplied integers number1 and number2. The return type is BIT.

convert(char, dest_char_set[, source_char_set])

Converts char in the source_char_set to the dest_char_set encoding format. This function takes effect only on the server.

cosh(n)

Returns the hyperbolic cosine of argument n.

decode(expr,search1, result1[[,search2, result2],......][, default])

Compares expr to each search value (search1, search2, etc). If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.

empty_blob()

Returns an empty BLOB.

hextoraw(char)

Converts a hexadecimal string to a raw value.

instrb(string, substring[, position[, occurrence]])

Searches a string for a substring using characters and return the position in the string that is the first character of a specified occurrence of the substring. The functions vary in how they determine the position of the substring to return.

last_day(date)

Returns the date of the last day of the month that contains date.

lengthb(char)

Returns the length of char. Char can be any of the data types (CHAR, VARCHAR2, NCHAR, or NVARCHAR2), or types (such as integer) that can be implicitly converted into character strings.

listagg(measure_expr[, 'delimiter']) within group(order_by_clause) [over query_partition_clause]

Sorts the values of the column expression measure_expr in the query_partition_clause group based on the order_by_clause rule and aggregates them into one row. Values are separated by delimiter.

lnnvl(condition)

Returns a value of condition expression. The return type is BOOLEAN.

mod(n2, n1)

Returns the remainder of n2 divided by n1. Returns n2 if n1 is 0.

months_between(date1, date2)

Returns the number of months between dates date1 and date2. If date1 is earlier than date2, then the result is negative.

nanvl(n2, n1)

Returns n1 if the single- or double-precision floating point number input value n2 is NAN. If the input value n2 is not NAN, n2 is returned.

nchr(number)

Returns the character having the binary equivalent to number in the national character set.

new_time(date, timezone1, timezone2)

Returns the date and time in time zone timezone2 when date and time in time zone timezone1 are date. The return type is DATE.

next_day(date, char)

Returns the date of the first weekday named by char that is later than the date (including workdays, weekends, and holidays). The return type is DATE.

numtodsinterval(n, interval_unit)

Converts n to an INTERVAL DAY TO SECOND literal. The value for interval_unit specifies the unit of n and must resolve to 'DAY', 'HOUR', 'MINUTE', and 'SECOND'.

numtoyminterval(n, 'interval_unit')

Converts n to an INTERVAL YEAR TO MONTH literal. The value for interval_unit can be YEAR or MONTH.

nlssort(char[, nlsparam])

Sorts the char string according to the sorting character set specified by nlsparam. By default, char is used for sorting.

nls_upper(char[, nlsparam])

Converts all alphabetic characters in the character string char to uppercase letters based on the sort sequence specified by nlsparam. The character string type is CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB, and nlsparam is in the form of NLS_SORT = sort.

nls_lower(char[, nlsparam])

Converts all alphabetic characters in the character string char to lowercase letters based on the sort sequence specified by nlsparam. The character string type is CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB, and nlsparam is in the form of NLS_SORT = sort.

nvl(expr1, expr2)

Returns the first non-null value in expr1 and expr2.

rawtohex(raw)

Converts raw to a character value containing its hexadecimal representation.

regexp_count(source_char, pattern, position, match_param)

Returns the number of times a pattern occurs in a source string starting from the position that indicates the source_char character where the database begins the search. The match_param parameter is a text literal that lets you change the default matching behavior of the function. For example, match_param='i' specifies case-insensitive matching.

regexp_instr(source_char, pattern[, position[, occurrence[, return_opt[, match_param[, subexpr]]]]])

Extends the INSTR function and allows regular expression matching. The return type is INTEGER.

  • position: indicates the start position of the search.
  • occurrence: indicates the sequence number of the pattern in source_char.
  • return_opt:
    • The value 0 indicates the start position of the return mode.
    • The value 1 indicates the end position of the return mode.
  • match_param: indicates the control parameter of the regular expression, such as case sensitive.
  • subexpr: indicates the group number of the regular expression group.

regexp_like(source_char, pattern[,match_param])

source_char is a character expression. Pattern is the regular expression. The match_param parameter is a text literal that lets you change the default matching behavior of the function.

regexp_substr(source_char, pattern[,position[,occurrence[,match_param[,subexpr]]]])

Matches the character string in the source_char string based on the regular expression.

  • source_char is the text expression that is searched. Supports all character strings, including CHAR, VARCHAR2, NCHAR, or NVARCHAR2, or types (such as integer) that can be implicitly converted into character strings.
  • pattern is the text expression to search for.
  • position is a nonzero integer indicating the character of source_char where the function begins the search.
  • occurrence is an integer indicating which occurrence of pattern the function should search for.
  • match_parameter is a text expression that lets you change the default matching behavior of the function.
  • subexpr is a nonnegative integer from 0 to 9 indicating which subexpression in pattern is to be returned by the function.

raise_application_error(errnum, errmsg)

Sends the error code errnum and error message errmsg to the client.

remainder(n2, n1)

Returns the remainder of n2 divided by n1. The remainder function is similar to mod except that mod uses floor in its formula, whereas reminder uses ROUND. The return type is NUMERIC or double-precision floating-point number (determined by the input parameter type).

round(n,precision)

Returns n rounded to integer places to the right of the decimal point. The precision is the number of digits in a number.

scn_to_timestamp(number)

Returns the approximate timestamp associated with a system change number (SCN).

sinh(n)

Returns the hyperbolic sine of n. If n is BINARY_FLOAT, the return type is BINARY_DOUBLE. Otherwise, the return type is NUMERIC.

substr(char,position[,substring_length])

Returns a portion of string, beginning at a specified position in the string. The functions vary in how they calculate the length of the substring to return. If substring_length is not specified, the function returns all characters to the end of string.

substrb(char, position[, substring_length])

Returns a portion of char, beginning at a specified position in the string. The functions vary in how they calculate the length of the substring to return. If substring_length is not specified, the function returns all characters to the end of string.

sys_context(namespace, parameter)

Returns the value of parameter associated with the context namespace. The return type is VARCHAR2.

sys_guid()

Returns a globally unique identifier (RAW value).

sys_connect_by_path(column, char)

Is valid only in CONNECT BY queries and returns the path of a column value from root to node.

tanh(n)

Returns the hyperbolic tangent of argument n.

to_blob(char)

Converts char strings to BLOB values. Char can be any of the data types (CHAR, VARCHAR2, NCHAR, or NVARCHAR2), or types (such as integer) that can be implicitly converted into character strings.

to_binary_float(expr)

Converts expr to the single-precision float type.

to_binary_double(expr)

Converts expr to the double-precision float type.

to_clob(char)

Converts char to the CLOB data type.

to_char(char)

Supports char types: char, character, and varchar.

to_date(char[,fmt])

Converts char of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or TIMESTAMP data type to a value of the DATE data type according to the fmt format. If fmt is omitted, char must use the default format of the DATE data type.

to_dsinterval('sql_format' | 'ds_iso_format')

Converts the time character string of the SQL standard (such as '100 00:00:00') or ISO standard (such as 'P100DT05H') to the INTERVAL DAY TO SECOND data type.

to_multi_byte(char)

Converts a single-byte character char into a multi-byte character.

to_number(expr)

Converts expr to a value of NUMBER data type.

to_number(expr, fmt, 'nlsparam')

Converts expr to a value of NUMBER data type in the format specified by fmt. The nlsparam is an international language parameter and supports the following parameters: NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, and NLS_ISO_CURRENCY.

to_timestamp(char[,fmt])

Converts char of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or TIMESTAMP data type to a value of the timestamp data type according to the fmt format. If fmt is omitted, char must use the default format of the TIMESTAMP data type.

to_single_byte(char)

Converts multibyte characters to their corresponding single-byte characters.

to_yminterval('sql_format' | 'ym_iso_format')

Converts the time character string of the SQL standard (such as '01-02') or ISO standard (such as 'P1Y2M') to the INTERVAL MONTH TO YEAR data type.

timestamp_to_scn(timestamp)

Returns the approximate system change number (SCN) associated with a timestamp.

trunc(date[, fmt])

Truncates date according to the date format specified by fmt. The return type is DATE. If fmt is omitted, the default date format is 'DDD'.

tz_offset({time_zone_name | '{+|-}hh:mi'})

Returns the specified time zone offset. The return type is VARCHAR2. The parameter is a character string in the time_zone_name or '{+|-}hh:mi' format.

value(correlation_variable)

Returns the recorded row associated with correlation_variable in object table mode. The return type is the object table associated with correlation_variable.

Table 2 Advanced function packages

Advanced Function Package

Description

DBMS_OUTPUT.PUT(item)

Places the item string in the local buffer. Item indicates all types that can be converted into character strings.

DBMS_OUTPUT.PUT_LINE(item)

Places the item string in the local buffer and outputs all the content in the local buffer. Item indicates all types that can be converted into character strings.

DBMS_RANDOM.SEED(val)

Val is the seed number used to generate a random number. It can be a character string or a digit.

DBMS_RANDOM.VALUE([low,high])

Returns a 16-digit random number between low and high. If the range of low and high is not specified, the default value range is 0-1.

dbms_lob.getlength(lob_loc {clob|blob})

Returns the LOB length specified by lob_loc.

dbms_lob.read(lob_loc, amount, offset, buffer)

Returns the specified amount into the buffer parameter, starting from an absolute offset from the beginning of the LOB.

dbms_lob.write(lob_loc, amount, offset, buffer)

Writes the buffer content to the large object lob_loc buffer (the referenced large object is not affected) starting at offset. The amount represents the size.

utl_raw.cast_to_raw(char)

Converts char of the VARCHAR2 data type to RAW. The return type is RAW.

utl_raw.length(raw)

Returns the length of the raw data type. The return type is NUMBER.

utl_raw.cast_from_binary_integer(n, endianess)

Converts the integer n to the RAW type based on the memory alignment mode specified by endianess. The values of endianess are as follows:

  • 1: big_endian
  • 2: little_endian
  • 3: machine_endian

Utilizamos cookies para mejorar nuestro sitio y tu experiencia. Al continuar navegando en nuestro sitio, tú aceptas nuestra política de cookies. Descubre más

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback