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

[Kevin Grittner]

On Tue, Mar 29, 2016 at 12:38 PM, Pavel Stehule  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

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

[Pavel Stehule]

Hi

2016-03-29 10:30 GMT+02:00 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.
>

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

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
>

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

[Jerry Sievers]

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


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

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

[Tom Lane]

Roman Scherer  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