parse_ident()

A function for splitting a qualified object name into an array

parse_ident() is a system function for splitting a qualified object name into an array.

parse_ident() was added in PostgreSQL 9.6.

Usage

parse_ident ( qualified_identifier text [, strict_mode boolean DEFAULT true ] )

parse_ident() takes a qualified object identifier (for example a table name qualified by its schema name such as foo.bar) and returns an array containing each individual identifier, removing any quoting.

Note that parse_ident() does not validate the identifier other than by checking for characters which are not valid in object names. This behaviour can be overridden by setting strict_mode is set to FALSE, which will allow parse_ident() to parse e.g. function definitions.

Change history

Examples

Basic usage:

postgres=# SELECT parse_ident('foo.bar');
 parse_ident 
-------------
 {foo,bar}
(1 row)

Usage with a partially quoted identifier:

postgres=# SELECT parse_ident('"Foo".bar');
 parse_ident 
-------------
 {Foo,bar}
(1 row)

Usage with an invalid identifier:

postgres=# SELECT parse_ident('foo.bar!');
ERROR:  string is not a valid identifier: "foo.bar!"

Usage with a function name:

postgres=# SELECT parse_ident('foo.bar()', FALSE);
 parse_ident 
-------------
 {foo,bar}
(1 row)

Note that setting strict_mode to FALSE (as done above for the function name) simply ignores any invalid characters from the point they are encountered:

postgres=# SELECT parse_ident('foo.bar!!!', FALSE);
 parse_ident 
-------------
 {foo,bar}
(1 row)

postgres=# SELECT parse_ident('foo!.bar!', FALSE);
 parse_ident 
-------------
 {foo}
(1 row)

Providing more elements than would be valid for an qualified identifier does not raise an error:

postgres=# SELECT parse_ident('foo.bar."Baz".boo.zoo');
      parse_ident      
-----------------------
 {foo,bar,Baz,boo,zoo}
(1 row)

Identifiers longer than NAMEDATALEN are accepted:

postgres=# SELECT parse_ident('foo.a2345678901234567890123456789012345678901234567890123456789012345');
                               parse_ident                               
-------------------------------------------------------------------------
 {foo,a2345678901234567890123456789012345678901234567890123456789012345}
(1 row)

These can be trimmed by casting the result array to NAME[]:

postgres=# SELECT parse_ident('foo.a2345678901234567890123456789012345678901234567890123456789012345')::NAME[];
                              parse_ident                              
-----------------------------------------------------------------------
 {foo,a23456789012345678901234567890123456789012345678901234567890123}
(1 row)

Categories

Schema (namespace), String manipulation, System function

See also

quote_ident()