Greenplum 내장함수

8.      Built in function

1)        Greenplum 내장함수

n  Pivotal Greenplum4.3은 Postgresql 8.2 버전을 Core로 사용하고 있으며, Built in 함수는 링크 참조 :  http://www.postgresql.org/docs/8.2/interactive/functions.html

 

n  Pivotal Greenplum5은 Postgresql 8.3 버전을 Core로 사용

 

n 필요한 함수는 Googling 해서 찾는게 빠름

2)        SQL String Functions and Operators

Function	Return Type	Description	Example	Result
string || string	text	String concatenation	'Post' || 'greSQL'	PostgreSQL
string || non-string or non-string || string	text	String concatenation with one non-string input	'Value: ' || 42	Value: 42
bit_length(string)	int	Number of bits in string	bit_length('jose')	32
char_length(string) or character_length(string)	int	Number of characters in string	char_length('jose')	4
lower(string)	text	Convert string to lower case	lower('TOM')	tom
octet_length(string)	int	Number of bytes in string	octet_length('jose')	4
overlay(string placing string from int [for int])	text	Replace substring	overlay('Txxxxas' placing 'hom' from 2 for 4)	Thomas
position(substring in string)	int	Location of specified substring	position('om' in 'Thomas')	3
substring(string [from int] [for int])	text	Extract substring	substring('Thomas' from 2 for 3)	hom
substring(string from pattern)	text		substring('Thomas' from '...$')	mas
substring(string from pattern for escape)	text		substring('Thomas' from '%#"o_a#"_' for '#')	oma
trim([leading | trailing | both] [characters] from string)	text	Remove the longest string containing only the characters (a space by default) from the start/end/both ends of the string	trim(both 'x' from 'xTomxx')	Tom
upper(string)	text	Convert string to uppercase	upper('tom')	TOM

 

3)        Binary String Functions and Operators

Function	Return Type	Description	Example	Result
string || string	bytea	String concatenation	E'\\\\Post'::bytea || E'\\047gres\\000'::bytea	\\Post'gres\000
get_bit(string, offset)	int	Extract bit from string	get_bit(E'Th\\000omas'::bytea, 45)	1
get_byte(string, offset)	int	Extract byte from string	get_byte(E'Th\\000omas'::bytea, 4)	109
octet_length(string)	int	Number of bytes in binary string	octet_length(E'jo\\000se'::bytea)	5
position(substring in string)	int	Location of specified substring	position(E'\\000om'::bytea in E'Th\\000omas'::bytea)	3
set_bit(string, offset, newvalue)	bytea	Set bit in string	set_bit(E'Th\\000omas'::bytea, 45, 0)	Th\000omAs
set_byte(string, offset, newvalue)	bytea	Set byte in string	set_byte(E'Th\\000omas'::bytea, 4, 64)	Th\000o@as
substring(string [from int] [for int])	bytea	Extract substring	substring(E'Th\\000omas'::bytea from 2 for 3)	h\000o
trim([both] bytes from string)	bytea	Remove the longest string containing only the bytes in bytes from the start and end of string	trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea)	Tom

 

4)        Formatting Functions

Function	Return Type	Description	Example
to_char(timestamp, text)	text	convert time stamp to string	to_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text)	text	convert interval to string	to_char(interval '15h 2m 12s', 'HH24:MI:SS')
to_char(int, text)	text	convert integer to string	to_char(125, '999')
to_char(double precision, text)	text	convert real/double precision to string	to_char(125.8::real, '999D9')
to_char(numeric, text)	text	convert numeric to string	to_char(-125.8, '999D99S')
to_date(text, text)	date	convert string to date	to_date('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text)	numeric	convert string to numeric	to_number('12,454.8-', '99G999D9S')
to_timestamp(text, text)	timestamp with time zone	convert string to time stamp	to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(double precision)	timestamp with time zone	convert UNIX epoch to time stamp	to_timestamp(200120400)

5)        Date/Time Functions

Function	Return Type	Description	Example	Result
age(timestamp, timestamp)	interval	Subtract arguments, producing a “symbolic” result that uses years and months	age(timestamp '2001-04-10', timestamp '1957-06-13')	43 years 9 mons 27 days
age(timestamp)	interval	Subtract from current_date	age(timestamp '1957-06-13')	43 years 8 mons 3 days
clock_timestamp()	timestamp with time zone	Current date and time
current_date	date	Current date;
current_time	time with time zone	Current time of day;
current_timestamp	timestamp with time zone	Current date and time (start of current transaction);
date_part(text, timestamp)	double precision	Get subfield (equivalent to extract);	date_part('hour', timestamp '2001-02-16 20:38:40')	20
date_part(text, interval)	double precision	Get subfield (equivalent to extract);	date_part('month', interval '2 years 3 months')	3
date_trunc(text, timestamp)	timestamp	Truncate to specified precision;	date_trunc('hour', timestamp '2001-02-16 20:38:40')	36938.83333
extract(field from timestamp)	double precision	Get subfield;	extract(hour from timestamp '2001-02-16 20:38:40')	20
extract(field from interval)	double precision	Get subfield;	extract(month from interval '2 years 3 months')	3
isfinite(timestamp)	boolean	Test for finite time stamp (not equal to infinity)	isfinite(timestamp '2001-02-16 21:28:30')	TRUE
isfinite(interval)	boolean	Test for finite interval	isfinite(interval '4 hours')	TRUE
justify_days(interval)	interval	Adjust interval so 30-day time periods are represented as months	justify_days(interval '30 days')	1 month
justify_hours(interval)	interval	Adjust interval so 24-hour time periods are represented as days	justify_hours(interval '24 hours')	1 day
justify_interval(interval)	interval	Adjust interval using justify_days and justify_hours, with additional sign adjustments	justify_interval(interval '1 mon -1 hour')	29 days 23:00:00
localtime	time	Current time of day
localtimestamp	timestamp	Current date and time (start of current transaction)
now()	timestamp with time zone	Current date and time (start of current transaction)
statement_timestamp()	timestamp with time zone	Current date and time (start of current statement)
timeofday()	text	Current date and time (like clock_timestamp, but as a text string)
transaction_timestamp()	timestamp with time zone	Current date and time (start of current transaction);

6)        Network Address Functions

Function	Return Type	Description	Example	Result
abbrev(inet)	text	abbreviated display format as text	abbrev(inet '10.1.0.0/16')	10.1.0.0/16
abbrev(cidr)	text	abbreviated display format as text	abbrev(cidr '10.1.0.0/16')	10.1/16
broadcast(inet)	inet	broadcast address for network	broadcast('192.168.1.5/24')	192.168.1.255/24
family(inet)	int	extract family of address; 4 for IPv4, 6 for IPv6	family('::1')	6
host(inet)	text	extract IP address as text	host('192.168.1.5/24')	192.168.1.5
hostmask(inet)	inet	construct host mask for network	hostmask('192.168.23.20/30')	0.0.0.3
masklen(inet)	int	extract netmask length	masklen('192.168.1.5/24')	24
netmask(inet)	inet	construct netmask for network	netmask('192.168.1.5/24')	255.255.255.0
network(inet)	cidr	extract network part of address	network('192.168.1.5/24')	192.168.1.0/24
set_masklen(inet, int)	inet	set netmask length for inet value	set_masklen('192.168.1.5/24', 16)	192.168.1.5/16
set_masklen(cidr, int)	cidr	set netmask length for cidr value	set_masklen('192.168.1.0/24'::cidr, 16)	192.168.0.0/16
text(inet)	text	extract IP address and netmask length as text	text(inet '192.168.1.5')	192.168.1.5/32