Re: [HACKERS] Creating temp tables inside read only transactions - Mailing list pgsql-general

On Mon, Jul 11, 2011 at 12:49 PM, David Johnston <polobo@yahoo.com> wrote:

> I do not see how recursive queries (really iteration of records) even enters
> the picture...

I agree, FWIW.  If the feature was that desirable, we could look at
questions of implementation to make recursion either unnecessary or at
least well managed.
>
> 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.

Right.  Semantically myapp_schemaname_subschemaname is no less
hierarchical than myapp.schemaname.subschemaname.  The larger issue is
that of potential ambiguity wrt cross-database references (I don't
have a lot of experience reading the SQL standards, but seeing how
different db's implement cross-db references suggests that the
standards contemplate semantic meaning to depth of the namespace).

>
> 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.

Agreed that this would be helpful.  I would personally have a lot of
use for this sort of feature, particularly with managing large numbers
of stored procedures.  Right now I am using a double underscore which
is error-prone.

Best Wishes,
Chris Travers