Re: Seems to be impossible to set a NULL search_path

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

bryn@xxxxxxxxxxxx wrote:

david.g.johnston@xxxxxxxxx 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

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.