[GENERAL] How to quote the COALESCE function?

[Roman Scherer]

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

Re: [GENERAL] How to quote the COALESCE function?

[Roman Scherer]

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.


On Tue, Mar 29, 2016 at 1:31 AM, Jerry Sievers 
wrote:

> Roman Scherer  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.consult...@comcast.net
> p: 312.241.7800
>