8000 Allow OLD and NEW in multi-row VALUES within rules. · postgrespro/postgres@092d7de · GitHub
[go: up one dir, main page]
Skip to content

Commit 092d7de

Browse files
tglsfdctglsfdc
committed
Allow OLD and NEW in multi-row VALUES within rules.
Now that we have LATERAL, it's fairly painless to allow this case, which was left as a TODO in the original multi-row VALUES implementation.
1 parent c246eb5 commit 092d7de

File tree

8 files changed

+164
-47
lines changed
  • parser
  • test/regress

    • 8 files changed

    +164
    -47
    lines changed

    ‎src/backend/optimizer/path/allpaths.c

    Lines changed: 1 addition & 3 deletions
    Original file line numberDiff line numberDiff line change
    @@ -1221,9 +1221,7 @@ set_values_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
    12211221

    12221222
    /*
    12231223
    * If it's a LATERAL RTE, it might contain some Vars of the current query
    1224-
    * level, requiring it to be treated as parameterized. (NB: even though
    1225-
    * the parser never marks VALUES RTEs as LATERAL, they could be so marked
    1226-
    * by now, as a result of subquery pullup.)
    1224+
    * level, requiring it to be treated as parameterized.
    12271225
    */
    12281226
    if (rte->lateral)
    12291227
    {

    ‎src/backend/parser/analyze.c

    Lines changed: 19 additions & 27 deletions
    Original file line numberDiff line numberDiff line change
    @@ -587,6 +587,7 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
    587587
    List *exprsLists = NIL;
    588588
    List *collations = NIL;
    589589
    int sublist_length = -1;
    590+
    bool lateral = false;
    590591
    int i;
    591592

    592593
    Assert(selectStmt->intoClause == NULL);
    @@ -647,25 +648,20 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
    647648
    collations = lappend_oid(collations, InvalidOid);
    648649

    649650
    /*
    650-
    * Another thing we can't currently support is NEW/OLD references in
    651-
    * rules --- seems we'd need something like SQL99's LATERAL construct
    652-
    * to ensure that the values would be available while evaluating the
    653-
    * VALUES RTE. This is a shame. FIXME
    651+
    * Ordinarily there can't be any current-level Vars in the expression
    652+
    * lists, because the namespace was empty ... but if we're inside
    653+
    * CREATE RULE, then NEW/OLD references might appear. In that case we
    654+
    * have to mark the VALUES RTE as LATERAL.
    654655
    */
    655656
    if (list_length(pstate->p_rtable) != 1 &&
    656657
    contain_vars_of_level((Node *) exprsLists, 0))
    657-
    ereport(ERROR,
    658-
    (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
    659-
    errmsg("VALUES must not contain OLD or NEW references"),
    660-
    errhint("Use SELECT ... UNION ALL ... instead."),
    661-
    parser_errposition(pstate,
    662-
    locate_var_of_level((Node *) exprsLists, 0))));
    658+
    lateral = true;
    663659

    664660
    /*
    665661
    * Generate the VALUES RTE
    666662
    */
    667663
    rte = addRangeTableEntryForValues(pstate, exprsLists, collations,
    668-
    NULL, true);
    664+
    NULL, lateral, true);
    669665
    rtr = makeNode(RangeTblRef);
    670666
    /* assume new rte is at end */
    671667
    rtr->rtindex = list_length(pstate->p_rtable);
    @@ -1032,6 +1028,7 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
    10321028
    List *collations;
    10331029
    List **colexprs = NULL;
    10341030
    int sublist_length = -1;
    1031+
    bool lateral = false;
    10351032
    RangeTblEntry *rte;
    10361033
    int rtindex;
    10371034
    ListCell *lc;
    @@ -1176,11 +1173,21 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
    11761173
    list_free(colexprs[i]);
    11771174
    }
    11781175

    1176+
    /*
    1177+
    * Ordinarily there can't be any current-level Vars in the expression
    1178+
    * lists, because the namespace was empty ... but if we're inside CREATE
    1179+
    * RULE, then NEW/OLD references might appear. In that case we have to
    1180+
    * mark the VALUES RTE as LATERAL.
    1181+
    */
    1182+
    if (pstate->p_rtable != NIL &&
    1183+
    contain_vars_of_level((Node *) exprsLists, 0))
    1184+
    lateral = true;
    1185+
    11791186
    /*
    11801187
    * Generate the VALUES RTE
    11811188
    */
    11821189
    rte = addRangeTableEntryForValues(pstate, exprsLists, collations,
    1183-
    NULL, true);
    1190+
    NULL, lateral, true);
    11841191
    addRTEtoQuery(pstate, rte, true, true, true);
    11851192

    11861193
    /* assume new rte is at end */
    @@ -1214,21 +1221,6 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
    12141221
    (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
    12151222
    errmsg("SELECT FOR UPDATE/SHARE cannot be applied to VALUES")));
    12161223

    1217-
    /*
    1218-
    * Another thing we can't currently support is NEW/OLD references in rules
    1219-
    * --- seems we'd need something like SQL99's LATERAL construct to ensure
    1220-
    * that the values would be available while evaluating the VALUES RTE.
    1221-
    * This is a shame. FIXME
    1222-
    */
    1223-
    if (list_length(pstate->p_rtable) != 1 &&
    1224-
    contain_vars_of_level((Node *) exprsLists, 0))
    1225-
    ereport(ERROR,
    1226-
    (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
    1227-
    errmsg("VALUES must not contain OLD or NEW references"),
    1228-
    errhint("Use SELECT ... UNION ALL ... instead."),
    1229-
    parser_errposition(pstate,
    1230-
    locate_var_of_level((Node *) exprsLists, 0))));
    1231-
    12321224
    qry->rtable = pstate->p_rtable;
    12331225
    qry->jointree = makeFromExpr(pstate->p_joinlist, NULL);
    12341226

    ‎src/backend/parser/parse_relation.c

    Lines changed: 2 additions & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -1313,6 +1313,7 @@ addRangeTableEntryForValues(ParseState *pstate,
    13131313
    List *exprs,
    13141314
    List *collations,
    13151315
    Alias *alias,
    1316+
    bool lateral,
    13161317
    bool inFromCl)
    13171318
    {
    13181319
    RangeTblEntry *rte = makeNode(RangeTblEntry);
    @@ -1355,7 +1356,7 @@ addRangeTableEntryForValues(ParseState *pstate,
    13551356
    *
    13561357
    * Subqueries are never checked for access rights.
    13571358
    */
    1358-
    rte->lateral = false;
    1359+
    rte->lateral = lateral;
    13591360
    rte->inh = false; /* never true for values RTEs */
    13601361
    rte->inFromCl = inFromCl;
    13611362

    ‎src/backend/utils/adt/ruleutils.c

    Lines changed: 42 additions & 15 deletions
    Original file line numberDiff line numberDiff line change
    @@ -2919,11 +2919,48 @@ get_select_query_def(Query *query, deparse_context *context,
    29192919
    context->windowTList = save_windowtlist;
    29202920
    }
    29212921

    2922+
    /*
    2923+
    * Detect whether query looks like SELECT ... FROM VALUES();
    2924+
    * if so, return the VALUES RTE. Otherwise return NULL.
    2925+
    */
    2926+
    static RangeTblEntry *
    2927+
    get_simple_values_rte(Query *query)
    2928+
    {
    2929+
    RangeTblEntry *result = NULL;
    2930+
    ListCell *lc;
    2931+
    2932+
    /*
    2933+
    * We want to return TRUE even if the Query also contains OLD or NEW rule
    2934+
    * RTEs. So the idea is to scan the rtable and see if there is only one
    2935+
    * inFromCl RTE that is a VALUES RTE. We don't look at the targetlist at
    2936+
    * all. This is okay because parser/analyze.c will never generate a
    2937+
    * "bare" VALUES RTE --- they only appear inside auto-generated
    2938+
    * sub-queries with very restricted structure.
    2939+
    */
    2940+
    foreach(lc, query->rtable)
    2941+
    {
    2942+
    RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);
    2943+
    2944+
    if (rte->rtekind == RTE_VALUES && rte->inFromCl)
    2945+
    {
    2946+
    if (result)
    2947+
    return NULL; /* multiple VALUES (probably not possible) */
    2948+
    result = rte;
    2949+
    }
    2950+
    else if (rte->rtekind == RTE_RELATION && !rte->inFromCl)
    2951+
    continue; /* ignore rule entries */
    2952+
    else
    2953+
    return NULL; /* something else -> not simple VALUES */
    2954+
    }
    2955+
    return result;
    2956+
    }
    2957+
    29222958
    static void
    29232959
    get_basic_select_query(Query *query, deparse_context *context,
    29242960
    TupleDesc resultDesc)
    29252961
    {
    29262962
    StringInfo buf = context->buf;
    2963+
    RangeTblEntry *values_rte;
    29272964
    char *sep;
    29282965
    ListCell *l;
    29292966

    @@ -2936,23 +2973,13 @@ get_basic_select_query(Query *query, deparse_context *context,
    29362973
    /*
    29372974
    * If the query looks like SELECT * FROM (VALUES ...), then print just the
    29382975
    * VALUES part. This reverses what transformValuesClause() did at parse
    2939-
    * time. If the jointree contains just a single VALUES RTE, we assume
    2940-
    * this case applies (without looking at the targetlist...)
    2976+
    * time.
    29412977
    */
    2942-
    if (list_length(query->jointree->fromlist) == 1)
    2978+
    values_rte = get_simple_values_rte(query);
    2979+
    if (values_rte)
    29432980
    {
    2944-
    RangeTblRef *rtr = (RangeTblRef *) linitial(query->jointree->fromlist);
    2945-
    2946-
    if (IsA(rtr, RangeTblRef))
    2947-
    {
    2948-
    RangeTblEntry *rte = rt_fetch(rtr->rtindex, query->rtable);
    2949-
    2950-
    if (rte->rtekind == RTE_VALUES)
    2951-
    {
    2952-
    get_values_def(rte->values_lists, context);
    2953-
    return;
    2954-
    }
    2955-
    }
    2981+
    get_values_def(values_rte->values_lists, context);
    2982+
    return;
    29562983
    }
    29572984

    29582985
    /*

    ‎src/include/nodes/parsenodes.h

    Lines changed: 1 addition & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -758,7 +758,7 @@ typedef struct RangeTblEntry
    758758
    */
    759759
    Alias *alias; /* user-written alias clause, if any */
    760760
    Alias *eref; /* expanded reference names */
    761-
    bool lateral; /* subquery or function is marked LATERAL? */
    761+
    bool lateral; /* subquery, function, or values is LATERAL? */
    762762
    bool inh; /* inheritance requested? */
    763763
    bool inFromCl; /* present in FROM clause? */
    764764
    AclMode requiredPerms; /* bitmask of required access permissions */

    ‎src/include/parser/parse_relation.h

    Lines changed: 1 addition & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -67,6 +67,7 @@ extern RangeTblEntry *addRangeTableEntryForValues(ParseState *pstate,
    6767
    List *exprs,
    6868
    List *collations,
    6969
    Alias *alias,
    70+
    bool lateral,
    7071
    bool inFromCl);
    7172
    extern RangeTblEntry *addRangeTableEntryForJoin(ParseState *pstate,
    7273
    List *colnames,

    ‎src/test/regress/expected/rules.out

    Lines changed: 78 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -1593,3 +1593,81 @@ select pg_get_viewdef('shoe'::regclass,0) as prettier;
    15931593
    WHERE sh.slunit = un.un_name;
    15941594
    (1 row)
    15951595

    1596+
    --
    1597+
    -- check multi-rule VALUES in rules
    1598+
    --
    1599+
    create table rules_src(f1 int, f2 int);
    1600+
    create table rules_log(f1 int, f2 int, tag text);
    1601+
    insert into rules_src values(1,2), (11,12);
    1602+
    create rule r1 as on update to rules_src do also
    1603+
    insert into rules_log values(old.*, 'old'), (new.*, 'new');
    1604+
    update rules_src set f2 = f2 + 1;
    1605+
    update rules_src set f2 = f2 * 10;
    1606+
    select * from rules_src;
    1607+
    f1 | f2
    1608+
    ----+-----
    1609+
    1 | 30
    1610+
    11 | 130
    1611+
    (2 rows)
    1612+
    1613+
    select * from rules_log;
    1614+
    f1 | f2 | tag
    1615+
    ----+-----+-----
    1616+
    1 | 2 | old
    1617+ 1023B
    1 | 3 | new
    1618+
    11 | 12 | old
    1619+
    11 | 13 | new
    1620+
    1 | 3 | old
    1621+
    1 | 30 | new
    1622+
    11 | 13 | old
    1623+
    11 | 130 | new
    1624+
    (8 rows)
    1625+
    1626+
    create rule r2 as on update to rules_src do also
    1627+
    values(old.*, 'old'), (new.*, 'new');
    1628+
    update rules_src set f2 = f2 / 10;
    1629+
    column1 | column2 | column3
    1630+
    ---------+---------+---------
    1631+
    1 | 30 | old
    1632+
    1 | 3 | new
    1633+
    11 | 130 | old
    1634+
    11 | 13 | new
    1635+
    (4 rows)
    1636+
    1637+
    select * from rules_src;
    1638+
    f1 | f2
    1639+
    ----+----
    1640+
    1 | 3
    1641+
    11 | 13
    1642+
    (2 rows)
    1643+
    1644+
    select * from rules_log;
    1645+
    f1 | f2 | tag
    1646+
    ----+-----+-----
    1647+
    1 | 2 | old
    1648+
    1 | 3 | new
    1649+
    11 | 12 | old
    1650+
    11 | 13 | new
    1651+
    1 | 3 | old
    1652+
    1 | 30 | new
    1653+
    11 | 13 | old
    1654+
    11 | 130 | new
    1655+
    1 | 30 | old
    1656+
    1 | 3 | new
    1657+
    11 | 130 | old
    1658+
    11 | 13 | new
    1659+
    (12 rows)
    1660+
    1661+
    \d+ rules_src
    1662+
    Table "public.rules_src"
    1663+
    Column | Type | Modifiers | Storage | Stats target | Description
    1664+
    --------+---------+-----------+---------+--------------+-------------
    1665+
    f1 | integer | | plain | |
    1666+
    f2 | integer | | plain | |
    1667+
    Rules:
    1668+
    r1 AS
    1669+
    ON UPDATE TO rules_src DO INSERT INTO rules_log (f1, f2, tag) VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
    1670+
    r2 AS
    1671+
    ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
    1672+
    Has OIDs: no
    1673+

    ‎src/test/regress/sql/rules.sql

    Lines changed: 20 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -933,3 +933,23 @@ select * from only t1_2;
    933933
    select pg_get_viewdef('shoe'::regclass) as unpretty;
    934934
    select pg_get_viewdef('shoe'::regclass,true) as pretty;
    935935
    select pg_get_viewdef('shoe'::regclass,0) as prettier;
    936+
    937+
    --
    938+
    -- check multi-rule VALUES in rules
    939+
    --
    940+
    941+
    create table rules_src(f1 int, f2 int);
    942+
    create table rules_log(f1 int, f2 int, tag text);
    943+
    insert into rules_src values(1,2), (11,12);
    944+
    create rule r1 as on update to rules_src do also
    945+
    insert into rules_log values(old.*, 'old'), (new.*, 'new');
    946+
    update rules_src set f2 = f2 + 1;
    947+
    update rules_src set f2 = f2 * 10;
    948+
    select * from rules_src;
    949+
    select * from rules_log;
    950+
    create rule r2 as on update to rules_src do also
    951+
    values(old.*, 'old'), (new.*, 'new');
    952+
    update rules_src set f2 = f2 / 10;
    953+
    select * from rules_src;
    954+
    select * from rules_log;
    955+
    \d+ rules_src

    0 commit comments

    Comments
     (0)
    0