PostgreSQL User-Defined Functions

CREATE OR REPLACE FUNCTION isdigit(text) returns BOOLEAN AS 'select $1 ~ ''^(-)?[0-9]+$'' as result' LANGUAGE sql; --test functionSELECT isdigit('1') --return trueSELECT isdigit('A') --return false

 

 

 

CREATE OR REPLACE FUNCTION isString(text) returns BOOLEAN AS 'select $1 ~ ''^(-)?[a-zA-Z]+$'' as result' LANGUAGE sql; --test functionSELECT isString('1') --return falseSELECT isString('A') --return true

3)IsIPAddress function-I do not think this is a built-in function in other database. This functions is use to validate IP Address allow.

Example

123456789

--check ipaddressCREATE OR REPLACE FUNCTION isIPAddress(text) returns BOOLEAN AS 'select $1 ~ ''^[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+$'' as result' LANGUAGE sql; --test functionSELECT isIPAddress('202.111.0.1') --return trueSELECT isIPAddress('202.ZZZ.0.A') --return falseSELECT isIPAddress('202.11199999999.1.100') --return true

Example B

12345678

CREATE OR REPLACE FUNCTION isIPAddressStrict(text) returns BOOLEAN AS 'select $1 ~ ''^[0-9]?[0-9]?[0-9]?\.[0-9]?[0-9]?[0-9]?\.[0-9]?[0-9]?[0-9]?\.[0-9]?[0-9]?[0-9]?$'' as result' LANGUAGE sql; --test functionSELECT isIPAddressStrict('202.111.0.1') --return trueSELECT isIPAddressStrict('202.ZZZ.0.A') --return falseSELECT isIPAddressStrict('202.11199999999.1.100') --return false

 

Metacharacter	Description
.	Matches any single character (applications exclude newlines, and exactly which characters are considered newlines is flavor, character encoding, and platform specific, but it is safe to assume that the line feed character is wrongly DED ). within POSIX bracket expressions, the dot character matches a literal dot. for example,a.cMatches"Abc", Etc.,[a.c]Matches only"A",".", Or"C".
[ ]	A bracket expression. Matches a single character that is contained within the brackets. For example,[abc]Matches"A","B", Or"C".[a-z]Specifies a range which matches any lowercase letter from"A""Z". These forms can be mixed:[abcx-z]Matches"A","B","C","X","Y", Or"Z", As does[a-cx-z]. The-Character is treated as a literal character if it is the last or the first (after^) Character within the brackets:[abc-],[-abc]. Note that backslash escapes are not allowed.]Character can be encoded in a bracket expression if it is the first (after^) Character:[]abc].
[^ ]	Matches a single character that is not contained within the brackets. For example,[^abc]Matches any character other"A","B", Or"C".[^a-z]Matches any single character that is not a lowercase letter from"A""Z". As abve, literal characters and ranges can be mixed.
^	Matches the starting position within the string. In line-based tools, it matches the starting position of any line.
$	Matches the ending position of the string or the position just before a string-ending newline. In line-based tools, it matches the ending position of any line.
BRE:\( \) ERE:( )	Defines a marked subexpression. The string matched within the parentheses can be recalled later (see the next entry,\n). A marked subexpression is also called a block or capturing group.
\n	Matches whatNTh marked subexpression matched, whereNIs a digit from 1 to 9. This construct is theoreticallyIrregularAnd was not adopted in the posix ere syntax. Some tools allow referencing more than nine capturing groups.
*	Matches the preceding element zero or more times. For example,ab*cMatches"Ac","Abc","Abbbc", Etc.[xyz]*Matches "","X","Y","Z","Zx","Zyx","Xybench", And so on.\(ab\)*Matches "","AB","Abab","Ababab", And so on.
BRE:\{m,n\} ERE:{m,n}	Matches the preceding element at leastMAnd not moreNTimes. For example,a\{3,5\}Matches only"Aaa","Aaaa", And"Aaaaa". This is not found in a few older instances of regular expressions.

Examples:

• .atMatches any three-character string ending with "at", including"Hat","Cat", And"Bat".
• [hc]atMatches"Hat"And"Cat".
• [^b]atMatches all strings matched.atExcept"Bat".
• ^[hc]atMatches"Hat"And"Cat", But only at the beginning of the string or line.
• [hc]at$Matches"Hat"And"Cat", But only at the end of the string or line.
• \[.\]Matches any single character surrounded by "[" and "]" since the brackets are escaped, for example :"[A]"And"[B]".

Wow, impressive right? Actually Regular Expression is more powerful than abve simple function. It is worth to invest time to study on it. Please go here if you want to know more about it
Http://en.wikipedia.org/wiki/Regular_expression