> Hello, > > I'm building a DSL in Clojure for SQL and specifically PostgreSQL > [1]. When building a SQL statement that contains a function call > I always quote the function name with \" in case the function > name contains any special characters. Here's an example: > > (select db ['(upper "x")]) > ;=> ["SELECT \"upper\"(?)" "x"] > > This worked fine so far, but today I found a case that doesn't > work as expected, the COALESCE function. > > (select db ['(coalesce nil 0)]) > ;=> ["SELECT \"coalesce\"(NULL, 0)"] > > Can someone explain to me what's the difference between quoting > the `upper` and the `coalesce` function? I can execute the > following statements via psql, and it works as expected: > > SELECT upper ('x'); > SELECT "upper"('x'); > SELECT coalesce(NULL, 1); > > But as soon as I try this with `coalesce` I get an error: > > SELECT "coalesce"(NULL, 1);
While not a precise answer to your question, it may be of interest to note that coalesce is *not* a function.
It is a language construct with a function-like syntax.
select distinct proname from pg_proc where proname in ('coalesce', 'lower'); proname --------- lower (1 row)
> > ERROR: function coalesce(unknown, integer) does not exist > LINE 1: SELECT "coalesce"(NULL, 1); > ^ > HINT: No function matches the given name and argument types. You might need to add explicit type casts. > > What I found so far is, that the `upper` function can be found in > the `pg_proc` table but not `coalesce`. > > SELECT proname FROM pg_proc WHERE proname ILIKE 'upper'; > SELECT proname FROM pg_proc WHERE proname ILIKE 'coalesce'; > > Does this mean that `coalesce` isn't a classical function and I > shouldn't quote it? Is it instead a keyword, as described in > the "Lexical Structure" section of the docs [2]? How can I find > out which other functions are not meant to be quoted? > > I'm aware that I do not need to quote the `coalesce` and `upper` > functions and I may change my strategy for quoting functions names. > > Thanks for you help, Roman. > > [1] https://github.com/r0man/sqlingvo > [2] http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html >