Hi
2016-03-29 10:30 GMT+02:00 Roman Scherer <roman@xxxxxxxxxxxxxxxx>:
Tom, Jerry, I'm going to do the same as the `quote_identifier`function of Postgres does, only quote if necessary.Thanks for your explanation, Roman.
The coalesce is one few functions implemented by special rule in PostgreSQL parser. Some functions with special behave, special syntax are implemented differently than other functions:coalesce, xmlelement, least, greatest, current_timestamp, session_user. When you use "coalesce", then PostgreSQL try to search custom function named coalesce. These functions are not usually in pg_proc catalogue.
see https://github.com/postgres/postgres/blob/master/src/backend/parser/gram.y func_expr_common_subexpr
Regards
Pavel
On Tue, Mar 29, 2016 at 1:31 AM, Jerry Sievers <gsievers19@xxxxxxxxxxx> wrote:Roman Scherer <roman@xxxxxxxxxxxxxxxx> writes:
> 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
>
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@xxxxxxxxxxx
p: 312.241.7800
