Thread: Nested Schemata, in a Standard-Compliant Way?

Greetings.

For some people the "what?" and "why?" of this will be immediately
obvious from the title, but I'm going to spend a little time on those
before "whether?" and "how?"

We have schemata.  They're namespaces; very convenient for organizing
things.  They let you group tables and other entities together, and,
by setting search_path, only see the ones which presently interest
you.

In fact, they're pretty similar to directories in a filesystem...
except that they don't nest.  Imagine a filesystem where you could
have directories, but the directories could only contain files, not
other directories.  (Like the first Unix on the PDP-7, or DOS before
2.0.)

You could, of course, use your own delimiters.  And we do; often along
the lines of: schema.category_subcategory_table.  You can't really use
these to establish context, however. The system doesn't recognize
category_subcategory as a "place".  So you can't easily deal with a
subset of your tables, and the combination of many tables and long
names tends to be messy.

So, for example, I'd like to be able to say something like this:

SELECT * FROM /projects/contacts/people;

Or:

cd /projects/contacts;
SELECT * FROM people;

We use / for division, so that probably isn't plausible, but it makes
for a familiar example.

I'm wondering whether such a feature could be added, without breaking
either existing code, or compliance with the SQL standard.  For
instance, borrowing :: from languages like Ruby and Perl:

SELECT * FROM ::projects::contacts::people;  -- Absolute path
cd ::projects;                               -- Session-specific
SELECT * FROM contacts::people;              -- Relative path

I'm not necessarily saying this is the best delimiter, but the colon
isn't valid in unquoted identifiers, so it's probably a choice which
would have minimal impact.

Now, you could do a fair job of this just within the client, but my
thought is that this would be better if actually supported by the
database.  For instance, having representation in the system tables.

So, then:  can it be done?  Should it be done?  I can say easily that
my database life would be better for having this, but there do tend to
be those nasty lurking problems which aren't obvious.

-- 
Ray Brinzer

Raymond Brinzer <ray.brinzer@gmail.com> writes:
> So, for example, I'd like to be able to say something like this:

> SELECT * FROM /projects/contacts/people;

I looked into this many years ago.  (The reason why pg_namespace is called
that and not pg_schema is exactly that I thought it might someday include
sub-schemas.)  I don't think it's possible to do it without huge ambiguity
problems, unless you introduce some separator other than dot, as indeed
you suggest here.  But I also don't think that using some other separator
is a good idea.  There's not that much free punctuation available (unless
you want to step outside core ASCII, which'd bring its own problems).
Pretty much every character that isn't otherwise nailed down is allowed
as an operator character, meaning that redefining it is very likely to
break somebody's application or extension.  We had huge problems even
with taking over the => digraph, never mind single characters.

In the end the functionality-versus-problems ratio is just not going
to be very good.

            regards, tom lane

On Tue, Sep 28, 2021 at 10:13 AM rob stone <floriparob@gmail.com> wrote:
> Double colons are used for casting.
> E.g., $1::INET or $1::INTEGER where $1 is a string.

Quite right; slipped my mind.  Thank you.

-- 
Ray Brinzer

On Tue, Sep 28, 2021 at 9:36 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I don't think it's possible to do it without huge ambiguity
> problems, unless you introduce some separator other than dot, as indeed
> you suggest here.

Heh... the moment I saw you'd replied, I thought, "Uh oh!"... because
I think of you as "the reality guy" here. And, sure enough, you came
with a bucket of cold water.  :-)

I haven't explored the matter thoroughly enough to give up all hope in
finding a solution which offers a decent ratio.  In the end, though,
it wouldn't surprise me at all if you were right.

Single characters are too dear.  Digraphs, maybe.  Trigraphs?  I know
it's getting ugly, but it still might be a net reduction in ugliness
for some people, which could be ignored by most.

> (The reason why pg_namespace is called
> that and not pg_schema is exactly that I
> thought it might someday include sub-schemas.)

I'd noticed the name; it's encouraging that at least people think it
*would be* a good idea.

--
Ray Brinzer