Contents
parse_ident()
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
- PostgreSQL 9.6
- added (commit 3187d6de)
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)
References
- PostgreSQL documentation: Other String Functions