How to quote the COALESCE function?

Roman Scherer <roman@burningswell.com> writes:

Can someone explain to me what's the difference between quoting
the `upper` and the `coalesce` function?

COALESCE is a keyword.

What I found so far is, that the `upper` function can be found in
the `pg_proc` table but not `coalesce`.

Yup.

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?

Yes, yes, and you already found one good way: if it doesn't have
a pg_proc entry then it's a special case of some sort or other.

Have you considered only quoting the function name if it actually
needs it, ie, contains special characters?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 <gsievers19@comcast.net>
wrote:

Hi

2016-03-29 10:30 GMT+02:00 Roman Scherer <roman@burningswell.com>:

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 12:38 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

The coalesce is one few functions implemented by special rule in
PostgreSQL parser.

In the SQL standard the COALESCE feature is not listed as a
function; it is listed as one of the short forms of CASE
expression. While it has function-like syntax, thinking of it as a
function is semantically incorrect.

COALESCE(a, b)

is supposed to be semantically equivalent to:

CASE WHEN a is not null THEN a ELSE b END

Among other things, that means that this statement should not
generate a divide by zero error:

SELECT COALESCE(1, 1/0);

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general