Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the data type conversion rules of PostgreSQL". In the daily operation, I believe that many people have doubts about the data type conversion rules of PostgreSQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "what are the data type conversion rules of PostgreSQL?" Next, please follow the editor to study!

I. function

The specified function that depends on a function call is determined using the following procedure.

Function Type Resolution

1. Select a function from pg_proc. In general, if schema is not specified, the name-parameter matching is selected in the current search path, otherwise the function that specifies schema will be selected.

a. If there are multiple operators of the same parameter type in the search path, choose the one that appears first. Operators with different parameter types are considered equal no matter how the search path is set.

b. If the function is declared with the VARIADIC array parameter, but the call does not use the VARIADIC keyword, the array parameter is replaced with one or more values of the array element type to match the function call. After this expansion, it may have the same parameters as the function of NON-VARIADIC, in which case, use the one that finally appears in the search path, or the one that uses the NON-VARIADIC in the same schema.

c. Functions with default parameter values match any function call that omits zero or more default parameter positions. If multiple functions match, the one that finally appears in the search path will be selected. If there are two or more such functions in the same schema, PG cannot choose which one to use, so it will report an error: "ambiguous function call".

two。 Check whether the input parameter type is accepted. Use this function if it exists. Similar to operators, there are security vulnerabilities.

3. If there is no exact match, check whether the function call requires type conversion. This occurs when the function call has only one parameter and the function name is the same as the internal function name. In addition, the function argument must be unknown-type literal, or binary-coercible a named data type, or be converted to a named data type by the Ibank O function. If these conditions are met, the function call is treated as a CAST.

4. Looking for the best match

Refer to the description of the operator operator.

Here are some examples:

Round

The definition of the round function in pg_proc is as follows:

Testdb=# select oid,proname,provariadic,proargtypes,prorettype,prosrc from pg_proc where proname = 'round' Oid | proname | provariadic | proargtypes | prorettype | prosrc-+-1342 | round | | 0 | 701 | dround 1707 | round | 0 | 1700 23 | 1700 | numeric_round 1708 | round | 0 | 1700 | 1700 | select pg_catalog.round ($1 rows 0) (3 rows)

Where proargtypes is the parameter type, prorettype is the return type, and prosrc is the source code for function implementation.

The type 23thumb 701amp 1700 is defined as follows

Testdb=# select oid,typname,typalign,typstorage from pg_type where oid in; oid | typname | typalign | typstorage-+-23 | int4 | I | p 701 | float8 | d | p 1700 | numeric | I | m (3 rows)

Execute SQL

Testdb=# SELECT round (4,4); round-4.0000 (1 row)

In pg_proc, only one function (oid = 1707) has two arguments, the first parameter type is regarded as numeric, and the second parameter type is integer, then the first parameter 4 is automatically converted to numeric type, which is the same as "SELECT round (CAST (4 AS numeric), 4);".

Variadic

First define a function variadic_example

Testdb=# CREATE FUNCTION public.variadic_example (VARIADIC numeric []) RETURNS inttestdb-# LANGUAGE sql AS 'SELECT 1 creating FUNCTION

Function definition

Testdb=# select oid,proname,provariadic,proargtypes,prorettype,prosrc from pg_proc where proname = 'variadic_example' Oid | proname | provariadic | proargtypes | prorettype | prosrc-+-32787 | variadic_example | 1700 | 1231 | 23 | SELECT 1 (1 row) testdb=# select oid,typname,typalign Typstorage from pg_type where oid in (1231 and 1700) Oid | typname | typalign | typstorage-+-1231 | _ numeric | I | x 1700 | numeric | I | m (2 rows)

Executes a function that accepts a variable parameter keyword, but does not need to be specified, and allows integer and numeric arguments:

Testdb=# SELECT public.variadic_example (0), testdb-# public.variadic_example (0.0), testdb-# public.variadic_example (VARIADIC array [0.0]) Variadic_example | variadic_example | variadic_example-+-+-1 | 1 | 1 (1 row)

The first and second calls above will prefer clearly defined functions:

Testdb=# CREATE FUNCTION public.variadic_example (numeric) RETURNS int LANGUAGE sql AS 'SELECT 2 destroy # CREATE FUNCTIONtestdb=# CREATE FUNCTION public.variadic_example (int) RETURNS int LANGUAGE sql AS' SELECT 3 destroy TestDB # CREATE FUNCTIONtestdb=# SELECT public.variadic_example (0), testdb-# public.variadic_example (0), testdb-# public.variadic_example (VARIADIC array [0]) Variadic_example | variadic_example | variadic_example-+-3 | 2 | 1 (1 row)

Substring

There are several substr functions:

Testdb=# select oid,proname,provariadic,proargtypes,prorettype,prosrc from pg_proc where proname = 'substr' Oid | proname | provariadic | proargtypes | prorettype | prosrc-+-877 | substr | 0 | 25 23 23 | 25 | Text_substr 883 | substr | 0 | 25 23 | 25 | text_substr_no_len 2085 | substr | 0 | 17 23 23 | 17 | bytea_substr 2086 | substr | 0 | 17 23 | 17 | bytea_substr_no_len (4 rows) testdb=# select oid Typname,typalign,typstorage from pg_type where oid in (17, 23, 25) Oid | typname | typalign | typstorage-+-17 | bytea | I | x 23 | int4 | I | p 25 | text | I | x (3 rows)

If you do not specify a parameter type to call a function, the system will give priority to the function whose argument is text + int4:

Testdb=# SELECT substr ('1234, 3); substr-34 (1 row)

Convert to text if the specified type is varchar

Testdb=# SELECT substr (varchar '1234, 3); substr-34 (1 row) testdb=# SELECT substr (CAST (varchar' 1234'AS text), 3); substr-34 (1 row)

If the first parameter is integer and the system has no conversion function, an error will be reported.

Testdb=# SELECT substr (1234, 3); psql: ERROR: function substr (integer, integer) does not existLINE 1: SELECT substr (1234, 3); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.testdb=# at this point, on the "PostgreSQL data type conversion rules what are the end of the study, I hope to be able to solve everyone's doubts." The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.