Test if a string is a valid, unquoted identifier?

Question

I'm just wondering if there is an established method for testing if a string can be used as an unquoted PostgreSQL identifier? (unquoted because almost any string can be a quoted identifier).

I ask because as shown in a previous question (How to quote qualified table name with format() function?), there are times when I would need to specify an identifier (such as the name of a table to be created) that does not yet exist, as string values (text) instead of a safer type such as regclass. Quoting the string/name can be problematic as shown there and probably else where. Without quoting, it's susceptible to SQL injection.

I guess if one programs it hard enough, a string parsing function can be written ultimately. Just wanted to check if there are existing solutions.

Is the function PARSENAME() the opposite of QUOTENAME() (sql-server)

score 0 · Accepted Answer · answered Mar 20 '18 at 01:03

Here is what I ended up using based on @a_horse_with_no_name's comment.

CREATE OR REPLACE FUNCTION is_unqualid(_ident text)
 RETURNS bool AS $func$
  SELECT format('%I',lower(_ident)) = lower(_ident);
$func$ LANGUAGE sql;

The idea is that the format function (with %I) will not add any quotes if it is unnecessary (i.e. when the input is already a simple identifier). I also normalized identifier names to lower cases using lower() before the comparison, which may or may not be desirable depending on whether the identifiers are supposed to be case insensitive or sensitive.

Sample output:

=> SELECT is_unqualid('Ab'), is_unqualid('ab'), is_unqualid('a b'), is_unqualid('a. b');
 is_unqualid | is_unqualid | is_unqualid | is_unqualid 
-------------+-------------+-------------+-------------
 t           | t           | f           | f
(1 row)

Test if a string is a valid, unquoted identifier?

1 Answers1