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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
