PostgreSQL如何实现上拉子链接

57次阅读

共计 3829 个字符，预计需要花费 10 分钟才能阅读完成。

这篇文章主要介绍了 PostgreSQL 如何实现上拉子链接，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。

按官方文档的介绍, 子链接 Sublink 代表的是出现在表达式 (可能会出现组合运算符) 中的子查询, 子查询的类型包括:
EXISTS_SUBLINK
语法:EXISTS(SELECT …)

select * 
from t_dwxx a
where exists (select b.dwbh from t_grxx b where a.dwbh = b.dwbh);

ALL_SUBLINK
语法:(lefthand) op ALL (SELECT …)

select * 
from t_dwxx a
where dwbh   all (select b.dwbh from t_grxx b);

ANY_SUBLINK
语法:(lefthand) op ANY (SELECT …)

select * 
from t_dwxx a
where dwbh = any (select b.dwbh from t_grxx b);

ROWCOMPARE_SUBLINK
语法:(lefthand) op (SELECT …)

select * 
from t_dwxx a
where dwbh   (select max(b.dwbh) from t_grxx b);

EXPR_SUBLINK
语法:(SELECT with single targetlist item …)

select *,(select max(dwbh) from t_grxx) 
from t_dwxx a;

MULTIEXPR_SUBLINK
语法:(SELECT with multiple targetlist items …)

ARRAY_SUBLINK
语法:ARRAY(SELECT with single targetlist item …)

CTE_SUBLINK
语法:
WITH query (never actually part of an expression)

官方说明:

/*
* SubLink
*
* A SubLink represents a subselect appearing in an expression, and in some
* cases also the combining operator(s) just above it. The subLinkType
* indicates the form of the expression represented:
* EXISTS_SUBLINK EXISTS(SELECT ...)
* ALL_SUBLINK (lefthand) op ALL (SELECT ...)
* ANY_SUBLINK (lefthand) op ANY (SELECT ...)
* ROWCOMPARE_SUBLINK (lefthand) op (SELECT ...)
* EXPR_SUBLINK (SELECT with single targetlist item ...)
* MULTIEXPR_SUBLINK (SELECT with multiple targetlist items ...)
* ARRAY_SUBLINK ARRAY(SELECT with single targetlist item ...)
* CTE_SUBLINK WITH query (never actually part of an expression)
* For ALL, ANY, and ROWCOMPARE, the lefthand is a list of expressions of the
* same length as the subselect s targetlist. ROWCOMPARE will *always* have
* a list with more than one entry; if the subselect has just one target
* then the parser will create an EXPR_SUBLINK instead (and any operator
* above the subselect will be represented separately).
* ROWCOMPARE, EXPR, and MULTIEXPR require the subselect to deliver at most
* one row (if it returns no rows, the result is NULL).
* ALL, ANY, and ROWCOMPARE require the combining operators to deliver boolean
* results. ALL and ANY combine the per-row results using AND and OR
* semantics respectively.
* ARRAY requires just one target column, and creates an array of the target
* column s type using any number of rows resulting from the subselect.
*
* 子链接属于 Expr Node, 但并不意味着子链接是可以执行的. 子链接必须在计划期间通过子计划节点在表达式树中替换
* SubLink is classed as an Expr node, but it is not actually executable;
* it must be replaced in the expression tree by a SubPlan node during
* planning.
*
* NOTE: in the raw output of gram.y, testexpr contains just the raw form
* of the lefthand expression (if any), and operName is the String name of
* the combining operator. Also, subselect is a raw parsetree. During parse
* analysis, the parser transforms testexpr into a complete boolean expression
* that compares the lefthand value(s) to PARAM_SUBLINK nodes representing the
* output columns of the subselect. And subselect is transformed to a Query.
* This is the representation seen in saved rules and in the rewriter.
*
* In EXISTS, EXPR, MULTIEXPR, and ARRAY SubLinks, testexpr and operName
* are unused and are always null.
*
* subLinkId is currently used only for MULTIEXPR SubLinks, and is zero in
* other SubLinks. This number identifies different multiple-assignment
* subqueries within an UPDATE statement s SET list. It is unique only
* within a particular targetlist. The output column(s) of the MULTIEXPR
* are referenced by PARAM_MULTIEXPR Params appearing elsewhere in the tlist.
*
* The CTE_SUBLINK case never occurs in actual SubLink nodes, but it is used
* in SubPlans generated for WITH subqueries.
*/
typedef enum SubLinkType
EXISTS_SUBLINK,
ALL_SUBLINK,
ANY_SUBLINK,
ROWCOMPARE_SUBLINK,
EXPR_SUBLINK,
MULTIEXPR_SUBLINK,
ARRAY_SUBLINK,
CTE_SUBLINK /* for SubPlans only */
} SubLinkType;

正文完