> b...@yugabyte.com wrote:
>
>> david.g.johns...@gmail.com <mailto:david.g.johns...@gmail.com> wrote:
>>
>> As for "schema identifiers" vs. "schema names" - they both seem equally
>> wrong. The list can very much contain sequences of characters that when
>> interpreted as an identifier and looked for in the pg_namespace catalog do
>> not find a matching entry and are therefore by definition not the name of
>> any known schema in that database.
>>
>> Besides, I hazard to guess how many times we write "table name" and "column
>> name" in the documentation when your argument is that "table identifier" and
>> "column identifier" is the correct choice. No, rather "name" and
>> "identifier" in the context of database objects are known to mean the same
>> thing - the alphabetic name of the object.
>
> Well, "putative" or "candidate" can be used to resolve your existence
> criterion. But why bother? In my book, Bertie Wooster (or Bertram Wilberforce
> Wooster if you prefer) is a perfectly fine candidate name in the general
> English speaking culture. It's neither here nor there if there happens to be
> any living person who has the name...
>
> But never mind. If you'd like a diverting read on this topic, go here:
>
> https://blogs.oracle.com/sql/post/a-collection-of-plsql-essays
> <https://blogs.oracle.com/sql/post/a-collection-of-plsql-essays>
>
> look for this, and download the PDF:
>
> «
> Names vs identifiers
>
> Databases are full of things: tables, sequences, columns, views, PL/SQL
> units, what have you. Things have names and are manipulated by mentioning the
> names. The programming languages SQL and PL/SQL use identifiers, not names.
> Questions show many programmers are confused about the difference. This note
> describes the relationships between things, names, and identifiers. Once the
> programming rules are absorbed, developers can write code faster and with
> less heartburn.
> »
>
> It's written by a former colleague with whom I spent many happy hours
> discussing the topic.
>
> Over and out?
Something has been nagging me since I sent this. I was sure that I’d seen this:
"Bllewell"
in some catalog view where I expected the name of a role. I just found it in
pg_proc. The spelling Bllewell with no quotes is the name of an o/s user on my
MacBook. I don't know why it ends up in my MacBook's PG installation—but that's
a story for a different day. My PG cluster has a database called demo. I just
did this test:
\c demo postgres
-- What a pain to have to do this to get silent "if exists" behavior.
do $body$
begin
begin
drop owned by "Exotic Me" cascade;
exception when undefined_object then
null;
end;
begin
drop owned by """Exotic Me""" cascade;
exception when undefined_object then
null;
end;
end;
$body$;
create role "Exotic Me" login password 'p';
grant connect on database demo to "Exotic Me";
create role """Exotic Me""" login password 'p';
\c demo "Exotic Me"
create schema s;
create procedure s.p()
language plpgsql
as $body$
begin
null;
end;
$body$;
with c as (
select
rolname as role_name
from pg_roles
union all
select
distinct proowner::regrole::text as role_name
from pg_proc)
select role_name
from c
where
lower(role_name) like '%bllewell%' or
lower(role_name) like '%exotic%';
This is the result:
role_name
-------------
Bllewell
Exotic Me
"Exotic Me"
"Bllewell"
Of course I understand why I see both Exotic Me with no quotes and "Exotic Me"
with double quotes: I asked for it. But why do I see both Bllewell with no
quotes and "Bllewell" with double quotes. Are there really two distinct roles
with those two names? Or did pg_roles end up with the identifier for the exotic
name Bllewell rather than the bare name itself?
And on the disputed notion that the identifier for a name is a distinct
phenomenon from the name itself, I noted this here:
https://www.postgresql.org/docs/current/datatype-oid.html
«
The input functions for these types allow whitespace between tokens, and will
fold upper-case letters to lower case, except within double quotes; this is
done to make the syntax rules similar to the way object names are written in
SQL. Conversely, the output functions will use double quotes if needed to make
the output be a valid SQL identifier.
»
It comes close (but no cigar) to making the distinction. It uses both "object
name" and "SQL identifier" and says (more or less) that a "SQL identifier" is
the way an object name is written in SQL by double-quoting it. I'm using
"exotic" as a tautological shorthand for what you *must* surround with
double-quotes in SQL and PL/pgSQL to express what you want.
