PostgreSQL Functions
In general terms, a function is a set of statements that take inputs, do some specific computation and produces output.
Types of Functions:
Single Row Functions: These are functions that operate on individual records and give output per record.
Multiple Row Functions: These functions focus on obtaining summary information on average for a group of records.
Single Row Functions :
String Functions :
These functions are used to manipulate strings.
| Function | Return Type | Description | Example | Result |
|---|---|---|---|---|
| string || string | Text | String Concatenation | SELECT 'Post||'greSQL' | PostgreSQL |
| char_length(string) | Int | Number of character in the string | SELECT char_length('Jack') | 4 |
| Position(substr in string) | Int | Location of specified string | SELECT position('hn' in John) | 3 |
Number Functions:
These functions work on numeric data.
| Function | Return Type | Description | Example | Result |
|---|---|---|---|---|
| |/ (number) | Int | Square root of the given number | Select |/(25) | 5 |
| power(a,b) | Int | Return a^b | SELECT power(2,3) | 8 |
| Mod(y,x) | Int | Return the reminder after dividing y to x | SELECT mod(25,5) | 0 |
Date Functions :
These functions work on data of date or time stamp type.
| Function | Return Type | Description | Example | Result |
|---|---|---|---|---|
| now() | Timestamp with timezone | current date and time | Select now() | 2019-10-11 15:05:38.710882 +5:30 |
| justify_days(interval) | Interval | Adjust interval so 30 days time period are represented as months | SELECT justify_days(interval '35 days') | 1 month 5 days |
| Extract(fields from source) | Double precision | Retrives subfields such as year or hour from date/time value | SELECT extract(Year from hire_date) from employees | 1 Year |
Data Type Formatting or Conversion Functions: :
| Function | Return Type | Description | Example |
|---|---|---|---|
| to_char(timestamp, text) | text | Converting timestamp to string | to_char(current_timestamp, 'HH12:MI:SS') |
| to_char(interval, text) | text | Converting interval to string | to_char(interval '15h 12m 36s', 'HH12:MI:SS') |
| to_char(int, text) | text | Converting integer to string | to_char(125, '999') |
| to_char(double precision, text) | text | Converting real/double precision to string | to_char(128.8::real, '999D9') |
| to_char(numeric, text) | text | Converting numeric to string | to_char(-125.8, '999D99S') |
| to_char(text, text) | date | Converting string to date | to_date('05 DEC 2018', 'DD MON YYYY') |
| to_number(text, text) | numeric | Converting string to numeric | to_number('12,454.8-', '99g999D9S') |
| to_timestamp(text, text) | timestamp with timezone | Converting string to timestamp | to_timestamp('05 DEC 2018', 'DD MON YYYY') |
Multiple Row Functions :
Multiple Row function is also called group function or aggregate functions..
| Function | Return Type | Description |
|---|---|---|
| max(col_name) | numeric | Max function is used to return the Maximum value of a given column |
| min(col_name) | numeric | Min function is used to return the Minimum value of a given column |
| sum(col_name) | numeric | Sum function is used to return the sum(total) of a given column. |
| count(col_name) | numeric | Count function is used to return the number of records present in a given column |
| avg(col_name) | numeric | Avg function is used to return the Average value of a given column |
Visit :
Discussion