How to quote the COALESCE function?

From: Roman Scherer <roman(at)burningswell(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How to quote the COALESCE function?
Date: 2016-03-28 11:36:19
Message-ID: CAEc_D28KMLXTKQooqnWm8YbQoDkbf4ixZh=n6fqt6h_cVjxcyg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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);

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jehan-Guillaume de Rorthais 2016-03-28 13:17:44 Re: Unique values on multiple tables
Previous Message Emre Hasegeli 2016-03-28 10:44:51 Re: Unique values on multiple tables