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