Re: Seems to be impossible to set a NULL search_path - Mailing list pgsql-general
| From | Bryn Llewellyn |
|---|---|
| Subject | Re: Seems to be impossible to set a NULL search_path |
| Date | |
| Msg-id | 1FB02EB7-627B-4D1F-B024-AB275871517F@yugabyte.com Whole thread Raw |
| In response to | Re: Seems to be impossible to set a NULL search_path (Bryn Llewellyn <bryn@yugabyte.com>) |
| Responses |
Re: Seems to be impossible to set a NULL search_path
("David G. Johnston" <david.g.johnston@gmail.com>)
|
| List | pgsql-general |
bryn@yugabyte.com wrote:david.g.johnston@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
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?
"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%';
-- 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"
-------------
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:
«
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.
pgsql-general by date: