Build-in Functions

Hai semuanya, di materi kali ini kita akan membahas Fuctions pada PostgreSQL, PostgreSQL provides a large number of functions for the built-in data types. Users can also define their own functions, as described in previews section but for now we explain build-in fuctions.

ada banyak selali Fucntions, kita akan bahas beberapa yang menurut saya penting untuk di pelajari pada cource ini diantaranya:

1. String function
2. Math function
3. Date/Time function
4. Nulless function
5. Data type formatting function

Ok langsung aja kita bahas materi yang pertama

String function

This section describes functions for examining and manipulating string values. Strings in this context include values of the types character, character varying, and text. Except where noted, these functions are declared to accept and return type text.

Functions	Description
substring ( string text [ FROM start integer ] [ FOR count integer ] ) → text	Extracts the substring of string starting at the start’th character if that is specified, and stopping after count characters if that is specified. Provide at least one of start and count.
lower ( text ) → text	Converts the string to all lower case, according to the rules of the database’s locale.
upper ( text ) → text	Converts the string to all upper case, according to the rules of the database’s locale.
trim ( [ LEADING | TRAILING | BOTH ] [ FROM ] string text [, characters text ] ) → text	Removes the longest string containing only characters in characters (a space by default) from the start, end, or both ends (BOTH is the default) of string.
position ( substring text IN string text ) → integer	Returns first starting index of the specified substring within string, or zero if it’s not present.
character_length ( text ) → integer	Returns number of characters in the string.
ascii ( text ) → integer	Returns the numeric code of the first character of the argument. In UTF8 encoding, returns the Unicode code point of the character. In other multibyte encodings, the argument must be an ASCII character.
concat ( val1 "any" [, val2 "any" [, ...] ] ) → text	Concatenates the text representations of all the arguments. NULL arguments are ignored.
initcap ( text ) → text	Converts the first letter of each word to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters.
length ( text ) → integer	Returns the number of characters in the string.
reverse ( text ) → text	Reverses the order of the characters in the string.
substr ( string text, start integer [, count integer ] ) → text	Extracts the substring of string starting at the start’th character, and extending for count characters if that is specified. (Same as substring(string from start for count).)

Berikut adalah implementasinya pada PostgreSQL:

Jika dijalankan hasilnya seperti berikut:

hr=# select  substring('Dimas Maryanto' from 1 for 3) as "substring from",
hr-#         substr('Dimas Maryanto', 1, 5) as "substr",
hr-#         lower('Ini Adalah Text BESAR dan Kecil') as "lower",
hr-#         upper('Ini Adalah Text BESAR dan Kecil') as "upper",
hr-#         initcap('Ini Adalah Text BESAR dan Kecil') as "initcamp",
hr-#         trim(both ' ' from '  ini text ada spacenya  ') as "trim both",
hr-#         trim(trailing ' ' from '  ini text ada spacenya  ') as "trim trailing",
hr-#         reverse('dimas') as "reverse",
hr-#         length('dimas maryanto') as "length",
hr-#         concat('dimasm93', ' : ', 'Dimas Maryanto') as "concat",
hr-#         ascii('D') as "ascii";
 substring from | substr |              lower              |              upper              |            initcamp             |       trim both       |      trim trailing      | reverse | length |          concat           | ascii
----------------+--------+---------------------------------+---------------------------------+---------------------------------+-----------------------+-------------------------+---------+--------+---------------------------+-------
 Dim            | Dimas  | ini adalah text besar dan kecil | INI ADALAH TEXT BESAR DAN KECIL | Ini Adalah Text Besar Dan Kecil | ini text ada spacenya |   ini text ada spacenya | samid   |     14 | dimasm93 : Dimas Maryanto |    68
(1 row)

Math function

This section describes and shows the available mathematical functions for calculation. Many of these functions are provided in multiple forms with different argument types. Except where noted, any given form of a function returns the same data type as its argument(s); cross-type cases are resolved in the same way as operators.

Functions	Description
abs ( numeric_type ) → numeric_type	Absolute value
div ( y numeric, x numeric ) → numeric	Integer quotient of y/x (truncates towards zero)
factorial ( bigint ) → numeric	Factorial
floor ( numeric ) → numeric	Nearest integer less than or equal to argument
mod ( y numeric_type, x numeric_type ) → numeric_type	Remainder of y/x; available for smallint, integer, bigint, and numeric
pi ( ) → double precision	Approximate value of π
power ( a numeric, b numeric ) → numeric	a raised to the power of b
round ( numeric ) → numeric	Rounds to nearest integer. For numeric, ties are broken by rounding away from zero.
sqrt ( numeric ) → numeric	Square root
random ( ) → double precision	Returns a random value in the range 0.0 <= x < 1.0

Selain itu juga tersedia function lainnya seperti:

1. Trigonometric Functions
2. Hyperbolic Functions

Berikut adalah implementasi SQLnya:

Jika dijalankan hasilnya seperti berikut:

hr=# select  abs(-10) "absolut",
hr-#         div(10, 3) "division",
hr-#         mod(5, 2) "mod",
hr-#         power(2, 3) "power",
hr-#         round(5.451234, 2) "round scale2",
hr-#         round(5.43) "round",
hr-#         round(5.6) "roundup",
hr-#         floor(5.45234) "floor",
hr-#         floor(5.6) "floor2";
 absolut | division | mod | power | round scale2 | round | roundup | floor | floor2
---------+----------+-----+-------+--------------+-------+---------+-------+--------
      10 |        3 |   1 |     8 |         5.45 |     5 |       6 |     5 |      5
(1 row)

Date/Time function

This section describes and shows the available functions for date/time value processing, with details appearing in the following subsections.

Functions	Description
current_date → date	Current date
current_time → time with time zone	Current time of day
current_timestamp → timestamp with time zone	Current date and time (start of current transaction)
now () → timestamp with time zone	Current date and time (start of current transaction)
extract ( field from timestamp ) → numeric	Get timestamp subfield
isfinite ( date ) → boolean	Test for finite date (not +/-infinity)
age ( timestamp, timestamp ) → interval	Subtract arguments, producing a “symbolic” result that uses years and months, rather than just days
age ( timestamp ) → interval	Subtract argument from current_date (at midnight)

The extract function retrieves subfields such as year or hour from date/time values. field is an identifier or string that selects what field to extract from the source value

1. century
2. day
3. decade
4. epoch
5. hour
6. milliseconds
7. minute
8. month
9. year

Berikut adalah implementasi SQLnya:

Jika dijalankan hasilnya seperti berikut:

hr=# select  current_date tgl_sekarang,
hr-#         now() datetime_sekarang_func,
hr-#         current_timestamp as datetime_tz,
hr-#         age(timestamp '1991-03-01') as years_old,
hr-#         extract(year from current_timestamp) get_current_year,
hr-#         extract(month from current_date) get_current_month;
 tgl_sekarang |    datetime_sekarang_func     |          datetime_tz          |    years_old    | get_current_year | get_current_month
--------------+-------------------------------+-------------------------------+-----------------+------------------+-------------------
 2022-03-04   | 2022-03-04 12:04:26.070727+00 | 2022-03-04 12:04:26.070727+00 | 31 years 3 days |             2022 |                 3
(1 row)

Nulless function

Function untuk menghandle nilai null di PostgreSQL bisa menggunakan berbagai macam cara yaitu

1. COALESCE(value [, ...])
2. NULLIF(value1, value2)

The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display.

The NULLIF function returns a null value if value1 equals value2; otherwise it returns value1. This can be used to perform the inverse operation of the COALESCE

Berikut adalah implementasi SQLnya:

Jika dijalankan hasilnya seperti berikut:

hr=# select  COALESCE(null, 'data1', 'data2') return_data1,
hr-#         COALESCE(null, null, 'data2') return_data2,
hr-#         COALESCE(null, null, null) return_null,
hr-#         NULLIF(null, 'data1') return_null1,
hr-#         NULLIF('data1', 'data1') return_null2,
hr-#         NULLIF('data1', 'data2') return_data1;
 return_data1 | return_data2 | return_null | return_null1 | return_null2 | return_data1
--------------+--------------+-------------+--------------+--------------+--------------
 data1        | data2        |             |              |              | data1
(1 row)

Data type formatting function

The PostgreSQL formatting functions provide a powerful set of tools for converting various data types (date/time, integer, floating point, numeric) to formatted strings and for converting from formatted strings to specific data types. These functions all follow a common calling convention: the first argument is the value to be formatted and the second argument is a template that defines the output or input format.

Functions	Description
to_char ( timestamp, date-patterns ) → text	Converts time stamp to string according to the given format.
to_char ( interval, date-patterns ) → text	Converts interval to string according to the given format.
to_char ( numeric_type, number-pattern ) → text	Converts number to string according to the given format; available for integer, bigint, numeric, real, double precision.
to_date ( text, date-patterns ) → date	Converts string to date according to the given format.
to_number ( text, number-pattern ) → numeric	Converts string to numeric according to the given format.
to_timestamp ( text, date-patterns ) → timestamp with time zone	Converts string to time stamp according to the given format.

The date-patterns template patterns available for formatting date and time values.

Pattern	Description
HH	hour of day (01–12)
HH24	hour of day (01–24)
MI	minute (00–59)
SS	second (00–59)
MS	millisecond (000–999)
DD	day of month (01–31)
DAY	full upper case day name (blank-padded to 9 chars)
D	day of the week, Sunday to Saturday
MM	month number (01–12)
MON	abbreviated upper case month name (3 chars in English, localized lengths vary)
YY	last 2 digits of year
YYYY	year (4 or more digits)

The number-pattern template patterns available for formatting numeric values.

Pattern	Description
9	digit position (can be dropped if insignificant)
0	digit position (will not be dropped, even if insignificant)
. (period)	decimal point
, (comma)	group (thousands) separator
L	currency symbol (uses locale)
RN	Roman numeral (input between 1 and 3999)

Berikut adalah implementasi SQLnya:

Jika dijalankan hasilnya seperti berikut:

hr=# select  to_char(current_date, 'DD/MON/YYYY') date_indonesia,
hr-#         to_char(current_timestamp, 'DD/MM/YYYY HH24:MM') datetime_indonesia,
hr-#         to_char(1000000, 'RpL999,999,999.00-') sejuta_rupiah,
hr-#         to_date('02/03/22', 'DD/MM/YY') format_ke_date,
hr-#         to_number('10,132,456.53', '999,999,999') format_ke_number;
 date_indonesia | datetime_indonesia |    sejuta_rupiah    | format_ke_date | format_ke_number
----------------+--------------------+---------------------+----------------+------------------
 04/MAR/2022    | 04/03/2022 11:03   | Rp    1,000,000.00- | 2022-03-02     |         10132456
(1 row)

Back to top ↑