PostgreSQL自訂函數

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 A

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 (many 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 included). Within POSIX bracket expressions, the dot character matches a literal dot. For example, a.c matches "abc", etc., but [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" to "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 the ^) character within the brackets: [abc-], [-abc]. Note that backslash escapes are not allowed. The ] character can be included in a bracket expression if it is the first (after the ^) character: []abc].
[^ ]	Matches a single character that is not contained within the brackets. For example, [^abc] matches any character other than "a", "b", or "c". [^a-z] matches any single character that is not a lowercase letter from "a" to "z". As above, 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 what the nth marked subexpression matched, where n is a digit from 1 to 9. This construct is theoretically irregular and 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*c matches "ac", "abc", "abbbc", etc. [xyz]* matches "", "x", "y", "z", "zx", "zyx", "xyzzy", and so on. \(ab\)* matches "", "ab", "abab", "ababab", and so on.
BRE: \{m,n\} ERE: {m,n}	Matches the preceding element at least m and not more than n times. For example, a\{3,5\} matches only "aaa", "aaaa", and "aaaaa". This is not found in a few older instances of regular expressions.

Examples:

• .at matches any three-character string ending with "at", including "hat", "cat", and "bat".
• [hc]at matches "hat" and "cat".
• [^b]at matches all strings matched by .at except "bat".
• ^[hc]at matches "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 above 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