postgres pattern matches
http://www.postgresql.org/docs/7.4/static/functions-matching.html
PostgreSQL
Search Documentation:
Text Size: Normal / Large
Home → Documentation → Manuals → PostgreSQL 7.4
PostgreSQL 7.4.24 Documentation
Prev Fast Backward Chapter 9. Functions and Operators Fast Forward Next
9.6. Pattern Matching
There are three separate approaches to pattern matching provided by PostgreSQL: the traditional SQL LIKE operator, the more recent SQL99 SIMILAR TO operator, and POSIX-style regular expressions. Additionally, a pattern matching function, substring, is available, using either SQL99-style or POSIX-style regular expressions.
Tip: If you have pattern matching needs that go beyond this, consider writing a user-defined function in Perl or Tcl.
9.6.1. LIKE
string LIKE pattern [ESCAPE escape-character]
string NOT LIKE pattern [ESCAPE escape-character]
Every pattern defines a set of strings. The LIKE expression returns true if the string is contained in the set of strings represented by pattern. (As expected, the NOT LIKE expression returns false if LIKE returns true, and vice versa. An equivalent expression is NOT (string LIKE pattern).)
If pattern does not contain percent signs or underscore, then the pattern only represents the string itself; in that case LIKE acts like the equals operator. An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any string of zero or more characters.
Some examples:
'abc' LIKE 'abc' true
'abc' LIKE 'a%' true
'abc' LIKE '_b_' true
'abc' LIKE 'c' false
LIKE pattern matches always cover the entire string. To match a pattern anywhere within a string, the pattern must therefore start and end with a percent sign.
To match a literal underscore or percent sign without matching other characters, the respective character in pattern must be preceded by the escape character. The default escape character is the backslash but a different one may be selected by using the ESCAPE clause. To match the escape character itself, write two escape characters.
Note that the backslash already has a special meaning in string literals, so to write a pattern constant that contains a backslash you must write two backslashes in an SQL statement. Thus, writing a pattern that actually matches a literal backslash means writing four backslashes in the statement. You can avoid this by selecting a different escape character with ESCAPE; then a backslash is not special to LIKE anymore. (But it is still special to the string literal parser, so you still need two of them.)
It's also possible to select no escape character by writing ESCAPE ''. This effectively disables the escape mechanism, which makes it impossible to turn off the special meaning of underscore and percent signs in the pattern.
The key word ILIKE can be used instead of LIKE to make the match case insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.
The operator ~~ is equivalent to LIKE, and ~~* corresponds to ILIKE. There are also !~~ and !~~* operators that represent NOT LIKE and NOT ILIKE, respectively. All of these operators are PostgreSQL-specific.
9.6.2. SIMILAR TO and SQL99 Regular Expressions
string SIMILAR TO pattern [ESCAPE escape-character]
string NOT SIMILAR TO pattern [ESCAPE escape-character]
The SIMILAR TO operator returns true or false depending on whether its pattern matches the given string. It is much like LIKE, except that it interprets the pattern using SQL99's definition of a regular expression. SQL99's regular expressions are a curious cross between LIKE notation and common regular expression notation.
Like LIKE, the SIMILAR TO operator succeeds only if its pattern matches the entire string; this is unlike common regular expression practice, wherein the pattern may match any part of the string. Also like LIKE, SIMILAR TO uses _ and % as wildcard characters denoting any single character and any string, respectively (these are comparable to . and .* in POSIX regular expressions).
In addition to these facilities borrowed from LIKE, SIMILAR TO supports these pattern-matching metacharacters borrowed from POSIX regular expressions:
*
| denotes alternation (either of two alternatives).
*
* denotes repetition of the previous item zero or more times.
*
+ denotes repetition of the previous item one or more times.
*
Parentheses () may be used to group items into a single logical item.
*
A bracket expression [...] specifies a character class, just as in POSIX regular expressions.
Notice that bounded repetition (? and {...}) are not provided, though they exist in POSIX. Also, the dot (.) is not a metacharacter.
As with LIKE, a backslash disables the special meaning of any of these metacharacters; or a different escape character can be specified with ESCAPE.
Some examples:
'abc' SIMILAR TO 'abc' true
'abc' SIMILAR TO 'a' false
'abc' SIMILAR TO '%(b|d)%' true
'abc' SIMILAR TO '(b|c)%' false
The substring function with three parameters, substring(string from pattern for escape-character), provides extraction of a substring that matches a SQL99 regular expression pattern. As with SIMILAR TO, the specified pattern must match to the entire data string, else the function fails and returns null. To indicate the part of the pattern that should be returned on success, the pattern must contain two occurrences of the escape character followed by a double quote ("). The text matching the portion of the pattern between these markers is returned.
Some examples:
substring('foobar' from '%#"o_b#"%' for '#') oob
substring('foobar' from '#"o_b#"%' for '#') NULL
9.6.3. POSIX Regular Expressions
Table 9-11 lists the available operators for pattern matching using POSIX regular expressions.
Table 9-11. Regular Expression Match Operators
Operator Description Example
~ Matches regular expression, case sensitive 'thomas' ~ '.*thomas.*'
~* Matches regular expression, case insensitive 'thomas' ~* '.*Thomas.*'
!~ Does not match regular expression, case sensitive 'thomas' !~ '.*Thomas.*'
!~* Does not match regular expression, case insensitive 'thomas' !~* '.*vadim.*'
POSIX regular expressions provide a more powerful means for pattern matching than the LIKE and SIMILAR TO operators. Many Unix tools such as egrep, sed, or awk use a pattern matching language that is similar to the one described here.
A regular expression is a character sequence that is an abbreviated definition of a set of strings (a regular set). A string is said to match a regular expression if it is a member of the regular set described by the regular expression. As with LIKE, pattern characters match string characters exactly unless they are special characters in the regular expression language --- but regular expressions use different special characters than LIKE does. Unlike LIKE patterns, a regular expression is allowed to match anywhere within a string, unless the regular expression is explicitly anchored to the beginning or end of the string.
Some examples:
'abc' ~ 'abc' true
'abc' ~ '^a' true
'abc' ~ '(b|d)' true
'abc' ~ '^(b|c)' false
The substring function with two parameters, substring(string from pattern), provides extraction of a substring that matches a POSIX regular expression pattern. It returns null if there is no match, otherwise the portion of the text that matched the pattern. But if the pattern contains any parentheses, the portion of the text that matched the first parenthesized subexpression (the one whose left parenthesis comes first) is returned. You can always put parentheses around the whole expression if you want to use parentheses within it without triggering this exception. Also see the non-capturing parentheses described below.
Some examples:
substring('foobar' from 'o.b') oob
substring('foobar' from 'o(.)b') o
PostgreSQL's regular expressions are implemented using a package written by Henry Spencer. Much of the description of regular expressions below is copied verbatim from his manual entry.
9.6.3.1. Regular Expression Details
Regular expressions (REs), as defined in POSIX 1003.2, come in two forms: extended REs or EREs (roughly those of egrep), and basic REs or BREs (roughly those of ed). PostgreSQL supports both forms, and also implements some extensions that are not in the POSIX standard, but have become widely used anyway due to their availability in programming languages such as Perl and Tcl. REs using these non-POSIX extensions are called advanced REs or AREs in this documentation. AREs are almost an exact superset of EREs, but BREs have several notational incompatibilities (as well as being much more limited). We first describe the ARE and ERE forms, noting features that apply only to AREs, and then describe how BREs differ.
Note: The form of regular expressions accepted by PostgreSQL can be chosen by setting the regex_flavor run-time parameter (described in Section 16.4). The usual setting is advanced, but one might choose extended for maximum backwards compatibility with pre-7.4 releases of PostgreSQL.
A regular expression is defined as one or more branches, separated by |. It matches anything that matches one of the branches.
A branch is zero or more quantified atoms or constraints, concatenated. It matches a match for the first, followed by a match for the second, etc; an empty branch matches the empty string.
A quantified atom is an atom possibly followed by a single quantifier. Without a quantifier, it matches a match for the atom. With a quantifier, it can match some number of matches of the atom. An atom can be any of the possibilities shown in Table 9-12. The possible quantifiers and their meanings are shown in Table 9-13.
A constraint matches an empty string, but matches only when specific conditions are met. A constraint can be used where an atom could be used, except it may not be followed by a quantifier. The simple constraints are shown in Table 9-14; some more constraints are described later.
Table 9-12. Regular Expression Atoms
Atom Description
(re) (where re is any regular expression) matches a match for re, with the match noted for possible reporting
(?:re) as above, but the match is not noted for reporting (a "non-capturing" set of parentheses) (AREs only)
. matches any single character
[chars] a bracket expression, matching any one of the chars (see Section 9.6.3.2 for more detail)
\k (where k is a non-alphanumeric character) matches that character taken as an ordinary character, e.g. \\ matches a backslash character
\c where c is alphanumeric (possibly followed by other characters) is an escape, see Section 9.6.3.3 (AREs only; in EREs and BREs, this matches c)
{ when followed by a character other than a digit, matches the left-brace character {; when followed by a digit, it is the beginning of a bound (see below)
x where x is a single character with no other significance, matches that character
An RE may not end with \.
Note: Remember that the backslash (\) already has a special meaning in PostgreSQL string literals. To write a pattern constant that contains a backslash, you must write two backslashes in the statement.
Table 9-13. Regular Expression Quantifiers
Quantifier Matches
* a sequence of 0 or more matches of the atom
+ a sequence of 1 or more matches of the atom
? a sequence of 0 or 1 matches of the atom
{m} a sequence of exactly m matches of the atom
{m,} a sequence of m or more matches of the atom
{m,n} a sequence of m through n (inclusive) matches of the atom; m may not exceed n
*? non-greedy version of *
+? non-greedy version of +
?? non-greedy version of ?
{m}? non-greedy version of {m}
{m,}? non-greedy version of {m,}
{m,n}? non-greedy version of {m,n}
The forms using {...} are known as bounds. The numbers m and n within a bound are unsigned decimal integers with permissible values from 0 to 255 inclusive.
Non-greedy quantifiers (available in AREs only) match the same possibilities as their corresponding normal (greedy) counterparts, but prefer the smallest number rather than the largest number of matches. See Section 9.6.3.5 for more detail.
Note: A quantifier cannot immediately follow another quantifier. A quantifier cannot begin an expression or subexpression or follow ^ or |.
Table 9-14. Regular Expression Constraints
Constraint Description
^ matches at the beginning of the string
$ matches at the end of the string
(?=re) positive lookahead matches at any point where a substring matching re begins (AREs only)
(?!re) negative lookahead matches at any point where no substring matching re begins (AREs only)
Lookahead constraints may not contain back references (see Section 9.6.3.3), and all parentheses within them are considered non-capturing.
9.6.3.2. Bracket Expressions
A bracket expression is a list of characters enclosed in []. It normally matches any single character from the list (but see below). If the list begins with ^, it matches any single character not from the rest of the list. If two characters in the list are separated by -, this is shorthand for the full range of characters between those two (inclusive) in the collating sequence, e.g. [0-9] in ASCII matches any decimal digit. It is illegal for two ranges to share an endpoint, e.g. a-c-e. Ranges are very collating-sequence-dependent, so portable programs should avoid relying on them.
To include a literal ] in the list, make it the first character (following a possible ^). To include a literal -, make it the first or last character, or the second endpoint of a range. To use a literal - as the first endpoint of a range, enclose it in [. and .] to make it a collating element (see below). With the exception of these characters, some combinations using [ (see next paragraphs), and escapes (AREs only), all other special characters lose their special significance within a bracket expression. In particular, \ is not special when following ERE or BRE rules, though it is special (as introducing an escape) in AREs.
Within a bracket expression, a collating element (a character, a multiple-character sequence that collates as if it were a single character, or a collating-sequence name for either) enclosed in [. and .] stands for the sequence of characters of that collating element. The sequence is a single element of the bracket expression's list. A bracket expression containing a multiple-character collating element can thus match more than one character, e.g. if the collating sequence includes a ch collating element, then the RE [[.ch.]]*c matches the first five characters of chchcc.
Note: PostgreSQL currently has no multi-character collating elements. This information describes possible future behavior.
Within a bracket expression, a collating element enclosed in [= and =] is an equivalence class, standing for the sequences of characters of all collating elements equivalent to that one, including itself. (If there are no other equivalent collating elements, the treatment is as if the enclosing delimiters were [. and .].) For example, if o and ^ are the members of an equivalence class, then [[=o=]], [[=^=]], and [o^] are all synonymous. An equivalence class may not be an endpoint of a range.
Within a bracket expression, the name of a character class enclosed in [: and :] stands for the list of all characters belonging to that class. Standard character class names are: alnum, alpha, blank, cntrl, digit, graph, lower, print, punct, space, upper, xdigit. These stand for the character classes defined in ctype. A locale may provide others. A character class may not be used as an endpoint of a range.
There are two special cases of bracket expressions: the bracket expressions [[:<:]] and [[:>:]] are constraints, matching empty strings at the beginning and end of a word respectively. A word is defined as a sequence of word characters that is neither preceded nor followed by word characters. A word character is an alnum character (as defined by ctype) or an underscore. This is an extension, compatible with but not specified by POSIX 1003.2, and should be used with caution in software intended to be portable to other systems. The constraint escapes described below are usually preferable (they are no more standard, but are certainly easier to type).
9.6.3.3. Regular Expression Escapes
Escapes are special sequences beginning with \ followed by an alphanumeric character. Escapes come in several varieties: character entry, class shorthands, constraint escapes, and back references. A \ followed by an alphanumeric character but not constituting a valid escape is illegal in AREs. In EREs, there are no escapes: outside a bracket expression, a \ followed by an alphanumeric character merely stands for that character as an ordinary character, and inside a bracket expression, \ is an ordinary character. (The latter is the one actual incompatibility between EREs and AREs.)
Character-entry escapes exist to make it easier to specify non-printing and otherwise inconvenient characters in REs. They are shown in Table 9-15.
Class-shorthand escapes provide shorthands for certain commonly-used character classes. They are shown in Table 9-16.
A constraint escape is a constraint, matching the empty string if specific conditions are met, written as an escape. They are shown in Table 9-17.
A back reference (\n) matches the same string matched by the previous parenthesized subexpression specified by the number n (see Table 9-18). For example, ([bc])\1 matches bb or cc but not bc or cb. The subexpression must entirely precede the back reference in the RE. Subexpressions are numbered in the order of their leading parentheses. Non-capturing parentheses do not define subexpressions.
Note: Keep in mind that an escape's leading \ will need to be doubled when entering the pattern as an SQL string constant.
Table 9-15. Regular Expression Character-Entry Escapes
Escape Description
\a alert (bell) character, as in C
\b backspace, as in C
\B synonym for \ to help reduce the need for backslash doubling
\cX (where X is any character) the character whose low-order 5 bits are the same as those of X, and whose other bits are all zero
\e the character whose collating-sequence name is ESC, or failing that, the character with octal value 033
\f form feed, as in C
\n newline, as in C
\r carriage return, as in C
\t horizontal tab, as in C
\uwxyz (where wxyz is exactly four hexadecimal digits) the Unicode character U+wxyz in the local byte ordering
\Ustuvwxyz (where stuvwxyz is exactly eight hexadecimal digits) reserved for a somewhat-hypothetical Unicode extension to 32 bits
\v vertical tab, as in C
\xhhh (where hhh is any sequence of hexadecimal digits) the character whose hexadecimal value is 0xhhh (a single character no matter how many hexadecimal digits are used)
\0 the character whose value is 0
\xy (where xy is exactly two octal digits, and is not a back reference) the character whose octal value is 0xy
\xyz (where xyz is exactly three octal digits, and is not a back reference) the character whose octal value is 0xyz
Hexadecimal digits are 0-9, a-f, and A-F. Octal digits are 0-7.
The character-entry escapes are always taken as ordinary characters. For example, \135 is ] in ASCII, but \135 does not terminate a bracket expression.
Table 9-16. Regular Expression Class-Shorthand Escapes
Escape Description
\d [[:digit:]]
\s [[:space:]]
\w [[:alnum:]_] (note underscore is included)
\D [^[:digit:]]
\S [^[:space:]]
\W [^[:alnum:]_] (note underscore is included)
Within bracket expressions, \d, \s, and \w lose their outer brackets, and \D, \S, and \W are illegal. (So, for example, [a-c\d] is equivalent to [a-c[:digit:]]. Also, [a-c\D], which is equivalent to [a-c^[:digit:]], is illegal.)
Table 9-17. Regular Expression Constraint Escapes
Escape Description
\A matches only at the beginning of the string (see Section 9.6.3.5 for how this differs from ^)
\m matches only at the beginning of a word
\M matches only at the end of a word
\y matches only at the beginning or end of a word
\Y matches only at a point that is not the beginning or end of a word
\Z matches only at the end of the string (see Section 9.6.3.5 for how this differs from $)
A word is defined as in the specification of [[:<:]] and [[:>:]] above. Constraint escapes are illegal within bracket expressions.
Table 9-18. Regular Expression Back References
Escape Description
\m (where m is a nonzero digit) a back reference to the m'th subexpression
\mnn (where m is a nonzero digit, and nn is some more digits, and the decimal value mnn is not greater than the number of closing capturing parentheses seen so far) a back reference to the mnn'th subexpression
Note: There is an inherent historical ambiguity between octal character-entry escapes and back references, which is resolved by heuristics, as hinted at above. A leading zero always indicates an octal escape. A single non-zero digit, not followed by another digit, is always taken as a back reference. A multi-digit sequence not starting with a zero is taken as a back reference if it comes after a suitable subexpression (i.e. the number is in the legal range for a back reference), and otherwise is taken as octal.
9.6.3.4. Regular Expression Metasyntax
In addition to the main syntax described above, there are some special forms and miscellaneous syntactic facilities available.
Normally the flavor of RE being used is determined by regex_flavor. However, this can be overridden by a director prefix. If an RE of any flavor begins with ***:, the rest of the RE is taken as an ARE. If an RE of any flavor begins with ***=, the rest of the RE is taken to be a literal string, with all characters considered ordinary characters.
An ARE may begin with embedded options: a sequence (?xyz) (where xyz is one or more alphabetic characters) specifies options affecting the rest of the RE. These options override any previously determined options (including both the RE flavor and case sensitivity). The available option letters are shown in Table 9-19.
Table 9-19. ARE Embedded-Option Letters
Option Description
b rest of RE is a BRE
c case-sensitive matching (overrides operator type)
e rest of RE is an ERE
i case-insensitive matching (see Section 9.6.3.5) (overrides operator type)
m historical synonym for n
n newline-sensitive matching (see Section 9.6.3.5)
p partial newline-sensitive matching (see Section 9.6.3.5)
q rest of RE is a literal ("quoted") string, all ordinary characters
s non-newline-sensitive matching (default)
t tight syntax (default; see below)
w inverse partial newline-sensitive ("weird") matching (see Section 9.6.3.5)
x expanded syntax (see below)
Embedded options take effect at the ) terminating the sequence. They are available only at the start of an ARE, and may not be used later within it.
In addition to the usual (tight) RE syntax, in which all characters are significant, there is an expanded syntax, available by specifying the embedded x option. In the expanded syntax, white-space characters in the RE are ignored, as are all characters between a # and the following newline (or the end of the RE). This permits paragraphing and commenting a complex RE. There are three exceptions to that basic rule:
*
a white-space character or # preceded by \ is retained
*
white space or # within a bracket expression is retained
*
white space and comments are illegal within multi-character symbols, like the ARE (?: or the BRE \(
Expanded-syntax white-space characters are blank, tab, newline, and any character that belongs to the space character class.
Finally, in an ARE, outside bracket expressions, the sequence (?#ttt) (where ttt is any text not containing a )) is a comment, completely ignored. Again, this is not allowed between the characters of multi-character symbols, like (?:. Such comments are more a historical artifact than a useful facility, and their use is deprecated; use the expanded syntax instead.
None of these metasyntax extensions is available if an initial ***= director has specified that the user's input be treated as a literal string rather than as an RE.
9.6.3.5. Regular Expression Matching Rules
In the event that an RE could match more than one substring of a given string, the RE matches the one starting earliest in the string. If the RE could match more than one substring starting at that point, its choice is determined by its preference: either the longest substring, or the shortest.
Most atoms, and all constraints, have no preference. A parenthesized RE has the same preference (possibly none) as the RE. A quantified atom with quantifier {m} or {m}? has the same preference (possibly none) as the atom itself. A quantified atom with other normal quantifiers (including {m,n} with m equal to n) prefers longest match. A quantified atom with other non-greedy quantifiers (including {m,n}? with m equal to n) prefers shortest match. A branch has the same preference as the first quantified atom in it which has a preference. An RE consisting of two or more branches connected by the | operator prefers longest match.
Subject to the constraints imposed by the rules for matching the whole RE, subexpressions also match the longest or shortest possible substrings, based on their preferences, with subexpressions starting earlier in the RE taking priority over ones starting later. Note that outer subexpressions thus take priority over their component subexpressions.
The quantifiers {1,1} and {1,1}? can be used to force longest and shortest preference, respectively, on a subexpression or a whole RE.
Match lengths are measured in characters, not collating elements. An empty string is considered longer than no match at all. For example: bb* matches the three middle characters of abbbc; (week|wee)(night|knights) matches all ten characters of weeknights; when (.*).* is matched against abc the parenthesized subexpression matches all three characters; and when (a*)* is matched against bc both the whole RE and the parenthesized subexpression match an empty string.
If case-independent matching is specified, the effect is much as if all case distinctions had vanished from the alphabet. When an alphabetic that exists in multiple cases appears as an ordinary character outside a bracket expression, it is effectively transformed into a bracket expression containing both cases, e.g. x becomes [xX]. When it appears inside a bracket expression, all case counterparts of it are added to the bracket expression, e.g. [x] becomes [xX] and [^x] becomes [^xX].
If newline-sensitive matching is specified, . and bracket expressions using ^ will never match the newline character (so that matches will never cross newlines unless the RE explicitly arranges it) and ^and $ will match the empty string after and before a newline respectively, in addition to matching at beginning and end of string respectively. But the ARE escapes \A and \Z continue to match beginning or end of string only.
If partial newline-sensitive matching is specified, this affects . and bracket expressions as with newline-sensitive matching, but not ^ and $.
If inverse partial newline-sensitive matching is specified, this affects ^ and $ as with newline-sensitive matching, but not . and bracket expressions. This isn't very useful but is provided for symmetry.
9.6.3.6. Limits and Compatibility
No particular limit is imposed on the length of REs in this implementation. However, programs intended to be highly portable should not employ REs longer than 256 bytes, as a POSIX-compliant implementation can refuse to accept such REs.
The only feature of AREs that is actually incompatible with POSIX EREs is that \ does not lose its special significance inside bracket expressions. All other ARE features use syntax which is illegal or has undefined or unspecified effects in POSIX EREs; the *** syntax of directors likewise is outside the POSIX syntax for both BREs and EREs.
Many of the ARE extensions are borrowed from Perl, but some have been changed to clean them up, and a few Perl extensions are not present. Incompatibilities of note include \b, \B, the lack of special treatment for a trailing newline, the addition of complemented bracket expressions to the things affected by newline-sensitive matching, the restrictions on parentheses and back references in lookahead constraints, and the longest/shortest-match (rather than first-match) matching semantics.
Two significant incompatibilities exist between AREs and the ERE syntax recognized by pre-7.4 releases of PostgreSQL:
*
In AREs, \ followed by an alphanumeric character is either an escape or an error, while in previous releases, it was just another way of writing the alphanumeric. This should not be much of a problem because there was no reason to write such a sequence in earlier releases.
*
In AREs, \ remains a special character within [], so a literal \ within a bracket expression must be written \\.
While these differences are unlikely to create a problem for most applications, you can avoid them if necessary by setting regex_flavor to extended.
9.6.3.7. Basic Regular Expressions
BREs differ from EREs in several respects. |, +, and ? are ordinary characters and there is no equivalent for their functionality. The delimiters for bounds are \{ and \}, with { and } by themselves ordinary characters. The parentheses for nested subexpressions are \( and \), with ( and ) by themselves ordinary characters. ^ is an ordinary character except at the beginning of the RE or the beginning of a parenthesized subexpression, $ is an ordinary character except at the end of the RE or the end of a parenthesized subexpression, and * is an ordinary character if it appears at the beginning of the RE or the beginning of a parenthesized subexpression (after a possible leading ^). Finally, single-digit back references are available, and \< and \> are synonyms for [[:<:]] and [[:>:]] respectively; no other escapes are available.
Prev Home Next
Binary String Functions and Operators Up Data Type Formatting Functions
Privacy Policy | Project hosted by our server sponsors. | Designed by tinysofa
Copyright © 1996 – 2009 PostgreSQL Global Development Group
Writing PostgreSQL Functions with PL/pgSQL
by David E. Wheeler05/11/2006
One of the most powerful features of PostgreSQL is its support for user-defined functions written in various programming languages, including pure SQL, C, Perl, Python, and PHP. Perhaps the most common programming language for PostgreSQL functions, however, is PL/pgSQL (don't ask me to pronounce it), because it comes with PostgreSQL and is easy to set up.
PL/pgSQL is a procedural language similar to Oracle's PL/SQL. It's much more powerful than pure SQL in that it supports variables, conditional expressions, looping constructs, exceptions, and the like. Because it natively supports all of PostgreSQL's SQL syntax, you can consider it a superset of PostgreSQL SQL. It also respects all data types and their associated functions and operators, and is completely safe for use inside of the server. Get all of the details in the PL/pgSQL documentation.
Installing PL/pgSQL
To get started with PL/pgSQL, first make sure it's installed in your PostgreSQL database. If it was a part of the template1 database when your database was created, it will already be installed. To see whether you have it, run the following in the psql client:
SELECT true FROM pg_catalog.pg_language WHERE lanname = 'plpgsql';If the result row has the value true, PL/pgSQL is already installed in your database. If not, quit psql and execute the command:
$ createlang plpgsql database_nameTo add a language, you must have superuser access to the database. If you've just installed PostgreSQL, then you can likely use the default postgres user by passing -U postgres to createlang. From this point, you should be able to follow along by pasting the sample functions into psql.
A First Function
To write your first PL/pgSQL function, start with something simple: a function to return the Fibonacci number for a position in the Fibonacci sequence. I know, I know; everyone uses a Fibonacci calculator to demonstrate code. Why can't I be original? Because a couple iterations of such a function will show off some of the more useful features of PL/pgSQL. It's purely pedagogical. A simple implementation is:
1 CREATE OR REPLACE FUNCTION fib (
2 fib_for integer
3 ) RETURNS integer AS $$
4 BEGIN
5 IF fib_for < 2 THEN
6 RETURN fib_for;
7 END IF;
8 RETURN fib(fib_for - 2) + fib(fib_for - 1);
9 END;
10 $$ LANGUAGE plpgsql;Using the function is easy:
try=% select fib(8);
fib
-----
21
(1 row)The first line uses PostgreSQL's CREATE OR REPLACE FUNCTION statement to create the function. The name of the function is fib. The CREATE OR REPLACE FUNCTION statement is more useful in practice than the simple CREATE FUNCTION statement, because it will drop an existing function with the same name and argument signature before creating the new one. This is very convenient while you're developing and testing a new function.
The second line declares the integer variable fib_for as the sole argument to the function, and thus constitutes its entire argument signature. The argument signature must come after the name of the function, inside parentheses. In this respect, it's not much different than function or method declarations in most programming languages. Arguments can be of any type supported by PostgreSQL, including user-created types and domains, as well as composite data types such as table row types. This article's examples will use only simple data types, but see the PL/pgSQL Declarations documentation for details.
Note that named arguments were added to PL/pgSQL in PostgreSQL 8.0. In earlier versions of PostgreSQL, you must either use the default, numbered variable names for the arguments, or declare aliases in a DECLARE block:
CREATE OR REPLACE FUNCTION fib ( integer ) RETURNS integer AS $$
DECLARE
fib_for ALIAS FOR $1;
BEGIN
-- ...Unless you have an older version of PostgreSQL, use named arguments. They're more convenient.
The third line closes the argument signature and specifies the function return value (integer). As with arguments, the return value of a function can be any PostgreSQL data type, including a composite type or even a cursor. The end of line three has the odd string $$. This is PostgreSQL dollar-quoting. When used in place of the usual single-quotation mark quoting ('), you don't have to escape single quotation marks within the body of the function. This makes them much easier to read.
Line four's BEGIN statement marks the start of the function body, while lines 5-8 are the function body, implenting the standard recursive algorithm for determining a Fibonacci number. Lines 5-7 use the PL/pgSQL IF-THEN conditional statement to return the sequence number itself if it is less than two. As with all blocks in PL/pgSQL, the IF-THEN conditional ends with a final END statement. Conditional expressions in PL/pgSQL can be any SQL expression that you might use in the WHERE clause of a typical SELECT statement. The nice thing here, however, is that you can use a variable (in this case, fib_for) in the expression.
Line five demonstrates the ability of PL/pgSQL to not only execute other PL/pgSQL functions, but to do so recursively. In this case, the fib() function calls itself twice in order to properly determine and return the Fibonacci number. Note that you can use the PL/pgSQL RETURN keyword anywhere in a PL/pgSQL function to terminate the execution of the function and return a value.
Line nine's END statement signals the end of the function body, while line ten closes the dollar quoting and identifies the function implementation language.
A Note on Statement Termination
At first glance, the placement of semicolons to terminate statements in the example function might appear to be somewhat ad hoc. I assure you that it is not. In PL/pgSQL, all blocks must terminate in a semicolon, as must all statements within that block. The expression that initiates the block, however, such as BEGIN on line 4 or IF fib_for <> on line five, does not end with semicolons. Line six, as a complete statement within the IF ... THEN block, ends with a semicolon, as does the statement on line eight.
Perhaps the simplest way to remember this rule is to think of statements as requiring semicolons, and block initiation expressions as not being complete statements. That is, blocks only become complete statements when they END.
