I will put my support for David Johnston's proposal, in principle, though minor
details of syntax could be changed if using "!" conflicts with something. --
Darren Duncan
David Johnston wrote:
> On Mon, Jul 11, 2011 at 10:12 AM, Florian Pflug <fgp(at)phlo(dot)org> wrote:
>> On Jul11, 2011, at 07:08 , Darren Duncan wrote:
>>> Christopher Browne wrote:
>>>> Vis-a-vis the attempt to do nested naming, that is "ns1.ns2.table1",
>>>> there's a pretty good reason NOT to support that, namely that this
>>>> breaks relational handling of tables. PostgreSQL is a *relational*
>>>> database system, hence it's preferable for structures to be
>>>> relational, as opposed to hierarchical, which is what any of the
>>>> suggested nestings are.
>
>>> Rather, the argument is that "it was intentional for the structuring of
> table naming to, itself, be relational," and changing that definitely has
> some undesirable characteristics.
>
>>> The need for recursive queries is the most obvious "undesirable", but it's
> not the only undesirable thing, by any means.
>
> I do not see how recursive queries (really iteration of records) even enters
> the picture...
>
> Right now I can emulate a hierarchical schema structure via a naming scheme
> - for example "schemabase_sub1_sub2_etc". I am simply looking for a formal
> way to do the above AND also tell the system that I want all schemas under
> "schemabase" to be in the search path. Heck, I guess just allowing for
> simply pattern matching in "search_path" would be useful in this case
> regardless of the presence of an actual schema hierarchy. Using "LIKE"
> syntax say: "SET search_path TO schemabase_sub1_%" or something similar.
> The only missing ability becomes a way for graphical tools to represent the
> schema "hierarchy" using a tree-structure with multiple depths.
>
> I can see how adding "." and ".." and relative paths would confuse the issue
> those are not necessary features of a multi-level schema depth.
>
> The above, combined with a different separator for intra-level
> namespace/schema delineation, would allow for an unambiguous way to define
> and use a hierarchical schema with seemingly minimal invasion into the
> current way of doing things. You could almost implement it just by requiring
> a specific character to act as the separator and then construct the actual
> schema using single-level literals and supporting functions that can convert
> them into an hierarchy. In other words, the schema table would still only
> contain one field with the full "parent!child" as opposed to (schema,
> parent) with (VALUES('parent',null),('child','parent')).
>
> In other words, if we use "!" as the separator, any schema named
> "parent!child" could be stored and referenced as such but then if you run a
> "getChildren(parent)" function it would return "child" along with any other
> schemas of the form "parent!%". In this case the "%" sign could maybe only
> match everything except "!" and the "*" symbol could be used to match "!" as
> well.
>
> I could give more examples but I hope the basic idea is obvious. The main
> thing is that the namespace hierarchy usage is standardized in such a way
> that pgAdmin and other GUI tools can reliably use for display purposes and
> that "search_path" can be constructed in a more compact format so that every
> schema and sub-schema is still absolutely referenced (you can even have the
> SET command resolve search_path at execution time and then remain static
> just like "CREATE VIEW SELECT * FROM table".
>
> David J.
>
>
>
>