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)

Let's suppose such a function existed. If you give it foo.bar, how it's supposed to know whether you mean table bar in schema foo, in which case it doesn't need to be quoted, or you mean table foo.bar in the current schema, in which case it must be quoted? — Daniel Vérité, Mar 17 '18 at 12:38
@DanielVérité foo.bar would be invalid as an unquoted ident, as it contains a dot ., wouldn't it? The answer here (https://dba.stackexchange.com/a/45591/55439) mentions only underscore and dollar sign as acceptable non-letter characters. — tinlyx, Mar 17 '18 at 17:28
I thought you wanted to accept schema.table just like the SQL parser does, as your related question says qualified table name with format(). If not, the suggestion by @a_horse_with_no_name seems to answer the question. — Daniel Vérité, Mar 17 '18 at 19:22
@a_horse_with_no_name Thanks for your comment. If you convert it to an answer, I'll accept it. — tinlyx, Mar 17 '18 at 19:26

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