Functions

Comparison functions

num_nonnulls(VARIADIC “any”)
Returns the number of non-null arguments.

SELECT num_nonnulls(1, NULL, 2);

Result:

num_nonnulls
------------
           2
(1 row)

num_nulls(VARIADIC “any”)
Returns the number of null arguments.

SELECT num_nulls(1, NULL, 2);

Result:

num_nonnulls
------------
           1
(1 row)

Mathematical functions

It provides various functions related to numbers, and the argument specified as dp indicates double precision.

abs(x)
Returns absolute value of the argument.
```
SELECT abs(-17.4);
```
Result:
```
 abs
-----
17.4
(1 row)
```

cbrt(dp)
Returns cube root of the argument.

SELECT cbrt(27.0);

Result:

 cbrt
------
    3
(1 row)

ceil(dp or numeric) or ceiling(dp or numeric)
Returns nearest integer greater than or equal to argument.
```
SELECT ceil(-42.8);
```
Result:
```
 ceil
------
  -42
(1 row)
```

degrees(dp)
Converts radians to degrees.

SELECT degrees(0.5);

Result:

     degrees      
------------------
 28.6478897565412
(1 row)

div(y numeric, x numeric)
Returns integer quotient of y/x.
```
SELECT div(9,4);
```
Result:
```
 div
-----
   2
(1 row)
```

exp(dp or numeric)
Returns exponential.

SELECT exp(1.0);

Result:

        exp         
--------------------
 2.7182818284590452
(1 row)

floor(dp or numeric)
Returns nearest integer less than or equal to the argument.
```
SELECT floor(-42.8);
```
Result:
```
 floor
-------
   -43
(1 row)
```

ln(dp or numeric)
Returns natural logarithm of the argument.

SELECT ln(2.0);

Result:

        ln
-------------------
 0.693147180559945
(1 row)

log(dp or numeric)
Returns base 10 logarithm.

SELECT log(100.0);

Result:

        log
--------------------
 2.0000000000000000
(1 row)

log(b numeric, x numeric)
Returns logarithm to base b.

SELECT log(2.0, 64.0);

Result:

        log
--------------------
 6.0000000000000000
(1 row)

mod(y, x)
Returns remainder of y/x.

SELECT mod(9,4);

Result:

 mod
-----
   1
(1 row)

pi()
Returns “π” constant.

SELECT pi();

Result:

        pi
------------------
 3.14159265358979
(1 row)

power(a dp, b dp) or power(a numeric, b numeric)
Returns a raised to the power of b.

SELECT power(9.0, 3.0);

Result:

        power
--------------------
 729.00000000000000
(1 row)

radians(dp)
Converts degrees to radians.

SELECT radians(45.0);

Result:

      radians      
-------------------
 0.785398163397448
(1 row)

round(dp or numeric)
Rounds to nearest integer.

SELECT round(42.4);

Result:

 round
-------
    42
(1 row)

round(v numeric, s int)
Rounds to s decimal places of v argument.
```
SELECT round(42.4382, 2);
```
Result:
```
 round
-------
 42.44
(1 row)
```

scale(numeric)
Returns scale of the argument.

SELECT scale(8.41);

Result:

 scale
-------
     2
(1 row)

sign(dp or numeric)
Returns the sign (-1, 0, 1) of the argument.
```
SELECT sign(-8.4);
```
Result:
```
 sign
------
   -1
(1 row)
```

sqrt(dp or numeric)
Returns square root of the argument.

SELECT sqrt(2.0);

Result:

        sqrt
-------------------
 1.414213562373095
(1 row)

trunc(dp or numeric)
Truncates toward zero.

SELECT trunc(42.8);

Result:

 trunc
-------
    42
(1 row)

trunc(v numeric, s int)
Truncates to s decimal places.

SELECT trunc(42.4382, 2);

Result:

 trunc
-------
 42.43
(1 row)

width_bucket(operand dp, b1 dp, b2 dp, count int) or width_bucket(operand numeric, b1 numeric, b2 numeric, count int)
Returns the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range b1 to b2; returns 0 or count+1 for an input outside the range.
```
SELECT width_bucket(5.35, 0.024, 10.06, 5);
```
Result:
```
 width_bucket
--------------
            3
(1 row)
```
width_bucket(operand anyelement, thresholds anyarray)
Returns the bucket number to which operand would be assigned given an array listing the lower bounds of the buckets; returns 0 for an input less than the first lower bound; the thresholds array must be sorted, smallest first.
```
SELECT width_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[]);
```
Result:
```
 width_bucket
--------------
            2
(1 row)
```

String functions

ascii(string)
ASCII code of the first character of the argument. For UTF8 returns the Unicode code point of the character. For other multibyte encodings, the argument must be an ASCII character.
```
SELECT ascii('x');
```
Result:
```
 ascii
-------
   120
(1 row)
```
btrim(string text [, characters text])
Removes the longest string consisting only of characters in characters (a space by default) from the start and end of string.
```
SELECT btrim('xyxtrimyyx', 'xyz');
```
Result:
```
 btrim
-------
  trim
(1 row)
```
chr(int)
Character with the given code. For UTF8 the argument is treated as a Unicode code point. For other multibyte encodings the argument must designate an ASCII character. The NULL (0) character is not allowed because text data types cannot store such bytes.
```
SELECT chr(65);
```
Result:
```
 chr
-----
   A
(1 row)
```
concat(str “any” [, str “any” [, …] ])
Concatenates the text representations of all the arguments. NULL arguments are ignored.
```
SELECT concat('abcde', 2, NULL, 22);
```
Result:
```
  concat
----------
 abcde222
(1 row)
```
concat_ws(sep text, str “any” [, str “any” [, …] ])
Concatenates all but the first argument with separators. The first argument is used as the separator string. NULL arguments are ignored.
```
SELECT concat_ws(',', 'abcde', 2, NULL, 22);
```
Result:
```
  concat_ws
------------
 abcde,2,22
(1 row)
```
convert(string bytea, src_encoding name, dest_encoding name)
Converts string to dest_encoding. The original encoding is specified by src_encoding. The string must be valid in this encoding. Conversions can be defined by CREATE CONVERSION. Also there are some predefined conversions. See this link for available conversions.
```
SELECT convert('text_in_utf8', 'UTF8', 'LATIN1');
```
Result:
```
          convert
----------------------------
  x746578745f696e5f75746638
(1 row)
```
convert_from(string bytea, src_encoding name)
Converts string to the database encoding. The original encoding is specified by src_encoding. The string must be valid in this encoding.
```
SELECT convert_from('text_in_utf8', 'UTF8');
```
Result:
```
 convert_from
--------------
 text_in_utf8 (A string represented by the current database encoding)
(1 row)
```

convert_to(string text, dest_encoding name)
Converts string to dest_encoding.

SELECT convert_to('some text', 'UTF8');

Result:

      convert_to
----------------------
  x736f6d652074657874
(1 row)

decode(string text, format text)
Decodes binary data from textual representation in string. Options for format are same as in encode.
```
SELECT decode('MTIzAAE=', 'base64');
```
Result:
```
    decode
--------------
  x3132330001
(1 row)
```
encode(data bytea, format text)
Encode binary data into a textual representation. Supported formats are: base64, hex, escape. escape converts zero bytes and high-bit-set bytes to octal sequences (\nnn) and doubles backslashes.
```
SELECT encode(E'123\\000\\001', 'base64');
```
Result:
```
  encode
----------
 MTIzAAE=
(1 row)
```
format(formatstr text [, formatarg “any” [, …] ])
Formats arguments according to a format string. This function is similar to the C function sprintf.
```
SELECT format('Hello %s, %1$s', 'World');
```
Result:
```
       format
--------------------
 Hello World, World
(1 row)
```
initcap(string)
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.
```
SELECT initcap('hi THOMAS');
```
Result:
```
  initcap
-----------
 Hi Thomas
(1 row)
```

length(string)
Returns the number of characters in string.

SELECT length('jose');

Result:

 length
--------
      4
(1 row)

length(string bytea, encoding name)
Returns the number of characters in string in the given encoding. The string must be valid in this encoding.
```
SELECT length('jose', 'UTF8');
```
Result:
```
 length
--------
      4
(1 row)
```
lpad(string text, length int [, fill text])
Fills up the string to length length by prepending the characters fill (a space by default). If the string is already longer than length then it is truncated (on the right).
```
SELECT lpad('hi', 5, 'xy');
```
Result:
```
 lpad
-------
 xyxhi
(1 row)
```
ltrim(string text [, characters text])
Removes the longest string containing only characters from characters (a space by default) from the start of string.
```
SELECT ltrim('zzzytest', 'xyz');
```
Result:
```
 ltrim
-------
  test
(1 row)
```

md5(string)
Calculates the MD5 hash of string, returning the result in hexadecimal.

SELECT md5('abc');

Result:

                md5
----------------------------------
 900150983cd24fb0d6963f7d28e17f72
(1 row)

parse_ident(qualified_identifier text [, strictmode boolean DEFAULT true])
Splits qualified_identifier into an array of identifiers, removing any quoting of individual identifiers. By default, extra characters after the last identifier are considered an error; but if the second parameter is false, then such extra characters are ignored. (This behavior is useful for parsing names for objects like functions). Note that this function does not truncate over-length identifiers. If you want truncation you can cast the result to name[].
```
SELECT parse_ident('"SomeSchema".someTable');
```
Result:
```
       parse_ident
------------------------
 {SomeSchema,sometable}
(1 row)
```

pg_client_encoding()
Returns current client encoding name.

SELECT pg_client_encoding();

Result:

 pg_client_encoding
--------------------
     SQL_ASCII
(1 row)

quote_ident(string text)
Returns the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled.
```
SELECT quote_ident('Foo bar');
```
Result:
```
 quote_ident
-------------
   "Foo bar"
(1 row)
```
quote_literal(string text)
Returns the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. Note that quote_literal returns null on null input; if the argument might be null, quote_nullable is often more suitable.
```
SELECT quote_literal(E'O\'Reilly');
```
Result:
```
 quote_literal
---------------
   'O''Reilly'
(1 row)
```
quote_literal(value anyelement)
Coerces the given value to text and then quote it as a literal. Embedded single-quotes and backslashes are properly doubled.
```
SELECT quote_literal(42.5);
```
Result:
```
 quote_literal
---------------
        '42.5'
(1 row)
```
quote_nullable(string text)
Returns the given string suitably quoted to be used as a string literal in an statement string; or, if the argument is null, return NULL. Embedded single-quotes and backslashes are properly doubled.
```
SELECT quote_nullable(NULL);
```
Result:
```
 quote_nullable
---------------
          NULL
(1 row)
```
quote_nullable(value anyelement)
Coerces the given value to text and then quote it as a literal; or, if the argument is null, return NULL. Embedded single-quotes and backslashes are properly doubled.
```
SELECT quote_nullable(42.5);
```
Result:
```
 quote_nullable
----------------
         '42.5'
(1 row)
```
regexp_matches(string text, pattern text [, flags text])
Returns captured substring(s) resulting from the first match of a POSIX regular expression to the string.
```
SELECT regexp_matches('foobarbequebaz', '(bar)(beque)');
```
Result:
```
 regexp_matches
----------------
    {bar,beque}
(1 row)
```
regexp_replace(string text, pattern text, replacement text [, flags text])
Replaces substring(s) matching a POSIX regular expression.
```
SELECT regexp_replace('Thomas', '.[mN]a.', 'M');
```
Result:
```
 regexp_replace
----------------
            ThM
(1 row)
```

regexp_split_to_array(string text, pattern text [, flags text])
Splits string using a POSIX regular expression as the delimiter.

SELECT regexp_split_to_array('hello world', E'\\s+');

Result:

 regexp_split_to_array
-----------------------
         {hello,world}
(1 row)

regexp_split_to_table(string text, pattern text [, flags text])
Splits string using a POSIX regular expression as the delimiter.

SELECT regexp_split_to_table('hello world', E'\\s+');

Result:

 regexp_split_to_array
-----------------------
                 hello
                 world
(2 rows)

repeat(string text, number int)
Repeats string the specified number of times.
```
SELECT repeat('Pg', 4);
```
Result:
```
  repeat
----------
 PgPgPgPg
(1 row)
```
replace(string text, from text, to text)
Replaces all occurrences in string of substring from with substring to.
```
SELECT replace('abcdefabcdef', 'cd', 'XX');
```
Result:
```
    replace
--------------
 abXXefabXXef
(1 row)
```

reverse(str)
Return reversed string.

SELECT reverse('abcde');

Result:

 reverse
---------
   edcba
(1 row)

right(str text, n int)
Returns last n characters in the string. When n is negative, it returns all but first |n| characters.
```
SELECT right('abcde', 2);
```
Result:
```
 right
-------
    de
(1 row)
```
rpad(string text, length int [, fill text])
Fills up the string to length length by appending the characters fill (a space by default). If the string is already longer than length then it is truncated.
```
SELECT rpad('hi', 5, 'xy');
```
Result:
```
 rpad
-------
 hixyx
(1 row)
```
rtrim(string text [, characters text])
Removes the longest string containing only characters from characters (a space by default) from the end of string.
```
SELECT rtrim('testxxzx', 'xyz');
```
Result:
```
 rtrim
-------
  test
(1 row)
```
split_part(string text, delimiter text, field int)
Splits string on delimiter and return the given field (counting from one).
```
SELECT split_part('abc~@~def~@~ghi', '~@~', 2);
```
Result:
```
 split_part
------------
        def
(1 row)
```
strpos(string, substring)
Location of specified substring (same as position (substring in string), but note the reversed argument order).
```
SELECT strpos('high', 'ig');
```
Result:
```
 strpos
--------
      2
(1 row)
```
substr(string, from [, count])
Extracts substring (same as substring(string from from for count)).
```
SELECT substr('alphabet', 3, 2);
```
Result:
```
 substr
--------
     ph
(1 row)
```
to_ascii(string text [, encoding text])
Converts string to ASCII from another encoding (only supports conversion from LATIN1, LATIN2, LATIN9, and WIN1250 encodings).
```
SELECT to_ascii('Karel');
```
Result:
```
 to_ascii
----------
    Karel
(1 row)
```
to_hex(number int or bigint)
Converts number to its equivalent hexadecimal representation.
```
SELECT to_hex(2147483647);
```
Result:
```
  to_hex
----------
 7fffffff
(1 row)
```
translate(string text, from text, to text)
Any character in string that matches a character in the from set is replaced by the corresponding character in the to set. If from is longer than to, occurrences of the extra characters in from are removed.
```
SELECT translate('12345', '143', 'ax');
```
Result:
```
 translate
-----------
      a2x5
(1 row)
```

Binary String functions

Defines some string functions that use key words, rather than commas, to separate arguments.

octet_length(string)
Returns the number of bytes in binary string.

SELECT octet_length(E'jo\\000se'::bytea);

Result:

 octet_length
--------------
            5
(1 row)

overlay(string placing string from int [for int])
Replaces substring.

SELECT overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea FROM 2 for 3);

Result:

     overlay
----------------
  x5402036d6173
(1 row)

position(substring in string)
Returns the location of specified substring.

SELECT position(E'\\000om'::bytea in E'Th\\000omas'::bytea);

Result:

 position
----------
        3
(1 row)

substring(string [from int] [for int])
Extracts substring.

SELECT substring(E'Th\\000omas'::bytea FROM 2 for 3);

Result:

 substring
-----------
  x68006f
(1 row)

trim([both] bytes from string)
Removes the longest string containing only bytes appearing in bytes from the start and end of string.
```
SELECT trim(E'\\000\\001'::bytea FROM E'\\000Tom\\001'::bytea);
```
Result:
```
   trim
----------
  x546f6d
(1 row)
```
btrim(string bytea, bytes bytea)
Removes the longest string containing only bytes appearing in bytes from the start and end of string.
```
SELECT btrim(E'\\000trim\\001'::bytea, E'\\000\\001'::bytea);
```
Result:
```
    btrim
------------
 x7472696d
(1 row)
```
decode(string text, format text)
Decodes binary data from textual representation in string. Options for format are same as in encode.
```
SELECT decode(E'123\\000456', 'escape');
```
Result:
```
      decode
------------------
 x31323300343536
(1 row)
```
encode(data bytea, format text)
Encodes binary data into a textual representation. Supported formats are: base64, hex, escape. escape converts zero bytes and high-bit-set bytes to octal sequences (\nnn) and doubles backslashes.
```
SELECT encode(E'123\\000456'::bytea, 'escape');
```
Result:
```
   encode
------------
 123\000456
(1 row)
```

get_bit(string, offset)
Extracts bit from string.

SELECT get_bit(E'Th\\000omas'::bytea, 45);

Result:

 get_bit
---------
       1
(1 row)

get_byte(string, offset)
Extract byte from string.

SELECT get_byte(E'Th\\000omas'::bytea, 4);

Result:

 get_byte
----------
      109
(1 row)

length(string)
Returns the length of binary string.

SELECT length(E'jo\\000se'::bytea);

Result:

 length
--------
      5
(1 row)

md5(string)
Calculates the MD5 hash of string, returning the result in hexadecimal.

SELECT md5(E'Th\\000omas'::bytea);

Result:

                md5
----------------------------------
 8ab2d3c9689aaf18b4958c334c82d8b1
(1 row)

set_bit(string, offset, newvalue)
Returns the set bit in string.

SELECT set_bit(E'Th\\000omas'::bytea, 45, 0);

Result:

      set_bit
------------------
  x5468006f6d4173
(1 row)

set_byte(string, offset, newvalue)
Returns the set byte in string.

SELECT set_byte(E'Th\\000omas'::bytea, 4, 64);

Result:

     set_byte
------------------
  x5468006f406173
(1 row)

Date Type Formatting functions

The formatting functions provide a powerful set of tools for converting various data types (date/time, integer, floating point, and 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.

to_char(timestamp, text)
Converts timestamp to string.

SELECT to_char(current_timestamp, 'HH12:MI:SS');

Result:

 to_char
----------
 04:56:02
(1 row)

to_char(interval, text)
Converts interval to string.

SELECT to_char(interval '15h 2m 12s', 'HH24:MI:SS');

Result:

 to_char
----------
 15:02:12
(1 row)

to_char(int, text)
Converts integer to string.

SELECT to_char(125, '999');

Result:

 to_char
----------
      125
(1 row)

to_char(double precision, text)
Converts real/double precision to string.

SELECT to_char(125.8::real, '999D9');

Result:

 to_char
----------
    125.8
(1 row)

to_char(numeric, text)
Converts numeric to string.

SELECT to_char(-125.8, '999D99S');

Result:

 to_char
----------
  125.80-
(1 row)

to_date(text, text)
Converts string to date.

SELECT to_date('05 Dec 2000', 'DD Mon YYYY');

Result:

   to_date
------------
 2000-12-05
(1 row)

to_number(text, text)
Converts string to numeric.

SELECT to_number('12,454.8-', '99G999D9S');

Result:

 to_number
-----------
  -12454.8
(1 row)

to_timestamp(text, text)
Converts string to timestamp.

SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY');

Result:

      to_timestamp
------------------------
 2000-12-05 00:00:00+09
(1 row)

Date/Time functions

All the functions and operators described below that take time or timestamp inputs actually come in two variants: one that takes time with time zone or timestamp with time zone, and one that takes time without time zone or timestamp without time zone. For brevity, these variants are not shown separately. Also, + and * operators come in commutative pairs (for example both date + integer and integer + date); we show only one of each such pair.

age(timestamp, timestamp)
Subtracts arguments, producing a result that uses years and months, rather than just days.

SELECT age(timestamp '2001-04-10', timestamp '1957-06-13');

Result:

          age
-------------------------
 43 years 9 mons 27 days
(1 row)

age(timestamp)
Subtracts from current_date (at midnight).

SELECT age(timestamp '1957-06-13');

Result:

          age
-------------------------
 60 years 3 mons 15 days
(1 row)

clock_timestamp()
Returns current date and time (changes during statement execution).

SELECT clock_timestamp();

Result:

        clock_timestamp
-------------------------------
 2017-09-28 17:47:31.208076+09
(1 row)

current_date
Returns current date.

SELECT current_date;

Result:

    date
------------
 2017-09-28
(1 row)

current_time
Returns current time of day.

SELECT current_time;

Result:

       timetz
--------------------
 17:53:23.972231+09
(1 row)

current_timestamp
Returns current date and time.

SELECT current_timestamp;

Result:

              now
-------------------------------
 2017-09-28 18:01:43.890977+09
(1 row)

date_part(text, timestamp)
Returns subfield specified in text.

SELECT date_part('hour', timestamp '2001-02-16 20:38:40');

Result:

 date_part
-----------
        20
(1 row)

date_part(text, interval)
Returns subfield specified in text.

SELECT date_part('month', interval '2 years 3 months');

Result:

 date_part
-----------
         3
(1 row)

date_trunc(text, timestamp)
Truncates to specified precision.

SELECT date_trunc('hour', timestamp '2001-02-16 20:38:40');

Result:

      date_trunc
---------------------
 2001-02-16 20:00:00
(1 row)

date_trunc(text, interval)
Truncates to specified precision.

SELECT date_trunc('hour', interval '2 days 3 hours 40 minutes');

Result:

    date_trunc
-----------------
 2 days 03:00:00
(1 row)

extract(field from timestamp)
Extracts the specified field.

SELECT extract(hour FROM timestamp '2001-02-16 20:38:40');

Result:

 date_part
-----------
        20
(1 row)

extract(field from interval)
Extracts the specified field.

SELECT extract(month FROM interval '2 years 3 months');

Result:

 date_part
-----------
         3
(1 row)

isfinite(date)
Returns the result of testing whether the input argument is finite (no +/- infinite).
```
SELECT isfinite(date '2001-02-16');
```
Result:
```
 isfinite
----------
        t
(1 row)
```
isfinite(timestamp)
Returns the result of testing whether the input argument is finite (no +/- infinite).
```
SELECT isfinite(timestamp '2001-02-16 21:28:30');
```
Result:
```
 isfinite
----------
        t
(1 row)
```

isfinite(interval)
Returns the result of testing whether the input argument is finite.

SELECT isfinite(interval '4 hours');

Result:

 isfinite
----------
        t
(1 row)

justify_days(interval)
Adjusts interval so 30-day time periods are represented as months.

SELECT justify_days(interval '35 days');

Result:

 justify_days
--------------
 1 mon 5 days
(1 row)

justify_hours(interval)
Adjusts interval so 24-hour time periods are represented as days.

SELECT justify_hours(interval '27 hours');

Result:

 justify_hours
----------------
 1 day 03:00:00
(1 row)

justify_interval(interval)
Adjusts interval using justify_days and justify_hours, with additional sign adjustments.

SELECT justify_interval(interval '1 mon -1 hour');

Result:

 justify_interval
------------------
 29 days 23:00:00
(1 row)

localtime
Returns current time of day.

SELECT localtime;

Result:

      time
----------------
 11:27:04.72722
(1 row)

localtimestamp
Returns current date and time (start of current transaction).

SELECT localtimestamp;

Result:

          timestamp
----------------------------
 2017-09-29 11:29:52.230028
(1 row)

make_date(year int, month int, day int)
Creates date from year, month and day fields.

SELECT make_date(2013, 7, 15);

Result:

 make_date
------------
 2013-07-15
(1 row)

make_interval(years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0)
Creates interval from years, months, weeks, days, hours, minutes and seconds fields.
```
SELECT make_interval(days => 10);
```
Result:
```
 make_interval
---------------
       10 days
(1 row)
```
make_time(hour int, min int, sec double precision)
Creates time from hour, minute and seconds fields.
```
SELECT make_time(8, 15, 23.5);
```
Result:
```
 make_time
------------
 08:15:23.5
(1 row)
```
make_timestamp(year int, month int, day int, hour int, min int, sec double precision)
Creates timestamp from year, month, day, hour, minute and seconds fields.
```
SELECT make_timestamp(2013, 7, 15, 8, 15, 23.5);
```
Result:
```
    make_timestamp
-----------------------
 2013-07-15 08:15:23.5
(1 row)
```
make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [ timezone text ])
Creates timestamp with time zone from year, month, day, hour, minute and seconds fields; if timezone is not specified, the current time zone is used.
```
SELECT make_timestamptz(2013, 7, 15, 8, 15, 23.5);
```
Result:
```
      make_timestampz
--------------------------
 2013-07-15 08:15:23.5+01
(1 row)
```

now()
Returns current date and time (start of current transaction).

SELECT now();

Result:

              now              
-------------------------------
 2017-10-11 16:09:51.154262+09
(1 row)

statement_timestamp()
Returns current date and time.

SELECT statement_timestamp();

Result:

      statement_timestamp      
-------------------------------
 2017-10-11 16:08:59.641426+09
(1 row)

timeofday()
Returns current date and time.

SELECT timeofday();

Result:

              timeofday              
-------------------------------------
 Wed Oct 11 16:09:26.934061 2017 KST
(1 row)

transaction_timestamp()
Returns current date and time.

SELECT transaction_timestamp();

Result:

     transaction_timestamp     
-------------------------------
 2017-10-11 16:10:21.530521+09
(1 row)

to_timestamp(double precision)
Converts Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp.

SELECT to_timestamp(1284352323);

Result:

      to_timestamp      
------------------------
 2010-09-13 13:32:03+09
(1 row)

Enum Support functions

For enum types, there are several functions that allow cleaner programming without hard-coding particular values of an enum type.

To execute the example in the function description, create an enum type as shown below first.

CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple');

enum_first(anyenum)
Returns the first value of the input enum type.

SELECT enum_first(null::rainbow);

Result:

 enum_first 
------------
     red
(1 row)

enum_last(anyenum)
Returns the last value of the input enum type.

SELECT enum_last(null::rainbow);

Result:

 enum_last
-----------
  purple
(1 row)

enum_range(anyenum)
Returns all values of the input enum type in an ordered array.

SELECT enum_range(null::rainbow);

Result:

              enum_range               
---------------------------------------
 {red,orange,yellow,green,blue,purple}
(1 row)

enum_range(anyenum, anyenum)
Returns the range between the two given enum values, as an ordered array. The values must be from the same enum type. If the first parameter is null, the result will start with the first value of the enum type. If the second parameter is null, the result will end with the last value of the enum type.

SELECT enum_range('orange'::rainbow, 'green'::rainbow);

Result:

      enum_range
-----------------------
 {orange,yellow,green}
(1 row)

SELECT enum_range(NULL, 'green'::rainbow);

Result:

        enum_range
---------------------------
 {red,orange,yellow,green}
(1 row)

SELECT enum_range('orange'::rainbow, NULL);

Result:

            enum_range
-----------------------------------
 {orange,yellow,green,blue,purple}
(1 row)

Geometric Functions

area(object)
Returns area.

SELECT area(box '((0,0),(1,1))');

Result:

 area
------
    1
(1 row)

center(object)
Returns the center coordinates of object.

SELECT center(box '((0,0),(1,2))');

Result:

 center
--------
 (0.5,1)
(1 row)

diameter(circle)
Returns the diameter of circle.

SELECT diameter(circle '((0,0),2.0)');

Result:

 diameter
----------
        4
(1 row)

height(box)
Returns the vertical size of box.

SELECT height(box '((0,0),(1,1))');

Result:

 height
--------
      1
(1 row)

isclosed(path)
Returns a logical value indicating whether the input path is a closed path.

SELECT isclosed(path '((0,0),(1,1),(2,0))');

Result:

 isclosed
----------
        t
(1 row)

isopen(path)
Returns a logical value indicating whether the input path is an open path.
```
SELECT isopen(path '[(0,0),(1,1),(2,0)]');
```
Result:
```
 isopen
--------
      t
(1 row)
```

length(object)
Returns length of the path.

SELECT length(path '((-1,0),(1,0))');

Result:

 length
--------
      4
(1 row)

npoints(path)
Returns the number of points of the input path.

SELECT npoints(path '[(0,0),(1,1),(2,0)]');

Result:

 npoints
---------
       3
(1 row)

npoints(polygon)
Returns the number of polygon points.

SELECT npoints(polygon '((1,1),(0,0))');

Result:

 npoints
---------
       2
(1 row)

pclose(path)
Converts the input path to closed.

SELECT pclose(path '[(0,0),(1,1),(2,0)]');

Result:

       pclose        
---------------------
 ((0,0),(1,1),(2,0))
(1 row)

popen(path)
Converts the input path to open.

SELECT popen(path '((0,0),(1,1),(2,0))');

Result:

        popen        
---------------------
 [(0,0),(1,1),(2,0)]
(1 row)

radius(circle)
Returns the radius of circle.

SELECT radius(circle '((0,0),2.0)');

Result:

 radius 
--------
      2
(1 row)

width(box)
Returns the radius of circle.

SELECT width(box '((0,0),(1,1))');

Result:

 width 
-------
     1
(1 row)

box(circle)
Returns a box circumscribed about circle.

SELECT box(circle '((0,0),2.0)');

Result:

                                    box                                    
---------------------------------------------------------------------------
 (1.41421356237309,1.41421356237309),(-1.41421356237309,-1.41421356237309)
(1 row)

box(point)
Returns a box whose width is zero (empty box) centered on the input point.

SELECT box(point '(0,0)');

Result:

     box     
-------------
 (0,0),(0,0)
(1 row)

box(point, point)
Returns a box whose vertices are the two input points.

SELECT box(point '(0,0)', point '(1,1)');

Result:

     box     
-------------
 (1,1),(0,0)
(1 row)

box(polygon)
Returns a box circumscribed about polygon.

SELECT box(polygon '((0,0),(1,1),(2,0))');

Result:

     box     
-------------
 (2,1),(0,0)
(1 row)

bound_box(box, box)
Returns the smallest box that contains the two boxes entered.

SELECT bound_box(box '((0,0),(1,1))', box '((3,3),(4,4))');

Result:

  bound_box  
-------------
 (4,4),(0,0)
(1 row)

circle(box)
Returns a circle circumscribed about box.

SELECT circle(box '((0,0),(1,1))');

Result:

            circle             
-------------------------------
 <(0.5,0.5),0.707106781186548>
(1 row)

circle(point, double precision)
Returns the circle created using the center coordinates and radius of the input circle.
```
SELECT circle(point '(0,0)', 2.0);
```
Result:
```
  circle   
-----------
 <(0,0),2>
(1 row)
```

circle(polygon)
Returns a circle with the average of the input coordinate pairs as the center of the circle and the average distance from the point to the input coordinate pair as the radius.

SELECT circle(polygon '((0,0),(1,1),(2,0))');

Result:

                  circle                   
-------------------------------------------
 <(1,0.333333333333333),0.924950591148529>
(1 row)

line(point, point)
Returns the value of the line.

SELECT line(point '(-1,0)', point '(1,0)');

Result:

   line   
----------
 {0,-1,0}
(1 row)

lseg(box)
Returns box diagonal to line segment.

SELECT lseg(box '((-1,0),(1,0))');

Result:

      lseg      
----------------
 [(1,0),(-1,0)]
(1 row)

lseg(point, point)
Returns a line segment with two input points taken as start and end points.

SELECT lseg(point '(-1,0)', point '(1,0)');

Result:

      lseg      
----------------
 [(1,0),(-1,0)]
(1 row)

path(polygon)
Returns a polygon path.

SELECT path(polygon '((0,0),(1,1),(2,0))');

Result:

        path         
---------------------
 ((0,0),(1,1),(2,0))
(1 row)

point(double precision, double precision)
Returns the value that construct the point.

SELECT point(23.4, -44.5);

Result:

    point     
--------------
 (23.4,-44.5)
(1 row)

point(box)
Returns center of box.

SELECT point(box '((-1,0),(1,0))');

Result:

 point 
-------
 (0,0)
(1 row)

point(circle)
Returns center of circle.

SELECT point(circle '((0,0),2.0)');

Result:

 point 
-------
 (0,0)
(1 row)

point(lseg)
Returns center of line segment.

SELECT point(lseg '((-1,0),(1,0))');

Result:

 point 
-------
 (0,0)
(1 row)

point(polygon)
Returns center of polygon.

SELECT point(polygon '((0,0),(1,1),(2,0))');

Result:

         point         
-----------------------
 (1,0.333333333333333)
(1 row)

polygon(box)
Returns box to 4-point polygon.

SELECT polygon(box '((0,0),(1,1))');

Result:

          polygon          
---------------------------
 ((0,0),(0,1),(1,1),(1,0))
(1 row)

polygon(circle)
Returns circle to 12-point polygon.

SELECT polygon(circle '((0,0),2.0)');

Result:

                                        polygon                                           
-----------------------------------------------------------------------------------------
 ((-2,0),(-1.73205080756888,1),(-1,1.73205080756888),(-1.22464679914735e-16,2),
 (1,1.73205080756888),(1.73205080756888,1),(2,2.44929359829471e-16),
 (1.73205080756888,-0.999999999999999),(1,-1.73205080756888),(3.67394039744206e-16,-2),
 (-0.999999999999999,-1.73205080756888),(-1.73205080756888,-1))
(1 row)

polygon(npts, circle)
Returns circle to npts-point polygon.

SELECT polygon(12, circle '((0,0),2.0)');

Result:

                                        polygon                                           
-----------------------------------------------------------------------------------------
 ((-2,0),(-1.73205080756888,1),(-1,1.73205080756888),(-1.22464679914735e-16,2),
 (1,1.73205080756888),(1.73205080756888,1),(2,2.44929359829471e-16),
 (1.73205080756888,-0.999999999999999),(1,-1.73205080756888),(3.67394039744206e-16,-2),
 (-0.999999999999999,-1.73205080756888),(-1.73205080756888,-1))
(1 row)

polygon(path)
Converts path into a polygon.

SELECT polygon(path '((0,0),(1,1),(2,0))');

Result:

       polygon       
---------------------
 ((0,0),(1,1),(2,0))
(1 row)

Network Address Functions

abbrev(inet)
Returns abbreviated display format as text.

SELECT abbrev(inet '10.1.0.0/16');

Result:

abbrev    
-------------
10.1.0.0/16
(1 row)

abbrev(cidr)
Returns abbreviated display format as text.

SELECT abbrev(cidr '10.1.0.0/16');

Result:

abbrev  
---------
10.1/16
(1 row)

broadcast(inet)
Returns broadcast address for network.

SELECT broadcast('192.168.1.5/24');

Result:

    broadcast     
------------------
192.168.1.255/24
(1 row)

family(inet)
Extracts family of address; 4 for IPv4, 6 for IPv6.

SELECT 	family('::1');

Result:

 family 
--------
      6
(1 row)

host(inet)
Extracts IP address as text.

SELECT 	host('192.168.1.5/24');

Result:

    host     
-------------
 192.168.1.5
(1 row)

hostmask(inet)
Constructs host mask for network.

SELECT hostmask('192.168.23.20/30');

Result:

 hostmask 
----------
  0.0.0.3
(1 row)

masklen(inet)
Extracts netmask length.

SELECT masklen('192.168.1.5/24');

Result:

 masklen 
---------
      24
(1 row)

netmask(inet)
Constructs netmask for network.

SELECT netmask('192.168.1.5/24');

Result:

    netmask    
---------------
 255.255.255.0
(1 row)

network(inet)
Extracts network part of address.

SELECT network('192.168.1.5/24');

Result:

    network     
----------------
 192.168.1.0/24
(1 row)

set_masklen(inet, int)
Returns the set netmask length for inet value.

SELECT set_masklen('192.168.1.5/24', 16);

Result:

  set_masklen   
----------------
 192.168.1.5/16
(1 row)

set_masklen(cidr, int)
Returns the set netmask length for cidr value.

SELECT set_masklen('192.168.1.0/24'::cidr, 16);

Result:

  set_masklen   
----------------
 192.168.0.0/16
(1 row)

text(inet)
Returns IP address and netmask length as text.

SELECT text(inet '192.168.1.5');

Result:

      text      
----------------
 192.168.1.5/32
(1 row)

inet_same_family(inet, inet)
Returns a logical value indicating whether the address is a family value.

SELECT inet_same_family('192.168.1.5/24', '::1');

Result:

 inet_same_family 
------------------
        f
(1 row)

inet_merge(inet, inet)
Returns the smallest network which includes all of the entered networks.

SELECT inet_merge('192.168.1.5/24', '192.168.2.5/24');

Result:

   inet_merge   
----------------
 192.168.0.0/22
(1 row)

trunc(macaddr)
Sets the last 3 bytes to zero.

SELECT trunc(macaddr '12:34:56:78:90:ab');

Result:

       trunc       
-------------------
 12:34:56:00:00:00
(1 row)

Text Search Functions

array_to_tsvector(text[])
Converts array of lexemes to tsvector.

SELECT array_to_tsvector('{fat,cat,rat}'::text[]);

Result:

 array_to_tsvector 
-------------------
 'cat' 'fat' 'rat'
(1 row)

get_current_ts_config()
Returns the default text search configuration.

SELECT get_current_ts_config();

Result:

 get_current_ts_config 
-----------------------
               english
(1 row)

length(tsvector)
Returns number of lexemes in tsvector.

SELECT length('fat:2,4 cat:3 rat:5A'::tsvector);

Result:

 length 
--------
      3
(1 row)

numnode(tsquery)
Returns the number of lexemes plus operators in tsquery.

SELECT numnode('(fat & rat) | cat'::tsquery);

Result:

 numnode 
---------
       5
(1 row)

plainto_tsquery([ config regconfig , ] query text)
Produces tsquery ignoring punctuation.

SELECT plainto_tsquery('english', 'The Fat Rats');

Result:

 plainto_tsquery 
-----------------
  'fat' & 'rat'
(1 row)

phraseto_tsquery([ config regconfig , ] query text)
Produces tsquery that searches for a phrase, ignoring punctuation.

SELECT phraseto_tsquery('english', 'The Fat Rats');

Result:

 phraseto_tsquery 
------------------
 'fat' <-> 'rat'
(1 row)

querytree(query tsquery)
Returns indexable part of a tsquery.

SELECT querytree('foo & ! bar'::tsquery);

Result:

 querytree 
-----------
   'foo'
(1 row)

setweight(vector tsvector, weight “char”)
Assigns weight to each element of vector.

SELECT setweight('fat:2,4 cat:3 rat:5B'::tsvector, 'A');

Result:

          setweight           
-------------------------------
 'cat':3A 'fat':2A,4A 'rat':5A     
(1 row)

setweight(vector tsvector, weight “char”, lexemes text[])
Assigns weight to elements of vector that are listed in lexemes.

SELECT setweight('fat:2,4 cat:3 rat:5B'::tsvector, 'A', '{cat,rat}');

Result:

          setweight          
-----------------------------
 'cat':3A 'fat':2,4 'rat':5A
(1 row)

strip(tsvector)
Removes positions and weights from tsvector.

SELECT strip('fat:2,4 cat:3 rat:5A'::tsvector);

Result:

       strip       
-------------------
 'cat' 'fat' 'rat'    
(1 row)

to_tsquery([ config regconfig , ] query text)
Normalizes words and converts to tsquery.

SELECT to_tsquery('english', 'The & Fat & Rats');

Result:

  to_tsquery   
---------------
 'fat' & 'rat'     
(1 row)

to_tsvector([ config regconfig , ] document text)
Reduce document text to tsvector.

SELECT to_tsvector('english', 'The Fat Rats');

Result:

   to_tsvector   
-----------------
 'fat':2 'rat':3    
(1 row)

ts_delete(vector tsvector, lexeme text)
Removes given lexeme from vector.

SELECT ts_delete('fat:2,4 cat:3 rat:5A'::tsvector, 'fat');

Result:

    ts_delete     
------------------
 'cat':3 'rat':5A     
(1 row)

ts_delete(vector tsvector, lexemes text[])
Removes any occurrence of lexemes in lexemes from vector.

SELECT ts_delete('fat:2,4 cat:3 rat:5A'::tsvector, ARRAY['fat','rat']);

Result:

 ts_delete 
-----------
 'cat':3     
(1 row)

ts_filter(vector tsvector, weights “char”[])
Selects only elements with given weights from vector.

SELECT ts_filter('fat:2,4 cat:3b rat:5A'::tsvector, '{a,b}');

Result:

     ts_filter     
-------------------
 'cat':3B 'rat':5A     
(1 row)

ts_headline([ config regconfig, ] document text, query tsquery [, options text ])
Displays a query match.

SELECT ts_headline('x y z', 'z'::tsquery);

Result:

 ts_headline  
--------------
 x y <b>z</b>     
(1 row)

ts_rank([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ])
Ranks documents for query.

SELECT ts_rank(to_tsvector('This is an example of document'), to_tsquery('example'));

Result:

  ts_rank  
-----------
 0.0607927
(1 row)

ts_rank_cd([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ])
Ranks documents for query using cover density.

SELECT ts_rank_cd(to_tsvector('This is an example of document'), to_tsquery('example'));

Result:

 ts_rank_cd 
------------
        0.1
(1 row)

ts_rewrite(query tsquery, target tsquery, substitute tsquery)
Replaces target with substitute within query.

SELECT ts_rewrite('a & b'::tsquery, 'a'::tsquery, 'foo|bar'::tsquery);

Result:

       ts_rewrite        
-------------------------
 'b' & ( 'foo' | 'bar' )     
(1 row)

ts_rewrite(query tsquery, select text)
Replaces the first column value of the SELECT result with the second column value of the SELECT result.

create table aliases (t tsquery primary key, s tsquery);
insert into aliases values ('a', 'foo|bar');

SELECT ts_rewrite('a & b'::tsquery, 'SELECT t,s FROM aliases');

Result:

       ts_rewrite        
-------------------------
 'b' & ( 'bar' | 'foo' )     
(1 row)

tsquery_phrase(query1 tsquery, query2 tsquery)
Makes query that searches for query1 followed by query2 (same as <-> operator).

SELECT tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'));

Result:

 tsquery_phrase  
-----------------
 'fat' <-> 'cat'     
(1 row)

tsquery_phrase(query1 tsquery, query2 tsquery, distance integer)
Makes query that searches for query1 followed by query2 at distance distance.

SELECT tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'), 10);

Result:

  tsquery_phrase  
------------------
 'fat' <10> 'cat'     
(1 row)

tsvector_to_array(tsvector)
Converts tsvector to array of lexemes.

SELECT tsvector_to_array('fat:2,4 cat:3 rat:5A'::tsvector);

Result:

 tsvector_to_array 
-------------------
   {cat,fat,rat}     
(1 row)

tsvector_update_trigger()
Triggers the function for automatic tsvector column update.

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON messages 
FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(tsv, 'pg_catalog.english', title, body);

INSERT INTO messages VALUES ('title here', 'the body text is here');

SELECT * FROM messages;

Result:

   title    |         body          |            tsv             
------------+-----------------------+----------------------------
 title here | the body text is here | 'bodi':4 'text':5 'titl':1   
(1 row)

tsvector_update_trigger_column()
Triggers the function for automatic tsvector column update.
```
CREATE TRIGGER ... tsvector_update_trigger_column(tsv, configcol, title, body);
```

unnest(tsvector, OUT lexeme text, OUT positions smallint[], OUT weights text)
Expands a tsvector to a set of rows.

SELECT unnest('fat:2,4 cat:3 rat:5A'::tsvector);

Result:

        unnest         
-----------------------
 (cat,{3},{D})
 (fat,"{2,4}","{D,D}")
 (rat,{5},{A})    
(3 row)

ts_debug([ config regconfig, ] document text, OUT alias text, OUT description text, OUT token text, OUT dictionaries regdictionary[], OUT dictionary regdictionary, OUT lexemes text[])
Tests a configuration.

SELECT ts_debug('english', 'The Brightest supernovaes');

Result:

                                     ts_debug                                      
-----------------------------------------------------------------------------------
 (asciiword,"Word, all ASCII",The,{english_stem},english_stem,{})
 (blank,"Space symbols"," ",{},,)
 (asciiword,"Word, all ASCII",Brightest,{english_stem},english_stem,{brightest})
 (blank,"Space symbols"," ",{},,)
 (asciiword,"Word, all ASCII",supernovaes,{english_stem},english_stem,{supernova})
(5 row)

ts_lexize(dict regdictionary, token text)
Tests a dictionary.

SELECT ts_lexize('english_stem', 'stars');

Result:

 ts_lexize 
-----------
  {star}
(1 row)

ts_parse(parser_name text, document text, OUT tokid integer, OUT token text)
Tests a parser.

SELECT ts_parse('default', 'foo - bar');

Result:

 ts_parse  
-----------
 (1,foo)
 (12," ")
 (12,"- ")
 (1,bar)
(4 row)

ts_parse(parser_oid oid, document text, OUT tokid integer, OUT token text)
Tests a parser with oid.

SELECT ts_parse(3722, 'foo - bar');

Result:

 ts_parse  
-----------
 (1,foo)
 (12," ")
 (12,"- ")
 (1,bar)
(4 row)

ts_token_type(parser_name text, OUT tokid integer, OUT alias text, OUT description text)
Gets token types defined by parser.

SELECT ts_token_type('default');

Result:

         ts_token_type                         
---------------------------------
 (1,asciiword,"Word, all ASCII")
 ...
(23 row)     

ts_token_type(parser_oid oid, OUT tokid integer, OUT alias text, OUT description text)
Gets token types defined by parser.

SELECT ts_token_type(3722);

Result:

         ts_token_type                         
---------------------------------
 (1,asciiword,"Word, all ASCII")
 ...
(23 row)     

ts_stat(sqlquery text, [ weights text, ] OUT word text, OUT ndoc integer, OUT nentry integer)
Returns statistics of a tsvector column.
```
SELECT ts_stat('SELECT vector FROM apod');
```
Result:
```
  ts_stat 
------------    
(foo,10,15)
...
(4 row)
```

JSON Functions

to_json(anyelement), to_jsonb(anyelement)
Returns the value as json or jsonb. Arrays and composites are converted to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion or a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid json or jsonb value.
```
SELECT to_json('Fred said "Hi."'::text);
```
Result:
```
       to_json
---------------------
 "Fred said "Hi.\""
(1 row)
```

array_to_json(anyarray [, pretty_bool])
Returns the array as a JSON array. Line feeds will be added between dimension-1 elements if returns pretty_bool is true.

SELECT array_to_json('{{1,5},{99,100}}'::int[]);

Result:

  array_to_json   
------------------
 [[1,5],[99,100]]
(1 row)
           
SELECT array_to_json('{{1,5},{99,100}}'::int[], true);

Result:

 array_to_json 
---------------
 [[1,5],      +
  [99,100]]  
(1 row)

row_to_json(record [, pretty_bool])
Returns the row as a JSON object. Line feeds will be added between level-1 elements if pretty_bool is true.
```
SELECT row_to_json(row(1,'foo'));
```
Result:
```
     row_to_json     
---------------------
 {"f1":1,"f2":"foo"}
(1 row)
```
json_build_array(VARIADIC “any”), jsonb_build_array(VARIADIC “any”)
Builds a possibly-heterogeneously-typed JSON array out of a variable argument list.
```
SELECT json_build_array(1,2,'3',4,5);
```
Result:
```
 json_build_array  
-------------------
 [1, 2, "3", 4, 5]
(1 row)
```
json_build_object(VARIADIC “any”), jsonb_build_object(VARIADIC “any”)
Builds a JSON object out of a variable argument list. By convention, the argument list consists of alternating keys and values.
```
SELECT json_build_object('foo',1,'bar',2);
```
Result:
```
   json_build_object    
------------------------
 {"foo" : 1, "bar" : 2}
(1 row)
```
json_object(text[]), jsonb_object(text[])
Builds a JSON object out of a text array. The array must have either exactly one dimension with an even number of members, in which case they are taken as alternating key/value pairs, or two dimensions such that each inner array has exactly two elements, which are taken as a key/value pair.
```
SELECT json_object('{a, 1, b, "def", c, 3.5}');
SELECT json_object('{{a, 1},{b, "def"},{c, 3.5}}');
```
Result:
```
              json_object              
---------------------------------------
 {"a" : "1", "b" : "def", "c" : "3.5"}
(1 row)
```
json_object(keys text[], values text[]), jsonb_object(keys text[], values text[])
This form of json_object takes keys and values pairwise from two separate arrays. In all other respects it is identical to the one-argument form.
```
SELECT json_object('{a, b}', '{1,2}');
```
Result:
```
      json_object       
------------------------
 {"a" : "1", "b" : "2"}
(1 row)
```

json_array_length(json), jsonb_array_length(jsonb)
Returns the number of elements in the outermost JSON array.

SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');

Result:

 json_array_length 
-------------------
                 5
(1 row)

json_each(json), jsonb_each(jsonb)
Expands the outermost JSON object into a set of key/value pairs.

SELECT * FROM json_each('{"a":"foo", "b":"bar"}');

Result:

 key | value 
-----+-------
 a   | "foo"
 b   | "bar"
(2 row)

json_each_text(json), jsonb_each_text(jsonb)
Expands the outermost JSON object into a set of key/value pairs. The returned values will be of type text.
```
SELECT * FROM json_each_text('{"a":"foo", "b":"bar"}');
```
Result:
```
 key | value 
-----+-------
 a   | foo
 b   | bar
(2 row)
```
json_extract_path(from_json json, VARIADIC path_elems text[]), jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[])
Returns JSON value pointed to by path_elems (equivalent to #> operator).
```
SELECT * FROM json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4');
```
Result:
```
  json_extract_path   
----------------------
 {"f5":99,"f6":"foo"}
(1 row)
```
json_extract_path_text(from_json json, VARIADIC path_elems text[]), jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])
Returns JSON value pointed to by path_elems as text (equivalent to #>> operator).
```
SELECT json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6');
```
Result:
```
 json_extract_path_text 
------------------------
         foo
(1 row)
```

json_object_keys(json), jsonb_object_keys(jsonb)
Returns set of keys in the outermost JSON object.

SELECT json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}');

Result:

 json_object_keys 
------------------
       f1
       f2
(2 row)

json_populate_record(base anyelement, from_json json), jsonb_populate_record(base anyelement, from_json jsonb)
Expands the object in from_json to a row whose columns match the record type defined by base.
```
CREATE TABLE myrowtype (a int, b int);

SELECT * FROM json_populate_record(null::myrowtype, '{"a":1,"b":2}');
```
Result:
```
 a | b 
---+---
 1 | 2
(1 row)
```
json_populate_recordset(base anyelement, from_json json), jsonb_populate_recordset(base anyelement, from_json jsonb)
Expands the outermost array of objects in from_json to a set of rows whose columns match the record type defined by base.
```
SELECT * FROM json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]');
```
Result:
```
 a | b 
---+---
 1 | 2
 3 | 4
(2 row)
```

json_array_elements(json), jsonb_array_elements(jsonb)
Expands a JSON array to a set of JSON values.

SELECT * FROM json_array_elements('[1,true, [2,false]]');

Result:

   value   
-----------
     1
   true
[2,false]]
(3 row)

json_array_elements_text(json), jsonb_array_elements_text(jsonb)
Expands a JSON array to a set of text values.
```
SELECT * FROM json_array_elements_text('["foo", "bar"]');
```
Result:
```
 value 
-------
  foo
  bar
(2 row)
```
json_typeof(json), jsonb_typeof(jsonb)
Returns the type of the outermost JSON value as a text string. Possible types are object, array, string, number, boolean, and null.
```
SELECT json_typeof('-123.4');
```
Result:
```
 json_typeof 
-------------
    number
(1 row)
```
json_to_record(json), jsonb_to_record(jsonb)
Builds an arbitrary record from a JSON object. As with all functions returning record, the caller must explicitly define the structure of the record with an AS clause.
```
SELECT * FROM json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}') as x(a int, b text, d text);
```
Result:
```
 a |    b    | d 
---+---------+---
 1 | [1,2,3] | 
(1 row)
```
json_to_recordset(json), jsonb_to_recordset(jsonb)
Builds an arbitrary set of records from a JSON array of objects. As with all functions returning record, the caller must explicitly define the structure of the record with an AS clause.
```
SELECT * FROM json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') 
as x(a int, b text);
```
Result:
```
 a |  b  
---+-----
 1 | foo
 2 |  
(2 row)
```
json_strip_nulls(from_json json), jsonb_strip_nulls(from_json jsonb)
Returns from_json with all object fields that have null values omitted. Other null values are unchanged.
```
SELECT json_strip_nulls('[{"f1":1,"f2":null},2,null,3]');
```
Result:
```
  json_strip_nulls   
---------------------
 [{"f1":1},2,null,3]  
(1 row)
```

jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])
Returns target with the section designated by path replaced by new_value, or with new_value added
if create_missing is true (default is true) and the item designated by path does not exist. As with the path orientated operators, negative integers that appear in path count from the end of JSON arrays.

SELECT jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false);

Result:

                  jsonb_set                  
---------------------------------------------
 [{"f1": [2, 3, 4], "f2": null}, 2, null, 3]
(1 row)
                                      
SELECT jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]');

Result:

                  jsonb_set                  
---------------------------------------------
 [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
(1 row)                                          

jsonb_insert(target jsonb, path text[], new_value jsonb, [insert_after boolean])
Returns target with new_value inserted. If target section designated by path is in a JSONB array, new_value will be inserted before target or after if insert_after is true (default is false). If target section designated by path is in JSONB object, new_value will be inserted only if target does not exist. As with the path orientated operators, negative integers that appear in path count from the end of JSON arrays.

SELECT jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"');

Result:

         jsonb_insert          
-------------------------------
 {"a": [0, "new_value", 1, 2]}
(1 row)
                                      
SELECT jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true);

Result:

         jsonb_insert          
-------------------------------
 {"a": [0, 1, "new_value", 2]}
(1 row)                                          

jsonb_pretty(from_json jsonb)
Returns from_json as indented JSON text.

SELECT jsonb_pretty('[{"f1":1,"f2":null},2,null,3]');

Result:

    jsonb_pretty    
--------------------
 [                 +
     {             +
         "f1": 1,  +
         "f2": null+
     },            +
     2,            +
     null,         +
     3             +
 ]
(1 row)

Sequence Manipulation Functions

This section describes functions for operating on sequences. Sequences can be created with CREATE SEQUENCE.

CREATE SEQUENCE serial increment by 1 start 101;

nextval(regclass)
Advances sequence and returns new value.

SELECT nextval('serial');

Result:

 nextval 
---------
     101 
(1 row)      

currval(regclass)
Returns value most recently obtained with nextval for specified sequence.
```
SELECT currval('serial');
```
Result:
```
 currval 
---------
     101 
(1 row)      
```

lastval()
Returns value most recently obtained with nextval for any sequence.

SELECT lastval();

Result:

 lastval 
---------
     101 
(1 row)      

setval(regclass, bigint)
Sets sequence’s current value.

SELECT setval('serial', 101);

Result:

 setval 
--------
    101
(1 row)      

setval(regclass, bigint, boolean)
Sets sequence’s current value and is_called flag.

-- true
SELECT setval('serial', 101, true);

Result:

 setval 
--------
    101
(1 row)

SELECT nextval('serial');

Result:

 nextval 
---------
     102
(1 row)

-- false
SELECT setval('serial', 101, false);

Result:

 setval 
--------
    101
(1 row)

SELECT nextval('serial');

Result:

 nextval 
---------
     101
(1 row)

Array Functions

array_append(anyarray, anyelement)
Appends anyelement to the end of an array.

SELECT array_append(ARRAY[1,2], 3);

Result:

 array_append 
--------------
   {1,2,3}
(1 row)      

array_cat(anyarray, anyarray)
Concatenates two arrays.

SELECT array_cat(ARRAY[1,2,3], ARRAY[4,5]);

Result:

  array_cat  
-------------
 {1,2,3,4,5}
(1 row)      

array_ndims(anyarray)
Returns the number of dimensions of the array.

SELECT array_ndims(ARRAY[[1,2,3], [4,5,6]]);

Result:

 array_ndims 
-------------
           2
(1 row)      

array_dims(anyarray)
Returns a text representation of array’s dimensions.

SELECT array_dims(ARRAY[[1,2,3], [4,5,6]]);

Result:

 array_dims 
------------
 [1:2][1:3]
(1 row)      

array_fill(anyelement, int[], [, int[]])
Returns an array initialized with optionally-supplied value and dimensions.

SELECT array_fill(7, ARRAY[3], ARRAY[2]);

Result:

  array_fill   
---------------
 [2:4]={7,7,7}
(1 row)      

array_length(anyarray, int)
Returns the length of the requested array dimension.

SELECT array_length(array[1,2,3], 1);

Result:

 array_length 
--------------
            3
(1 row)      

array_lower(anyarray, int)
Returns the lower bound of the requested array dimension.

SELECT array_lower('[0:2]={1,2,3}'::int[], 1);

Result:

 array_lower 
-------------
          0
(1 row)      

array_position(anyarray, anyelement [, int])
Returns the index of the first occurrence of the second argument in the array, starting at the element indicated by the third argument or at the first element (array must be one-dimensional).
```
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
```
Result:
```
 array_position 
----------------
              2
(1 row)      
```
array_positions(anyarray, anyelement)
Returns an array of indexes of all occurrences of the second argument in the array given as first argument (array must be one-dimensional).
```
SELECT array_positions(ARRAY['A','A','B','A'], 'A');
```
Result:
```
 array_positions 
-----------------
     {1,2,4}
(1 row)      
```

array_prepend(anyelement, anyarray)
Appends anyelement to the beginning of an array.

SELECT array_prepend(1, ARRAY[2,3]);

Result:

 array_prepend 
---------------
    {1,2,3}
(1 row)      

array_remove(anyarray, anyelement)
Removes all elements equal to the given value from the array.

SELECT array_remove(ARRAY[1,2,3,2], 2);

Result:

 array_remove 
--------------
     {1,3}
(1 row)      

array_replace(anyarray, anyelement, anyelement)
Replaces each array element equal to the given value with a new value.
```
SELECT array_replace(ARRAY[1,2,5,4], 5, 3);
```
Result:
```
 array_replace 
---------------
   {1,2,3,4}
(1 row)      
```

array_to_string(anyarray, text [, text])
Concatenates array elements using specified delimiter and optional null string.

SELECT array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*');

Result:

 array_to_string 
-----------------
    1,2,3,*,5
(1 row)      

array_upper(anyarray, int)
Returns upper bound of the requested array dimension.

SELECT array_upper(ARRAY[1,8,3,7], 1);

Result:

 array_upper 
-------------
           4
(1 row)      

cardinality(anyarray)
Returns the total number of elements in the array, or 0 if the array is empty.

SELECT cardinality(ARRAY[[1,2],[3,4]]);

Result:

 cardinality 
-------------
           4
(1 row)      

string_to_array(text, text [, text])
Splits string into array elements using supplied delimiter and optional null string.

SELECT string_to_array('xx~^~yy~^~zz', '~^~', 'yy');

Result:

 string_to_array 
-----------------
   {xx,NULL,zz}
(1 row)      

unnest(anyarray)
Expands an array to a set of rows.

SELECT unnest(ARRAY[1,2]);

Result:

 unnest 
--------
    1
    2
(2 row)      

unnest(anyarray, anyarray [, …])
Expands multiple arrays (possibly of different types) to a set of rows. This is only allowed in the FROM clause.

SELECT * FROM unnest(ARRAY[1,2],ARRAY['foo','bar','baz']);

Result:

 unnest | unnest 
--------+--------
      1 | foo
      2 | bar
        | baz    

(1 row)      

Range Functions and Operators

lower(anyrange)
Returns the lower bound of the input numeric range.

SELECT * FROM lower(numrange(1.1,2.2));

Result:

 lower 
-------
   1.1
(1 row)

upper(anyrange)
Returns upper of the input numeric range.

SELECT * FROM upper(numrange(1.1,2.2));

Result:

 upper 
-------
   2.2
(1 row)

isempty(anyrange)
Returns a boolean value indicating whether the entered number range is empty.
```
SELECT * FROM isempty(numrange(1.1,2.2));
```
Result:
```
 isempty 
---------
 f
(1 row)
```

lower_inc(anyrange)
Returns whether the lower bound of the entered number range exists.

SELECT * FROM lower_inc(numrange(1.1,2.2));

Result:

 lower_inc 
-----------
 t
(1 row)

upper_inc(anyrange)
Returns a logical value indicating whether the upper bound of the input numeral range exists.
```
SELECT * FROM upper_inc(numrange(1.1,2.2));
```
Result:
```
 lower_inc 
-----------
 f
(1 row)
```
lower_inf(anyrange)
Returns a logical value indicating whether the lower bound of the entered number range is infinite.
```
SELECT * FROM lower_inf('(,)'::daterange);
```
Result:
```
 lower_inf 
-----------
 t
(1 row)
```
upper_inf(anyrange)
Returns a logical value indicating whether the upper bound of the entered number range is infinite.
```
SELECT * FROM upper_inf('(,)'::daterange);
```
Result:
```
 upper_inf 
-----------
 t
(1 row)
```

range_merge(anyrange, anyrange)
Returns the smallest range which includes both of the given ranges.

SELECT * FROM range_merge('[1,2)'::int4range, '[3,4)'::int4range);

Result:

 range_merge 
-------------
 [1,4)
(1 row)

Aggregate Functions

array_agg(expression)
Returns input values, including nulls, concatenated into an array.

Argument Type(s): any non-array type
Return Type: array of the argument type
array_agg(expression)
Returns input arrays concatenated into array of one higher dimension (note: inputs must all have same dimensionality, and cannot be empty or NULL).

Argument Type(s): any array type
Return Type: same as argument data type
avg(expression)
Returns the average (arithmetic mean) of all input values.

Argument Type(s): smallint, int, bigint, real, double precision, numeric, or interval
Return Type: numeric for any integer-type argument, double precision for a floating-point argument, otherwise the same as the argument data type
bit_and(expression)
Returns the bitwise AND of all non-null input values, or null if none.

Argument Type(s): smallint, int, bigint, or bit
Return Type: same as argument data type
bit_or(expression)
Returns the bitwise OR of all non-null input values, or null if none.

Argument Type(s): smallint, int, bigint, or bit
Return Type: same as argument data type
bool_and(expression)
Returns true if all input values are true, otherwise false.

Argument Type(s): bool
Return Type: bool
bool_or(expression)
Returns true if at least one input value is true, otherwise false.

Argument Type(s): bool
Return Type: bool
count(anything)
Returns the number of input rows.

Argument Type(s): any
Return Type: bigint
count(expression)
Returns the number of input rows for which the value of expression is not null.

Argument Type(s): any
Return Type: bigint
every(expression)
Equivalent to bool_and.

Argument Type(s): bool
Return Type: bool
json_agg(expression)
Aggregates values as a JSON array.

Argument Type(s): any
Return Type: json
jsonb_agg(expression)
Aggregates values as a JSON array.

Argument Type(s): any
Return Type: jsonb
json_object_agg(name, value)
Aggregates name/value pairs as a JSON object.

Argument Type(s): (any, any)
Return Type: json
jsonb_object_agg(name, value)
Aggregates name/value pairs as a JSON object.

Argument Type(s): (any, any)
Return Type: jsonb
max(expression)
Returns the maximum value of expression across all input values.

Argument Type(s): any numeric, string, date/time, network, or enum type, or arrays of these types
Return Type: same as argument type
min(expression)
Returns the minimum value of expression across all input values.

Argument Type(s): any numeric, string, date/time, network, or enum type, or arrays of these types
Return Type: same as argument type
string_agg(expression, delimiter)
Returns the input values concatenated into a string, separated by delimiter.

Argument Type(s): (text, text) or (bytea, bytea)
Return Type: same as argument types
sum(expression)
Returns the sum of expression across all input values.

Argument Type(s): smallint, int, bigint, real, double precision, numeric, interval, or money
Return Type: bigint for smallint or int arguments, numeric for bigint arguments, otherwise the same as the argument data type
xmlagg(expression)
Returns the concatenation of XML values.

Argument Type(s): xml
Return Type: xml
corr(Y, X)
Returns correlation coefficient of the two entered numbers.

Argument Type(s): double precision
Return Type: double precision
covar_pop(Y, X)
Returns population covariance of the two entered numbers.

Argument Type(s): double precision
Return Type: double precision
covar_samp(Y, X)
Returns sample covariance of the two entered numbers.

Argument Type(s): double precision
Return Type: double precision
regr_avgx(Y, X)
Returns average of the independent variable X (sum(X)/N).

Argument Type(s): double precision
Return Type: double precision
regr_avgy(Y, X)
Returns average of the dependent variable Y (sum(Y)/N).

Argument Type(s): double precision
Return Type: double precision
regr_count(Y, X)
Returns the number of input rows in which both expressions are nonnull.

Argument Type(s): double precision
Return Type: bigint
regr_intercept(Y, X)
Returns y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs.

Argument Type(s): double precision
Return Type: double precision
regr_r2(Y, X)
Returns square of the correlation coefficient of the two entered numbers.

Argument Type(s): double precision
Return Type: double precision
regr_slope(Y, X)
Returns slope of the least-squares-fit linear equation determined by the (X, Y) pairs.

Argument Type(s): double precision
Return Type: double precision
regr_sxx(Y, X)
Returns sum(X^2) - sum(X)^2/N (“sum of squares” of the independent variable).

Argument Type(s): double precision
Return Type: double precision
regr_sxy(Y, X)
Returns sum(X*Y) - sum(X) * sum(Y)/N (“sum of products” of independent times dependent variable).

Argument Type(s): double precision
Return Type: double precision
regr_syy(Y, X)
Returns sum(Y^2) - sum(Y)^2/N (“sum of squares” of the dependent variable).

Argument Type(s): double precision
Return Type: double precision
stddev(expression)
Returns historical alias for stddev_samp.

Argument Type(s): smallint, int, bigint, real, double precision, or numeric
Return Type: double precision for floating-point arguments, otherwise numeric
stddev_pop(expression)
Returns population standard deviation of the input values.

Argument Type(s): smallint, int, bigint, real, double precision, or numeric
Return Type: double precision for floating-point arguments, otherwise numeric
stddev_samp(expression)
Returns sample standard deviation of the input values.

Argument Type(s): smallint, int, bigint, real, double precision, or numeric
Return Type: double precision for floating-point arguments, otherwise numeric
variance(expression)
Returns historical alias for var_samp.

Argument Type(s): smallint, int, bigint, real, double precision, or numeric
Return Type: double precision for floating-point arguments, otherwise numeric
var_pop(expression)
Returns population variance of the input values (square of the population standard deviation).

Argument Type(s): smallint, int, bigint, real, double precision, or numeric
Return Type: double precision for floating-point arguments, otherwise numeric
var_samp(expression)
Returns sample variance of the input values (square of the sample standard deviation).

Argument Type(s): smallint, int, bigint, real, double precision, or numeric
Return Type: double precision for floating-point arguments, otherwise numeric
mode() WITHIN GROUP (ORDER BY sort_expression)
Returns the most frequent input value (arbitrarily choosing one if there are multiple equally-frequent results).

Argument Type(s): any sortable type
Return Type: same as sort expression
percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression)
Returns a value corresponding to the specified fraction in the ordering, interpolating between adjacent input items if needed.

Argument Type(s): double precision or interval
Return Type: same as sort expression
percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression)
Returns an array of results matching the shape of the fractions parameter, with each non-null element replaced by the value corresponding to that percentile

Argument Type(s): double precision or interval
Return Type: array of sort expression's type
percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression)
Returns the first input value whose position in the ordering equals or exceeds the specified fraction.

Argument Type(s): any sortable type
Return Type: same as sort expression
percentile_disc(fractions) WITHIN GROUP (ORDER BY sort_expression)
Returns an array of results matching the shape of the fractions parameter, with each non-null element replaced by the input value corresponding to that percentile.

Argument Type(s): any sortable type
Return Type: array of sort expression's type
rank(args) WITHIN GROUP (ORDER BY sorted_args)
Returns rank of the argument, with gaps for duplicate rows.

Argument Type(s): VARIADIC "any"
Return Type: bigint
dense_rank(args) WITHIN GROUP (ORDER BY sorted_args)
Returns rank of the argument, without gaps for duplicate rows.

Argument Type(s): VARIADIC "any"
Return Type: bigint
percent_rank(args) WITHIN GROUP (ORDER BY sorted_args)
Returns relative rank of the argument, ranging from 0 to 1.

Argument Type(s): VARIADIC "any"
Return Type: double precision
cume_dist(args) WITHIN GROUP (ORDER BY sorted_args)
Returns relative rank of the argument, ranging from 1/N to 1.

Argument Type(s): VARIADIC "any"
Return Type: double precision
GROUPING(args…)
Returns integer bit mask indicating which arguments are not being included in the current grouping set.

Return Type: integer

Window Functions

row_number()
Returns the number of the current row within its partition, counting from 1.

Return Type: bigint
rank()
Returns rank of the current row with gaps; same as row_number of its first peer.

Return Type: bigint
dense_rank()
Returns rank of the current row without gaps; this function counts peer groups.

Return Type: bigint
percent_rank()
Returns relative rank of the current row: (rank - 1)/(total partition rows - 1).

Return Type: double precision
cume_dist()
Returns cumulative distribution: (number of partition rows preceding or peer with current row)/total partition rows.

Return Type: double precision
ntile(num_buckets integer)
Returns integer ranging from 1 to the argument value, dividing the partition as equally as possible.

Return Type: integer
lag(value anyelement [, offset integer [, default anyelement ]])
Returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null.

Return Type: same type as value
lead(value anyelement [, offset integer [, default anyelement ]])
Returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null.

Return Type: same type as value
first_value(value any)
Returns the value evaluated at the row that is the first row of the window frame.

Return Type: same type as value
last_value(value any)
Returns the value evaluated at the row that is the last row of the window frame.

Return Type: same type as value
nth_value(value any, nth integer)
Returns the value evaluated at the row that is the nth row of the window frame (counting from 1); null if there is no such row.

Return Type: same type as value

System Information Functions

current_catalog
Name of current database (called “catalog” in the SQL standard).

SELECT current_catalog;

Result:

 current_database 
------------------
       test
(1 row)

current_database()
Returns name of current database.

SELECT current_database();

Result:

 current_database 
------------------
       test
(1 row)

current_query()
Returns text of the currently executing query, as submitted by the client (might contain more than one statement).
```
SELECT current_query();
```
Result:
```
      current_query      
-------------------------
 SELECT current_query();
(1 row)
```

current_role
Returns equivalent to current_user.

SELECT current_role;

Result:

 current_user 
--------------
    agens
(1 row)    

current_schema[()]
Returns name of current schema.

SELECT current_schema();

Result:

 current_schema 
----------------
     public
(1 row)

current_schemas(boolean)
Returns names of schemas in search path, optionally including implicit schemas.

SELECT current_schemas(true);

Result:

   current_schemas   
---------------------
 {pg_catalog,public}
(1 row)

current_user
Returns user name of current execution context.

SELECT current_user;

Result:

 current_user 
--------------
    agens
(1 row)

inet_client_addr()
Returns address of the remote connection.

SELECT inet_client_addr();

Result:

 inet_client_addr
------------------
       ::1
(1 row)

inet_client_port()
Returns port of the remote connection.

SELECT inet_client_port();

Result:

 inet_client_port 
------------------
      64427        
(1 row)

inet_server_addr()
Returns address of the local connection.

SELECT inet_server_addr();

Result:

 inet_server_addr 
------------------
       ::1
(1 row)

inet_server_port()
Returns port of the local connection.

SELECT inet_server_port();

Result:

 inet_server_port 
------------------
       5432
(1 row)

pg_backend_pid()
Returns the process ID of the server process attached to the current session.

SELECT pg_backend_pid();

Result:

 pg_backend_pid 
----------------
          61675
(1 row)

pg_blocking_pids(int)
Returns the process ID(s) that are blocking specified server process ID.

SELECT pg_blocking_pids(61675);

Result:

 pg_blocking_pids 
------------------
        {}
(1 row)

pg_conf_load_time()
Returns configuration load time.

SELECT pg_conf_load_time();

Result:

      pg_conf_load_time       
------------------------------
 2017-10-18 13:36:51.99984+09
(1 row)

pg_my_temp_schema()
Returns OID of session’s temporary schema, or 0 if none.

SELECT pg_my_temp_schema();

Result:

 pg_my_temp_schema 
-------------------
                 0
(1 row)

pg_is_other_temp_schema(oid)
Returns whether schema is another session’s temporary schema.

SELECT pg_is_other_temp_schema(61675);

Result:

 pg_is_other_temp_schema 
-------------------------
           f
(1 row)

pg_listening_channels()
Returns channel names that the session is currently listening on.

SELECT pg_listening_channels();

Result:

 pg_listening_channels 
----------------------- 
(0 row) 

pg_notification_queue_usage()
Returns fraction of the asynchronous notification queue currently occupied (0-1).

SELECT pg_notification_queue_usage();

Result:

 pg_notification_queue_usage 
-----------------------------
                           0
(1 row)

pg_postmaster_start_time()
Returns server start time.

SELECT pg_postmaster_start_time();

Result:

   pg_postmaster_start_time    
-------------------------------
 2017-10-18 13:36:52.019037+09
(1 row)

pg_trigger_depth()
Returns current nesting level of PostgreSQL triggers (0 if not called, directly or indirectly, from inside a trigger).
```
SELECT pg_trigger_depth();
```
Result:
```
 pg_trigger_depth 
------------------
                0
(1 row)
```

session_user
Returns session user name.

SELECT session_user;

Result:

 session_user 
--------------
     agens
(1 row)

user
Returns the equivalent to current_user.

SELECT user;

Result:

 current_user 
--------------
     agens
(1 row)

version()
Returns AgensGraph’s version info.

SELECT version();

Result:

                                version    
-------------------------------------------------------------------------------
 PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 
 (Red Hat 4.4.7-17), 64-bit
(1 row)

has_any_column_privilege(user, table, privilege)
Returns a boolean value indicating whether user has the same privileges on all columns of table as others.

SELECT has_any_column_privilege('agens', 'myschema.mytable', 'SELECT');     

Result:
 has_any_column_privilege 
--------------------------
            t
(1 row)

has_any_column_privilege(table, privilege)
Returns whether current user has privilege for all columns of table.

SELECT has_any_column_privilege('myschema.mytable', 'SELECT');

Result:

 has_any_column_privilege 
--------------------------
            t       
(1 row)

has_column_privilege(user, table, column, privilege)
Returns whether user has privilege for table’s column.

SELECT has_column_privilege('agens', 'myschema.mytable', 'col1', 'SELECT');

Result:

 has_column_privilege 
----------------------
          t
(1 row)

has_column_privilege(table, column, privilege)
Returns whether current user has privilege for table’s column.

SELECT has_column_privilege('myschema.mytable', 'col1', 'SELECT');

Result:

 has_column_privilege 
----------------------
          t
(1 row)

has_database_privilege(user, database, privilege)
Returns whether user has privilege for database.

SELECT has_database_privilege('agens', 'test', 'connect');

Result:

 has_database_privilege 
------------------------
           t
(1 row)

has_database_privilege(database, privilege)
Returns whether current user has privilege for database.

SELECT has_database_privilege('test', 'connect');

Result:

 has_database_privilege 
------------------------
           t
(1 row)

has_foreign_data_wrapper_privilege(user, fdw, privilege)
Returns whether user has privilege for foreign-data wrapper.

CREATE EXTENSION postgres_fdw;

SELECT has_foreign_data_wrapper_privilege('agens', 'postgres_fdw', 'usage');

Result:

 has_foreign_data_wrapper_privilege 
------------------------------------
                 t
(1 row)

has_foreign_data_wrapper_privilege(fdw, privilege)
Returns whether current user has privilege for foreign-data wrapper.

SELECT has_foreign_data_wrapper_privilege('postgres_fdw', 'usage');

Result:

 has_foreign_data_wrapper_privilege 
------------------------------------
                 t
(1 row)

has_function_privilege(user, function, privilege))
Returns whether user has privilege for function.

SELECT has_function_privilege('agens', 'getfoo()', 'execute');

Result:

 has_function_privilege 
------------------------
           t
(1 row)

has_function_privilege(function, privilege)
Returns whether current user has privilege for function.

SELECT has_function_privilege('getfoo()', 'execute');

Result:

 has_function_privilege 
------------------------
           t
(1 row)

has_language_privilege(user, language, privilege)
Returns whether the user has privilege for language.

SELECT has_language_privilege('agens', 'c', 'usage');

Result:

 has_language_privilege 
------------------------
           t
(1 row)

has_language_privilege(language, privilege)
Returns whether current user has privilege for language.

SELECT has_language_privilege('c', 'usage');

Result:

 has_language_privilege 
------------------------
           t
(1 row)     

has_schema_privilege(user, schema, privilege)
Returns whether user has privilege for schema.

SELECT has_schema_privilege('agens', 'myschema', 'usage');

Result:

 has_schema_privilege 
----------------------
          t
(1 row)

has_schema_privilege(schema, privilege)
Returns whether current user has privilege for schema.

SELECT has_schema_privilege('myschema', 'usage');

Result:

 has_schema_privilege 
----------------------
          t
(1 row)

has_sequence_privilege(user, sequence, privilege)
Returns whether the user has privilege for sequence.

 SELECT has_sequence_privilege('agens', 'serial', 'usage');

Result:

  has_sequence_privilege 
 ------------------------
            t
(1 row)

has_sequence_privilege(sequence, privilege)
Returns whether user has privilege for sequence.

SELECT has_sequence_privilege('serial', 'usage');

Result:

  has_sequence_privilege 
 ------------------------
            t
(1 row)

has_server_privilege(user, server, privilege)
Returns whether user has privilege for server.

CREATE SERVER app_database_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '127.0.0.1', dbname 'agens');

SELECT has_server_privilege('agens', 'app_database_server', 'usage');

Result:

 has_server_privilege 
----------------------
          t
(1 row)

has_server_privilege(server, privilege)
Returns whether current user has privilege for server.

SELECT has_server_privilege('app_database_server', 'usage');

Result:

 has_server_privilege 
----------------------
          t
(1 row)

has_table_privilege(user, table, privilege)
Returns whether user has privilege for table.

SELECT has_table_privilege('agens', 'myschema.mytable', 'SELECT');

Result:

 has_table_privilege 
---------------------
          t
(1 row)

has_table_privilege(table, privilege)
Returns whether current user has privilege for table.

SELECT has_table_privilege('myschema.mytable', 'SELECT');

Result:

 has_table_privilege 
---------------------
          t
(1 row)

has_tablespace_privilege(user, tablespace, privilege)
Returns whether user has privilege for tablespace.

SELECT has_tablespace_privilege('agens', 'pg_default', 'create');

Result:

 has_tablespace_privilege 
--------------------------
             t
(1 row)

has_tablespace_privilege(tablespace, privilege)
Returns whether current user has privilege for tablespace.

SELECT has_tablespace_privilege('pg_default', 'create');

Result:

 has_tablespace_privilege 
--------------------------
             t
(1 row)

has_type_privilege(user, type, privilege)
Returns whether user has privilege for type.

SELECT has_type_privilege('agens', 'rainbow', 'usage');

Result:

 has_type_privilege 
--------------------
         t
(1 row)

has_type_privilege(type, privilege)
Returns whether current user has privilege for type.

SELECT has_type_privilege('rainbow', 'usage');

Result:

 has_type_privilege 
--------------------
         t
(1 row)

pg_has_role(user, role, privilege)
Returns whether user has privilege for role.

SELECT pg_has_role('agens', 'agens', 'usage');

Result:

 pg_has_role 
-------------
      t
(1 row)

pg_has_role(role, privilege)
Returns whether current user has privilege for role.

SELECT pg_has_role('agens', 'usage');

Result:

 pg_has_role 
-------------
      t
(1 row)

row_security_active(table)
Returns whether current user has row level security active for table.

SELECT row_security_active('myschema.mytable');

Result:

 row_security_active 
---------------------
         f
(1 row)

pg_collation_is_visible(collation_oid)
Returns whether collation is visible in search path.

SELECT pg_collation_is_visible(100);

Result:

 pg_collation_is_visible 
-------------------------
            t
(1 row) 

pg_conversion_is_visible(conversion_oid)
Returns whether conversion is visible in search path.

SELECT pg_conversion_is_visible(12830);

Result:

 pg_conversion_is_visible  
--------------------------
            t
(1 row) 

pg_function_is_visible(function_oid)
Returns whether function is visible in search path.

SELECT pg_function_is_visible(16716);

Result:

 pg_function_is_visible 
------------------------
            t 
(1 row) 

pg_opclass_is_visible(opclass_oid)
Returns whether opclass is visible in search path.

SELECT pg_opclass_is_visible(10007);

Result:

 pg_opclass_is_visible 
-----------------------
            t
(1 row) 

pg_operator_is_visible(operator_oid)
Returns whether operator is visible in search path.

SELECT pg_operator_is_visible(15);

Result:

 pg_operator_is_visible 
------------------------
            t
(1 row) 

pg_opfamily_is_visible(opclass_oid)
Returns whether opfamily is visible in search path.

SELECT pg_opfamily_is_visible(421);

Result:

 pg_opfamily_is_visible 
------------------------
            t
(1 row) 

pg_table_is_visible(table_oid)
Returns whether table is visible in search path.

SELECT pg_table_is_visible(16553);

Result:

 pg_table_is_visible 
---------------------
          t
(1 row) 

pg_ts_config_is_visible(config_oid)
Returns whether text search configuration is visible in search path.

SELECT pg_ts_config_is_visible(3748);

Result:

 pg_ts_config_is_visible 
-------------------------
            t
(1 row) 

pg_ts_dict_is_visible(dict_oid)
Returns whether text search dictionary is visible in search path.

SELECT pg_ts_dict_is_visible(3765);

Result:

 pg_ts_dict_is_visible 
-----------------------
           t
(1 row) 

pg_ts_parser_is_visible(parser_oid)
Returns whether text search parser is visible in search path.

SELECT pg_ts_parser_is_visible(3722);

Result:

 pg_ts_parser_is_visible 
-------------------------
             t
(1 row) 

pg_ts_template_is_visible(template_oid)
Returns whether text search template is visible in search path.

SELECT pg_ts_template_is_visible(3727);

Result:

 pg_ts_template_is_visible 
---------------------------
             t
(1 row) 

pg_type_is_visible(type_oid)
Returns whether type or domain is visible in search path.

SELECT pg_type_is_visible(16);

Result:

 pg_type_is_visible 
--------------------
          t
(1 row) 

format_type(type_oid, typemod) Gets the name of a data type.
```
SELECT format_type(16, 1);
```
Result:
```
 format_type 
-------------
   boolean
(1 row) 
```

pg_get_constraintdef(constraint_oid) Gets definition of a constraint.

SELECT pg_get_constraintdef(13096);

Result:

 pg_get_constraintdef 
----------------------
 CHECK ((VALUE >= 0))
(1 row) 

pg_get_constraintdef(constraint_oid, pretty_bool)
Gets definition of a constraint.

SELECT pg_get_constraintdef(13096, true);

Result:

 pg_get_constraintdef 
----------------------
 CHECK ((VALUE >= 0))
(1 row) 

pg_get_functiondef(func_oid)
Gets definition of a function.

SELECT pg_get_functiondef(16716);

Result:

             pg_get_functiondef             
--------------------------------------------
 CREATE OR REPLACE FUNCTION public.getfoo()+
 ...
(1 row) 

pg_get_function_arguments(func_oid)
Gets argument list of function’s definition (with default values).

SELECT pg_get_function_arguments(16739);

Result:

     pg_get_function_arguments      
------------------------------------
 double precision, double precision
(1 row) 

pg_get_function_identity_arguments(func_oid)
Gets argument list to identify a function (without default values).

SELECT pg_get_function_identity_arguments(16739);

Result:

 pg_get_function_identity_arguments 
------------------------------------
 double precision, double precision
(1 row) 

pg_get_function_result(func_oid)
Gets RETURNS clause for function.

SELECT pg_get_function_result(16739);

Result:

 pg_get_function_result 
------------------------
       float8_range
(1 row) 

pg_get_indexdef(index_oid) Gets CREATE INDEX command for index.

SELECT pg_get_indexdef(828);

Result:

                                 pg_get_indexdef                                  
----------------------------------------------------------------------------------
 CREATE UNIQUE INDEX pg_default_acl_oid_index ON pg_default_acl USING btree (oid)
(1 row) 

pg_get_indexdef(index_oid, column_no, pretty_bool)
Gets CREATE INDEX command for index, or definition of just one index column when column_no is not zero.
```
SELECT pg_get_indexdef(828, 1, true);
```
Result:
```
 pg_get_indexdef 
-----------------
       oid
(1 row) 
```

pg_get_keywords()
Gets list of SQL keywords and their categories.

SELECT pg_get_keywords();

Result:

      pg_get_keywords                         
---------------------------
   (abort,U,unreserved)
  (absolute,U,unreserved)
 ...
(434 row) 

pg_get_ruledef(rule_oid)
Gets CREATE RULE command for rule.

SELECT pg_get_ruledef(11732);

Result:

            pg_get_ruledef                                        
---------------------------------------
 CREATE RULE "_RETURN" AS  
 ...
(1 row) 

pg_get_ruledef(rule_oid, pretty_bool)
Gets CREATE RULE command for rule.

SELECT pg_get_ruledef(11732, true);

Result:

            pg_get_ruledef                                        
---------------------------------------
 CREATE RULE "_RETURN" AS  
 ...
(1 row) 

pg_get_serial_sequence(table_name, column_name)
Returns the name of the sequence using serial, smallserial, and bigserial columns.

SELECT pg_get_serial_sequence('serial_t', 'col1');

Result:

  pg_get_serial_sequence  
--------------------------
 public.serial_t_col1_seq
(1 row) 

pg_get_triggerdef(trigger_oid)
Gets CREATE [ CONSTRAINT ] TRIGGER command for trigger.

SELECT pg_get_triggerdef(16887);

Result:

                          pg_get_triggerdef   
-------------------------------------------------------------------------
 CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON messages 
 FOR EACH ROW EXECUTE PROCEDURE   
 tsvector_update_trigger_column('tsv', 'configcol', 'title', 'body')
(1 row) 

pg_get_triggerdef(trigger_oid, pretty_bool)
Gets CREATE [ CONSTRAINT ] TRIGGER command for trigger.

SELECT pg_get_triggerdef(16887, true);

Result:

                          pg_get_triggerdef   
-------------------------------------------------------------------------
 CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON messages 
 FOR EACH ROW EXECUTE PROCEDURE   
 tsvector_update_trigger_column('tsv', 'configcol', 'title', 'body')
(1 row) 

pg_get_userbyid(role_oid)
Gets role name with given OID.

SELECT pg_get_userbyid(13096);

Result:

 pg_get_userbyid 
-----------------
      agens
(1 row) 

pg_get_viewdef(view_oid)
Gets underlying SELECT command for view or mview.

SELECT pg_get_viewdef(17046);

Result:

          pg_get_viewdef           
-----------------------------------
  SELECT pg_class.relname,        +
     pg_class.relnamespace,       +
     ...
    FROM pg_class;
(1 row) 

pg_get_viewdef(view_oid, pretty_bool)
Gets underlying SELECT command for view or mview.

SELECT pg_get_viewdef(17046, true);

Result:

          pg_get_viewdef           
-----------------------------------
  SELECT pg_class.relname,        +
     pg_class.relnamespace,       +
     ...
    FROM pg_class;
(1 row) 

pg_get_viewdef(view_oid, wrap_column_int)
Gets underlying SELECT command for view or mview; lines with fields are wrapped to specified number of columns.

SELECT pg_get_viewdef(17046,50);

Result:

                   pg_get_viewdef                    
-----------------------------------------------------
  SELECT pg_class.relname, pg_class.relnamespace,   +
     pg_class.reltype, pg_class.reloftype,          +
     pg_class.relowner, pg_class.relam,             +
     pg_class.relfilenode, pg_class.reltablespace,  +
     pg_class.relpages, pg_class.reltuples,         +
     pg_class.relallvisible, pg_class.reltoastrelid,+
     pg_class.relhasindex, pg_class.relisshared,    +
     pg_class.relpersistence, pg_class.relkind,     +
     pg_class.relnatts, pg_class.relchecks,         +
     pg_class.relhasoids, pg_class.relhaspkey,      +
     pg_class.relhasrules, pg_class.relhastriggers, +
     pg_class.relhassubclass,                       +
     pg_class.relrowsecurity,                       +
     pg_class.relforcerowsecurity,                  +
     pg_class.relispopulated, pg_class.relreplident,+
     pg_class.relfrozenxid, pg_class.relminmxid,    +
     pg_class.relacl, pg_class.reloptions           +
    FROM pg_class;
(1 row) 

pg_index_column_has_property(index_oid, column_no, prop_name)
Tests whether an index column has a specified property.

SELECT pg_index_column_has_property(17134, 1, 'orderable');

Result:

 pg_index_column_has_property 
------------------------------
             t
(1 row)

pg_index_has_property(index_oid, prop_name)
Tests whether an index has a specified property.

SELECT pg_index_has_property(17134, 'clusterable');

Result:

 pg_index_has_property 
-----------------------
          t
(1 row)

pg_indexam_has_property(am_oid, prop_name)
Tests whether an index access method has a specified property.

SELECT pg_indexam_has_property(403, 'can_order');

Result:

 pg_indexam_has_property 
-------------------------
          t
(1 row)

pg_options_to_table(reloptions)
Gets name/value pairs of the set of storage option.

SELECT pg_options_to_table(reloptions) FROM pg_class; 

Result:
   pg_options_to_table   
-------------------------
 (security_barrier,true)
(1 row) 

pg_tablespace_databases(tablespace_oid)
Gets the set of database OIDs that have objects in the tablespace.

SELECT pg_tablespace_databases(1663);

Result:

 pg_tablespace_databases 
-------------------------
                       1
                   13372
                   13373
                   16384
                   16482
(5 row) 

pg_tablespace_location(tablespace_oid)
Gets the path in the file system that this tablespace is located in.

SELECT pg_tablespace_location(1663);

Result:

      pg_tablespace_location         
----------------------------------
 /home/agens/AgensGraph/db_cluster
(1 row) 

pg_typeof(any)
Gets the data type of any value.

SELECT pg_typeof(1);

Result:

 pg_typeof 
-----------
  integer
(1 row) 

collation for (any)
Gets the collation of the argument.

SELECT collation for ('foo' COLLATE "de_DE");

Result:

 pg_collation_for 
------------------
     "de_DE"
(1 row) 

pg_describe_object(catalog_id, object_id, object_sub_id)
Gets description of a database object.

SELECT pg_describe_object(1255, 16716, 0);

Result:

 pg_describe_object 
--------------------
  function 16716
(1 row) 

pg_identify_object(catalog_id oid, object_id oid, object_sub_id integer)
Gets identity info of a database object.

SELECT pg_identify_object(1255, 16716, 0);

Result:

          pg_identify_object          
--------------------------------------
 (function,public,,"public.getfoo()")
(1 row) 

pg_identify_object_as_address(catalog_id oid, object_id oid, object_sub_id integer)
Gets external representation of a database object’s address.

SELECT pg_identify_object_as_address(1255, 16716, 0);

Result:

  pg_identify_object_as_address  
---------------------------------
 (function,"{public,getfoo}",{})
(1 row) 

pg_get_object_address(type text, name text[], args text[])
Gets address of a database object, from its external representation.

SELECT pg_get_object_address('type', '{public.comp}', '{}');

Result:

 pg_get_object_address 
-----------------------
     (1247,17063,0)
(1 row) 

col_description(table_oid, column_number)
Gets comment for a table column.

SELECT col_description(17064, 1);

Result:

 col_description 
-----------------
   code_number
(1 row) 

obj_description(object_oid, catalog_name)
Gets comment for a database object.

SELECT obj_description(16887, 'pg_trigger');

Result:

  obj_description   
--------------------
 comment on trigger
(1 row) 

obj_description(object_oid)
Gets comment for a database object (no longer used).

SELECT obj_description(16887);

Result:

  obj_description   
--------------------
 comment on trigger
(1 row) 

shobj_description(object_oid, catalog_name)
Gets comment for a shared database object.

SELECT shobj_description(1262,'pg_database');

Result:

 shobj_description 
-------------------
 
(1 row)

txid_current()
Gets current transaction ID, assigning a new one if the current transaction does not have one.
```
SELECT txid_current();
```
Result:
```
 txid_current 
--------------
         2061
(1 row) 
```

txid_current_snapshot()
Gets current snapshot.

SELECT txid_current_snapshot();

Result:

 txid_current_snapshot 
-----------------------
      2062:2062:
(1 row) 

txid_snapshot_xip(txid_snapshot)
Gets in-progress transaction IDs in snapshot.

SELECT txid_snapshot_xip('2095:2095:');

Result:

 txid_snapshot_xip 
-------------------

(1 row)

txid_snapshot_xmax(txid_snapshot)
Gets xmax of snapshot.

SELECT txid_snapshot_xmax('2094:2095:');

Result:

 txid_snapshot_xmax 
--------------------
               2095
(1 row)

txid_snapshot_xmin(txid_snapshot)
Gets xmin of snapshot.

SELECT txid_snapshot_xmin('2094:2095:');

Result:

 txid_snapshot_xmin 
--------------------
               2094
(1 row)

txid_visible_in_snapshot(bigint, txid_snapshot)
Returns whether transaction ID is visible in snapshot (do not use with subtransaction ids).

SELECT txid_visible_in_snapshot(2099, '2100:2100:');

Result:

 txid_visible_in_snapshot 
--------------------------
            t
(1 row)

pg_xact_commit_timestamp(xid)
Gets commit timestamp of a transaction (track_commit_timestamp parameter should be set to on).

SELECT pg_xact_commit_timestamp('2097'::xid);

Result:

   pg_xact_commit_timestamp    
-------------------------------
 2017-10-18 13:38:09.738211+09
(1 row)

pg_last_committed_xact()
Gets transaction ID and commit timestamp of latest committed transaction (track_commit_timestamp parameter should be set to on).

SELECT pg_last_committed_xact();

Result:

         pg_last_committed_xact         
----------------------------------------
 (2097,"2017-10-18 13:38:09.738211+09")
(1 row)

pg_control_checkpoint()
Returns information about current checkpoint state.

SELECT pg_control_checkpoint();

Result:

                     pg_control_checkpoint                                            
-------------------------------------------------------------------
 (0/1D4B0D0,0/1D4B038,0/1D4B0D0,000000010000000000000001,
 1,1,t,0:2063,24576,1,0,1751,1,0,1,1,0,0,"2017-10-16 16:26:21+09")
(1 row) 

pg_control_system()
Returns information about current control file state.

SELECT pg_control_system();

Result:

                      pg_control_system                       
--------------------------------------------------------------
 (960,201608131,6469891178207434037,"2017-10-16 16:26:21+09")
(1 row) 

pg_control_init()
Returns information about cluster initialization state.

SELECT pg_control_init();

Result:

                    pg_control_init                    
-------------------------------------------------------
 (8,8192,131072,8192,16777216,64,32,1996,2048,t,t,t,0)
(1 row) 

pg_control_recovery()
Returns information about recovery state.

SELECT pg_control_recovery();

Result:

 pg_control_recovery 
---------------------
  (0/0,0,0/0,0/0,f)
(1 row) 

System Administration Functions

current_setting(setting_name [, missing_ok ])
Gets current value of setting.

SELECT current_setting('datestyle');

Result:

 current_setting 
-----------------
 ISO, YMD
(1 row)      

set_config(setting_name, new_value, is_local)
Sets parameter and returns new value.

SELECT set_config('log_statement_stats', 'off', false);

Result:

 set_config 
------------
    off
(1 row)      

pg_cancel_backend(pid int)
Cancels a backend’s current query. This is also allowed if the calling role is a member of the role whose backend is being canceled or the calling role has been granted pg_signal_backend. However, only superusers can cancel superuser backends.
```
SELECT pg_cancel_backend(30819);
Error: Cancel operation by user request.        
```

pg_reload_conf()
Causes server processes to reload their configuration files.

SELECT pg_reload_conf();

Result:

 pg_reload_conf 
----------------
       t
(1 row) 

pg_rotate_logfile()
Signals the log-file manager to switch to a new log file immediately (This works only when the built-in log collector is running).
```
SELECT pg_rotate_logfile();

 pg_rotate_logfile 
-------------------
         f
(1 row) 
```

pg_terminate_backend(pid int)
Terminates a backend. This is also allowed if the calling role is a member of the role whose backend is being terminated or the calling role has been granted pg_signal_backend. However, only superusers can terminate superuser backends.

SELECT pg_terminate_backend(30819);

Result:

Fatal error: Connection is terminated by an administrator request.
             The server suddenly closed the connection.
             This type of processing means the server was abruptly terminated
             while or before processing the client's request.
             The server connection has been lost. Attempt to reconnect: Success.

pg_create_restore_point(name text)
Creates a named point for performing restore (restricted to superusers by default, but other users can be granted EXECUTE to run the function).
```
SELECT pg_create_restore_point( 'important_moment' );
```
Result:
```
 pg_create_restore_point 
-------------------------
       0/1D72DC0
(1 row)
```

pg_current_xlog_flush_location()
Returns the transaction log flush location.

SELECT pg_current_xlog_flush_location();

Result:

 pg_current_xlog_flush_location 
---------------------------------
            0/1D72ED8
(1 row)

pg_current_xlog_insert_location()
Returns the location of the current transaction log insert.

SELECT pg_current_xlog_insert_location();

Result:

 pg_current_xlog_insert_location 
---------------------------------
            0/1D72ED8
(1 row)

pg_current_xlog_location()
Returns the location of the current transaction log write.

SELECT pg_current_xlog_location();

Result:

 pg_current_xlog_location 
--------------------------
         0/1D72ED8
(1 row)

pg_start_backup(label text [, fast boolean [, exclusive boolean ]])
Prepares for performing on-line backup (restricted to superusers by default, but other users can be granted EXECUTE to run the function).
```
SELECT pg_start_backup('my_backup', true, false);
```
Result:
```
 pg_start_backup 
-----------------
     0/2000028
(1 row)
```

pg_stop_backup()
Finishes performing exclusive on-line backup (restricted to superusers by default, but other users can be granted EXECUTE to run the function).

SELECT pg_stop_backup();

Result:

NOTICE: The pg_stop_backup operation is finished. 
        All necessary WAL pieces have been archived.    
 pg_stop_backup    
----------------
 (0/50000F8,,)
(1 row) 

pg_stop_backup(exclusive boolean)
Finishes performing exclusive or non-exclusive on-line backup (restricted to superusers by default, but other users can be granted EXECUTE to run the function).

SELECT pg_stop_backup(false);

Result:

NOTICE:  WAL archiving is not enabled; you must ensure that all required WAL 
         segments are copied through other means to complete the backup

                              pg_stop_backup                               
---------------------------------------------------------------------------
 (0/3000088,"START WAL LOCATION: 0/2000028 (file 000000010000000000000002)+
 CHECKPOINT LOCATION: 0/2000060                                           +
 BACKUP METHOD: streamed                                                  +
 BACKUP FROM: master                                                      +
 START TIME: 2017-10-17 10:00:18 KST                                      +
 LABEL: my_backup                                                         +
 ","17060 /home/agens/AgensGraph/db_cluster/data                          +
 ")
(1 row) 

pg_is_in_backup()
Returns true if an on-line exclusive backup is still in progress.

SELECT pg_is_in_backup();

Result:

 pg_is_in_backup 
-----------------
        t
(1 row) 

pg_backup_start_time()
Gets start time of an on-line exclusive backup in progress.

SELECT pg_backup_start_time();

Result:

  pg_backup_start_time  
------------------------
 2017-10-17 10:29:26+09
(1 row) 

pg_switch_xlog()
Forces switch to a new transaction log file (restricted to superusers by default, but other users can be granted EXECUTE to run the function).
```
SELECT pg_switch_xlog();
```
Result:
```
 pg_switch_xlog 
----------------
    0/9000120
(1 row) 
```

pg_xlogfile_name(location pg_lsn)
Converts the transaction log location string to file name.

SELECT pg_xlogfile_name('0/9000028');

Result:

     pg_xlogfile_name     
--------------------------
 000000010000000000000009
(1 row) 

pg_xlogfile_name_offset(location pg_lsn)
Converts the transaction log location string to file name and decimal byte offset within file.

SELECT pg_xlogfile_name_offset('0/9000028');

Result:

    pg_xlogfile_name_offset    
-------------------------------
 (000000010000000000000009,40)
(1 row) 

pg_xlog_location_diff(location pg_lsn, location pg_lsn)
Calculates the difference between two transaction log locations.
```
SELECT pg_xlog_location_diff('0/9000120', '0/9000028');
```
Result:
```
 pg_xlog_location_diff 
-----------------------
(1 row) 
```
pg_is_in_recovery() Returns true if recovery is still in progress.
```
SELECT pg_is_in_recovery();
```
Result:
```
 pg_is_in_recovery 
-------------------
        t                 
(1 row) 
```
pg_last_xlog_receive_location()
Gets the last transaction log location received and synced to disk by streaming replication. While streaming replication is in progress this will increase monotonically. If recovery has completed this will remain static at the value of the last WAL record received and synced to disk during recovery. If streaming replication is disabled, or if it has not yet started, the function returns NULL.
```
SELECT pg_last_xlog_receive_location();
```
Result:
```
 pg_last_xlog_receive_location 
-------------------------------

(1 row) 
```
pg_last_xlog_replay_location()
Gets the last transaction log location replayed during recovery. If recovery is still in progress this will increase monotonically. If recovery has completed then this value will remain static at the value of the last WAL record applied during that recovery. When the server has been started normally without recovery the function returns NULL.
```
SELECT pg_last_xlog_replay_location();
```
Result:
```
 pg_last_xlog_replay_location 
------------------------------

(1 row) 
```
pg_last_xact_replay_timestamp()
Gets timestamp of last transaction replayed during recovery. This is the time at which the commit or abort WAL record for that transaction was generated on the primary. If no transactions have been replayed during recovery, this function returns NULL. Otherwise, if recovery is still in progress this will increase monotonically. If recovery has completed then this value will remain static at the value of the last transaction applied during that recovery. When the server has been started normally without recovery the function returns NULL.
```
SELECT pg_last_xact_replay_timestamp();
```
Result:
```
 pg_last_xact_replay_timestamp 
-------------------------------
 
(1 row) 
```

pg_export_snapshot()
Saves the current snapshot and returns its identifier.

SELECT pg_export_snapshot();

Result:

 pg_export_snapshot 
--------------------
     00000816-1
(1 row)

pg_create_physical_replication_slot(slot_name name [, immediately_reserve boolean ])
Creates a new physical replication slot named slot_name. The optional second parameter, when true, specifies that the LSN for this replication slot be reserved immediately; otherwise the LSN is reserved on first connection from a streaming replication client. Streaming changes from a physical slot is only possible with the streaming-replication protocol (see this link for more technical info). This function corresponds to the replication protocol command CREATE_REPLICATION_SLOT ... PHYSICAL.
```
SELECT pg_create_physical_replication_slot('test_slot', 'true');
```
Result:
```
 pg_create_physical_replication_slot 
-------------------------------------
        (test_slot,0/D000220)
(1 row)
```
pg_drop_replication_slot(slot_name name)
Drops the physical or logical replication slot named slot_name. Same as replication protocol command DROP_REPLICATION_SLOT.
```
SELECT pg_drop_replication_slot('test_slot');
```
Result:
```
 pg_drop_replication_slot 
--------------------------

(1 row)
```
pg_create_logical_replication_slot(slot_name name, plugin name)
Creates a new logical (decoding) replication slot named slot_name using the output plugin plugin. A call to this function has the same effect as the replication protocol command CREATE_REPLICATION_SLOT ... LOGICAL.
```
SELECT pg_create_logical_replication_slot('test_slot', 'test_decoding');
```
Result:
```
 pg_create_logical_replication_slot 
------------------------------------
       (test_slot,0/D000338)
(1 row)
```
pg_logical_slot_get_changes(slot_name name, upto_lsn pg_lsn, upto_nchanges int, VARIADIC options text[])
Returns changes in the slot slot_name, starting from the point at which since changes have been consumed last. If upto_lsn and upto_nchanges are NULL, logical decoding will continue until end of WAL. If upto_lsn is non-NULL, decoding will include only those transactions which commit prior to the specified LSN.
If upto_nchanges is non-NULL, decoding will stop when the number of rows produced by decoding exceeds the specified value. Note, however, that the actual number of rows returned may be larger, since this limit is only checked after adding the rows produced when decoding each new transaction commit.
```
SELECT pg_logical_slot_get_changes('regression_slot',null, null);
```
Result:
```
  pg_logical_slot_get_changes   
--------------------------------
 (0/F000190,2079,"BEGIN 2079")
 (0/F028360,2079,"COMMIT 2079")
(2 row) 
```

pg_logical_slot_peek_changes(slot_name name, upto_lsn pg_lsn, upto_nchanges int, VARIADIC options text[])
Behaves just like the pg_logical_slot_get_changes() function, except that changes are not consumed.

SELECT pg_logical_slot_peek_changes('regression_slot',null, null);

Result:

                        pg_logical_slot_peek_changes                        
----------------------------------------------------------------------------
 (0/F028398,2080,"BEGIN 2080")
 (0/F028468,2080,"table public.data: INSERT: id[integer]:1 data[text]:'3'")
 (0/F028568,2080,"COMMIT 2080")
(3 row) 

pg_logical_slot_get_binary_changes(slot_name name, upto_lsn pg_lsn, upto_nchanges int, VARIADIC options text[])
Behaves just like the pg_logical_slot_get_changes() function, except that changes are returned as bytea.

SELECT pg_logical_slot_get_binary_changes('regression_slot',null, null);

Result:

               pg_logical_slot_get_binary_changes
--------------------------------------------------------------------
 (0/F028398,2080,"\\x424547494e2032303830")
 (0/F028468,2080,"\\x7461626c65207075626c69632e646174613a20494e5345
 52543a2069645b696e74656765725d3a3120646174615b746578745d3a273327")
 (0/F028568,2080,"\\x434f4d4d49542032303830")
(3 row) 

pg_logical_slot_peek_binary_changes(slot_name name, upto_lsn pg_lsn, upto_nchanges int, VARIADIC options text[])
Behaves just like the pg_logical_slot_get_changes() function, except that changes are returned as bytea and that changes are not consumed.

SELECT pg_logical_slot_peek_binary_changes('regression_slot',null, null);

Result:

               pg_logical_slot_peek_binary_changes
--------------------------------------------------------------------
 (0/F028398,2080,"\\x424547494e2032303830")
 (0/F028468,2080,"\\x7461626c65207075626c69632e646174613a20494e5345
 52543a2069645b696e74656765725d3a3120646174615b746578745d3a273327")
 (0/F028568,2080,"\\x434f4d4d49542032303830")
(3 row) 

pg_replication_origin_create(node_name text)
Creates a replication origin with the given external name, and returns the internal id assigned to it.

SELECT pg_replication_origin_create('test_decoding: regression_slot');

Result:

 pg_replication_origin_create 
------------------------------
                            1
(1 row) 

pg_replication_origin_drop(node_name text)
Deletes a previously created replication origin, including any associated replay progress.
```
SELECT pg_replication_origin_drop('test_decoding: temp');
```
Result:
```
 pg_replication_origin_drop 
----------------------------
 
(1 row)
```

pg_replication_origin_oid(node_name text)
Look ups a replication origin by name and returns the internal id. If no corresponding replication origin is found an error is thrown.

SELECT pg_replication_origin_oid('test_decoding: temp');

Result:

 pg_replication_origin_oid 
---------------------------
                         2
(1 row)

pg_replication_origin_session_setup(node_name text)
Marks the current session as replaying from the given origin, allowing replay progress to be tracked.
Use pg_replication_origin_session_reset to revert. Can only be used if no previous origin is configured.
```
SELECT pg_replication_origin_session_setup('test_decoding: regression_slot');
```
Result:
```
 pg_replication_origin_session_setup 
-------------------------------------
 
(1 row)
```

pg_replication_origin_session_reset()
Cancels the configuration of pg_replication_origin_session_setup().

SELECT pg_replication_origin_session_reset();

Result:

 pg_replication_origin_session_reset 
-------------------------------------
 
(1 row)

pg_replication_origin_session_is_setup()
Returns whether a replication origin has been configured in the current session.

SELECT pg_replication_origin_session_is_setup();

Result:

 pg_replication_origin_session_is_setup 
----------------------------------------
                  t
(1 row)

pg_replication_origin_session_progress(flush bool)
Returns the replay location for the replication origin configured in the current session. The parameter flush determines whether the corresponding local transaction will be guaranteed to have been flushed to disk or not.
```
SELECT pg_replication_origin_session_progress(false);
```
Result:
```
 pg_replication_origin_session_progress 
----------------------------------------
              0/AABBCCDD
(1 row)
```
pg_replication_origin_xact_setup(origin_lsn pg_lsn, origin_timestamp timestamptz)
Current transaction as replaying a transaction that has committed at the given LSN and timestamp. Can only be called when a replication origin has previously been configured using pg_replication_origin_session_setup().
```
SELECT pg_replication_origin_xact_setup('0/AABBCCDD', '2017-01-01 00:00');
```
Result:
```
 pg_replication_origin_xact_setup 
----------------------------------

(1 row)
```

pg_replication_origin_xact_reset()
Cancels the configuration of pg_replication_origin_xact_setup().

SELECT pg_replication_origin_xact_reset();

Result:

 pg_replication_origin_xact_reset 
----------------------------------

(1 row)

pg_replication_origin_advance(node_name text, pos pg_lsn)
Sets replication progress for the given node to the given location. This primarily is useful for setting up the initial location or a new location after configuration changes and similar. Be aware that careless use of this function can lead to inconsistently replicated data.
```
SELECT pg_replication_origin_advance('test_decoding: regression_slot', '0/1');
```
Result:
```
 pg_replication_origin_advance 
-------------------------------
 
(1 row)
```
pg_replication_origin_progress(node_name text, flush bool)
Returns the replay location for the given replication origin. The parameter flush determines whether the corresponding local transaction will be guaranteed to have been flushed to disk or not.
```
SELECT pg_replication_origin_progress('test_decoding: temp', true);
```
Result:
```
 pg_replication_origin_progress 
--------------------------------
           0/AABBCCDD
(1 row)           
```
pg_logical_emit_message(transactional bool, prefix text, content text) Emits a text logical decoding message. This can be used to pass generic messages to logical decoding plugins through WAL. The parameter transactional specifies if the message should be part of current transaction or if it should be written immediately and decoded as soon as the logical decoding reads the record. The prefix is textual prefix used by the logical decoding plugins to easily recognize interesting messages for them. The content is the text of the message.
```
SELECT pg_logical_emit_message(false, 'test', 'this message will not be decoded');
```
Result:
```
 pg_logical_emit_message 
-------------------------
        0/F05E1D0
(1 row)           
```
pg_logical_emit_message(transactional bool, prefix text, content bytea)
Emits binary logical decoding message. This can be used to pass generic messages to logical decoding plugins through WAL. The parameter transactional specifies if the message should be part of current transaction or if it should be written immediately and decoded as soon as the logical decoding reads the record. The prefix is textual prefix used by the logical decoding plugins to easily recognize interesting messages for them. The content is the binary content of the message.
```
SELECT pg_logical_emit_message(false, 'test', '0/F05E1D0');
```
Result:
```
 pg_logical_emit_message 
-------------------------
        0/F05E2C8
(1 row)           
```

pg_column_size(any)
Returns the number of bytes used to store a particular value.

SELECT pg_column_size('SELECT fooid FROM foo');

Result:

 pg_column_size 
----------------
             22
(1 row)

pg_database_size(oid) Returns disk space used by the database with the specified OID.
```
SELECT pg_database_size(16482);
```
Result:
```
 pg_database_size 
------------------
          9721508
(1 row)
```

pg_database_size(name)
Returns disk space used by the database with the specified name.

SELECT pg_database_size('test');

Result:

 pg_database_size 
------------------
          9721508
(1 row)

pg_indexes_size(regclass) Returns total disk space used by indexes attached to the specified table.
```
SELECT pg_indexes_size(2830);
```
Result:
```
 pg_indexes_size 
-----------------
            8192
(1 row)
```
pg_relation_size(relation regclass, fork text)
Returns disk space used by the specified fork (‘main’, ‘fsm’, ‘vm’, or ‘init’) of the specified table or index.
```
SELECT  pg_relation_size(16881, 'main');
```
Result:
```
 pg_relation_size 
------------------
                0
(1 row)
```

pg_relation_size(relation regclass)
Shorthand for pg_relation_size(..., 'main').

SELECT  pg_relation_size(16881);

Result:

 pg_relation_size 
------------------
                0
(1 row)

pg_size_bytes(text)
Converts a size in human-readable format with size units into bytes.

SELECT pg_size_bytes('100');

Result:

 pg_size_bytes 
---------------
           100
(1 row)

pg_size_pretty(bigint)
Converts a size in bytes expressed as a 64-bit integer into a human-readable format with size units.
```
SELECT pg_size_pretty(10::bigint);
```
Result:
```
 pg_size_pretty 
----------------
       10 bytes
(1 row)
```
pg_size_pretty(numeric)
Converts a size in bytes expressed as a numeric value into a human-readable format with size units.
```
SELECT pg_size_pretty(10::numeric);
```
Result:
```
 pg_size_pretty 
----------------
       10 bytes
(1 row)
```
pg_table_size(regclass)
Returns disk space used by the specified table, excluding indexes (but including TOAST, free space map, and visibility map).
```
SELECT pg_table_size('myschema.mytable');
```
Result:
```
 pg_table_size 
---------------
          8192
(1 row)
```

pg_tablespace_size(oid)
Returns disk space used by the tablespace with the specified OID.

SELECT pg_tablespace_size(1663);

Result:

 pg_tablespace_size 
--------------------
           40859636
(1 row)

pg_tablespace_size(name)
Returns disk space used by the tablespace with the specified name.

SELECT pg_tablespace_size('pg_default');

Result:

 pg_tablespace_size 
--------------------
           40859636
(1 row)

pg_total_relation_size(regclass)
Returns total disk space used by the specified table, including all indexes and TOAST data.

SELECT  pg_total_relation_size(16881);

Result:

 pg_total_relation_size 
------------------------
                   8192
(1 row)

pg_relation_filenode(relation regclass)
Returns the filenode number of the specified relation.

SELECT pg_relation_filenode('pg_database');

Result:

 pg_relation_filenode 
----------------------
                 1262
(1 row)

pg_relation_filepath(relation regclass)
Returns file path name of the specified relation.

SELECT pg_relation_filepath('pg_database');

Result:

 pg_relation_filepath 
----------------------
          global/1262
(1 row)

pg_filenode_relation(tablespace oid, filenode oid)
Finds the relation associated with a given tablespace and filenode.

SELECT pg_filenode_relation(1663, 16485);

Result:

 pg_filenode_relation 
----------------------
  test.ag_label_seq
(1 row)

brin_summarize_new_values(index regclass)
Summarizes page ranges not already summarized.

SELECT brin_summarize_new_values('brinidx');

Result:

 brin_summarize_new_values 
---------------------------
                         0
(1 row)

gin_clean_pending_list(index regclass)
Moves GIN pending list entries into main index structure.

SELECT gin_clean_pending_list('gin_test_idx');

Result:

 gin_clean_pending_list 
------------------------
                      0
(1 row)

pg_ls_dir(dirname text [, missing_ok boolean, include_dot_dirs boolean])
Lists the content of a directory.

SELECT pg_ls_dir('.');

Result:

      pg_ls_dir       
----------------------
 pg_xlog
 global
 ...
(28 row)

pg_read_file(filename text [, offset bigint, length bigint [, missing_ok boolean] ])
Returns the content of a text file.
```
SELECT pg_read_file('test.sql');
```
Result:
```
 pg_read_file 
--------------
 test        +
(1 row)
```

pg_read_binary_file(filename text [, offset bigint, length bigint [, missing_ok boolean] ])
Returns the content of a file.

SELECT pg_read_binary_file('test');

Result:

 pg_read_binary_file 
---------------------
     x6161610a       
(1 row)

pg_stat_file(filename text[, missing_ok boolean])
Returns information about a file.

SELECT pg_stat_file('test');

Result:

                                   pg_stat_file                                    
-----------------------------------------------------------------------------------
 (4,"2017-10-18 11:05:09+09","2017-10-18 11:04:55+09","2017-10-18 11:04:55+09",,f)
(1 row)

pg_advisory_lock(key bigint)
Obtains exclusive session level advisory lock.

SELECT pg_advisory_lock(1);
SELECT locktype, classid, objid, mode FROM pg_locks where objid=1;

Result:

 locktype | classid | objid |     mode      
----------+---------+-------+---------------
 advisory |       0 |     1 | ExclusiveLock
(1 row)

pg_advisory_lock(key1 int, key2 int)
Obtains exclusive session level advisory lock.

SELECT pg_advisory_lock(1,2);
SELECT locktype, classid, objid, mode FROM pg_locks where objid=2;

Result:

 locktype | classid | objid |     mode      
----------+---------+-------+---------------
 advisory |       1 |     2 | ExclusiveLock
(1 row)

pg_advisory_lock_shared(key bigint)
Obtains shared session level advisory lock.

SELECT pg_advisory_lock_shared(10);
SELECT locktype, classid, objid, mode FROM pg_locks where objid=10;

Result:

 locktype | classid | objid |   mode    
----------+---------+-------+-----------
 advisory |       0 |    10 | ShareLock
(1 row)

pg_advisory_lock_shared(key1 int, key2 int)
Obtains shared session level advisory lock.

SELECT pg_advisory_lock_shared(10,20);
SELECT locktype, classid, objid, mode FROM pg_locks where objid=20;

Result:

 locktype | classid | objid |   mode    
----------+---------+-------+-----------
 advisory |      10 |    20 | ShareLock
(1 row)

pg_advisory_unlock(key bigint)
Releases an exclusive session level advisory lock.

SELECT pg_advisory_unlock(1);

Result:

 pg_advisory_unlock 
--------------------
         t
(1 row)

pg_advisory_unlock(key1 int, key2 int)
Releases an exclusive session level advisory lock.

SELECT pg_advisory_unlock(1,2);

Result:

 pg_advisory_unlock 
--------------------
         t
(1 row)

pg_advisory_unlock_all()
Releases all session level advisory locks held by the current session.

SELECT pg_advisory_unlock_all();

Result:

 pg_advisory_unlock_all 
------------------------
 
(1 row)

pg_advisory_unlock_shared(key bigint)
Releases a shared session level advisory lock.

SELECT pg_advisory_unlock_shared(10);

Result:

 pg_advisory_unlock_shared 
---------------------------
             t
(1 row)

pg_advisory_unlock_shared(key1 int, key2 int)
Releases a shared session level advisory lock.

SELECT pg_advisory_unlock_shared(10,20);

Result:

 pg_advisory_unlock_shared 
---------------------------
             t
(1 row)

pg_advisory_xact_lock(key bigint)
Obtains exclusive transaction level advisory lock.

SELECT pg_advisory_xact_lock(1);

Result:

 pg_advisory_xact_lock 
------------------------
           
(1 row)

pg_advisory_xact_lock(key1 int, key2 int)
Obtains exclusive transaction level advisory lock.

SELECT pg_advisory_xact_lock(1,2);

Result:

 pg_advisory_xact_lock 
------------------------
           
(1 row)

pg_advisory_xact_lock_shared(key bigint)
Obtains shared transaction level advisory lock.

SELECT pg_advisory_xact_lock_shared(10);

Result:

 pg_advisory_xact_lock_shared 
------------------------------
           
(1 row)

pg_advisory_xact_lock_shared(key1 int, key2 int)
Obtains shared transaction level advisory lock.

SELECT pg_advisory_xact_lock_shared(10,20);

Result:

 pg_advisory_xact_lock_shared 
------------------------------
           
(1 row)

pg_try_advisory_lock(key bigint)
Obtains exclusive session level advisory lock if available.

SELECT pg_try_advisory_lock(100);

Result:

 pg_try_advisory_lock 
----------------------
          t
(1 row)

pg_try_advisory_lock(key1 int, key2 int)
Obtains exclusive session level advisory lock if available.

SELECT pg_try_advisory_lock(100,200);

Result:

 pg_try_advisory_lock 
----------------------
          t
(1 row)

pg_try_advisory_lock_shared(key bigint)
Obtains shared session level advisory lock if available.

SELECT pg_try_advisory_lock_shared(1000);

Result:

 pg_try_advisory_lock_shared 
-----------------------------
              t
(1 row)

pg_try_advisory_lock_shared(key1 int, key2 int)
Obtains shared session level advisory lock if available.

SELECT pg_try_advisory_lock_shared(1000,2000);

Result:

 pg_try_advisory_lock_shared 
-----------------------------
              t
(1 row)

pg_try_advisory_xact_lock(key bigint)
Obtains exclusive transaction level advisory lock if available.

SELECT pg_try_advisory_xact_lock(1000);

Result:

 pg_try_advisory_xact_lock 
---------------------------
             t
(1 row)

pg_try_advisory_xact_lock(key1 int, key2 int)
Obtains exclusive transaction level advisory lock if available.

SELECT pg_try_advisory_xact_lock(1000,2000);

Result:

 pg_try_advisory_xact_lock 
---------------------------
             t
(1 row)

pg_try_advisory_xact_lock_shared(key bigint)
Obtains shared transaction level advisory lock if available.

SELECT pg_try_advisory_xact_lock_shared(10000);

Result:

 pg_try_advisory_xact_lock_shared 
----------------------------------
                t
(1 row)

pg_try_advisory_xact_lock_shared(key1 int, key2 int)
Obtains shared transaction level advisory lock if available.

SELECT pg_try_advisory_xact_lock_shared(10000,20000);

Result:

 pg_try_advisory_xact_lock_shared 
----------------------------------
                t
(1 row)

User-defined function

AgensGraph enables you to create and use functions you need.

As AgensGraph can use SQL and Cypher query statements at the same time, it is easy to create functions using them, and the created functions can be confirmed with \df command. The generated functions can also be called using SELECT or RETURN syntax.

You may refer to PostgreSQL documentation for more information on creation and grammars of user-defined functions.

User-defined function

CREATE FUNCTION func_name (integer, integer) RETURNS integer
    AS 'SELECT $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;
    
SELECT func_name (1, 1);

Result:

 add 
-----
   2
(1 row)  

RETURN func_name (1, 1);

Result:

 add 
-----
   2
(1 row) 

DROP FUNCTION func_name (integer, integer);