ltree

README

pg/ltree

The ltree extension for sqlb. Needs CREATE EXTENSION ltree and, for the operators to use an index, CREATE INDEX ... USING gist (path).

SQL	Helper
Column type	Path, text on the wire
$n::ltree, a bound path with the cast	Lit
a @> b, a <@ b	AncestorOf, DescendantOf
path ~ lquery	MatchesLQuery
path @ ltxtquery	MatchesTxtQuery
nlevel, subpath, subltree, index, lca	NLevel, Subpath, Subltree, Index, LCA
a || b	Concat

Descendants of a node, ordered by depth:

sel.Columns(sqlb.Bind(CatID, &id), sqlb.Bind(CatPath, &path)).
	From(Categories).
	Where(ltree.DescendantOf(CatPath, ltree.Lit("top.science"))).
	Order(sqlb.OrderBy(ltree.NLevel(CatPath), sqlb.SortAsc))

The same query as a recursive CTE needs no extension, so the choice is between an index that answers subtree questions in one scan and a portable shape; see the snippets package, sections 1 and 20.6, for both.

Documentation

Overview

Package ltree is the ltree extension: a materialized-path label tree with its own operators. Needs CREATE EXTENSION ltree; the @> and ~ operators are served by a GiST index with gist_ltree_ops.

Index

• Variables
• func AncestorOf(a, b sqlb.Expr[Path]) sqlb.Predicate
• func Concat(a, b sqlb.Expr[Path]) sqlb.Expr[Path]
• func DescendantOf(a, b sqlb.Expr[Path]) sqlb.Predicate
• func Index(p sqlb.Expr[Path], sub string) sqlb.Expr[int32]
• func LCA(paths ...sqlb.Expr[Path]) sqlb.Expr[Path]
• func Lit(path string) sqlb.Expr[Path]
• func MatchesLQuery(path sqlb.Expr[Path], lquery string) sqlb.Predicate
• func MatchesTxtQuery(path sqlb.Expr[Path], q string) sqlb.Predicate
• func NLevel(p sqlb.Expr[Path]) sqlb.Expr[int32]
• func Subltree(p sqlb.Expr[Path], start, end int) sqlb.Expr[Path]
• func Subpath(p sqlb.Expr[Path], offset, length int) sqlb.Expr[Path]
• type Path

Variables

var Extension = pg.Extension{Name: "ltree"}

Extension is the ltree extension this package's constructs need, for pg.CreateExtension(Extension), so a library installs what it uses without spelling its name.

var GistOps = pg.NewOpClass[Path]("gist_ltree_ops")

GistOps is ltree's operator class, which serves the ancestor and descendant operators; pg.GiST is its method.

Functions

func AncestorOf

func AncestorOf(a, b sqlb.Expr[Path]) sqlb.Predicate

AncestorOf renders a @> b: a is an ancestor of b, or equal.

func Concat

func Concat(a, b sqlb.Expr[Path]) sqlb.Expr[Path]

Concat renders a || b for two paths.

func DescendantOf

func DescendantOf(a, b sqlb.Expr[Path]) sqlb.Predicate

DescendantOf renders a <@ b: a is below b, or equal.

func Index

func Index(p sqlb.Expr[Path], sub string) sqlb.Expr[int32]

Index renders index(p, $n::ltree): the position of sub in p, or -1.

func LCA

func LCA(paths ...sqlb.Expr[Path]) sqlb.Expr[Path]

LCA renders lca(p...): the longest common ancestor.

func Lit

func Lit(path string) sqlb.Expr[Path]

Lit renders a bound path with an explicit cast, $n::ltree, so an operator between a column and the parameter resolves without ambiguity.

func MatchesLQuery

func MatchesLQuery(path sqlb.Expr[Path], lquery string) sqlb.Predicate

MatchesLQuery renders path ~ $n::lquery, the pattern "*.science.*{1,}".

func MatchesTxtQuery

func MatchesTxtQuery(path sqlb.Expr[Path], q string) sqlb.Predicate

MatchesTxtQuery renders path @ $n::ltxtquery, the boolean label search "science & !astronomy".

func NLevel

func NLevel(p sqlb.Expr[Path]) sqlb.Expr[int32]

NLevel renders nlevel(p), the number of labels in the path. It is the depth of the node, so it orders a subtree by level.

func Subltree

func Subltree(p sqlb.Expr[Path], start, end int) sqlb.Expr[Path]

Subltree renders subltree(p, start, end): the labels from start up to but not including end. Subpath takes a count where this takes an end.

func Subpath

func Subpath(p sqlb.Expr[Path], offset, length int) sqlb.Expr[Path]

Subpath renders subpath(p, offset, len): len labels from offset, 0-based. A negative offset counts from the end.

Types

type Path

type Path string

Path is an ltree value, "top.science.astronomy". It scans and values as text, which is ltree's I/O form on every driver.