Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit092d7de

Browse files
committed
Allow OLD and NEW in multi-row VALUES within rules.
Now that we have LATERAL, it's fairly painless to allow this case, whichwas left as a TODO in the original multi-row VALUES implementation.
1 parentc246eb5 commit092d7de

File tree

8 files changed

+164
-47
lines changed

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
intsublist_length=-1;
590+
boollateral= false;
590591
inti;
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 wecan'tcurrently 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 therecan'tbe 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
intsublist_length=-1;
1031+
boollateral= false;
10351032
RangeTblEntry*rte;
10361033
intrtindex;
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+
boollateral,
13161317
boolinFromCl)
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+
staticRangeTblEntry*
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+
returnNULL;/* multiple VALUES (probably not possible) */
2948+
result=rte;
2949+
}
2950+
elseif (rte->rtekind==RTE_RELATION&& !rte->inFromCl)
2951+
continue;/* ignore rule entries */
2952+
else
2953+
returnNULL;/* something else -> not simple VALUES */
2954+
}
2955+
returnresult;
2956+
}
2957+
29222958
staticvoid
29232959
get_basic_select_query(Query*query,deparse_context*context,
29242960
TupleDescresultDesc)
29252961
{
29262962
StringInfobuf=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-
boollateral;/* subqueryorfunction is marked LATERAL? */
761+
boollateral;/* subquery, function,orvalues is LATERAL? */
762762
boolinh;/* inheritance requested? */
763763
boolinFromCl;/* present in FROM clause? */
764764
AclModerequiredPerms;/* 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+
boollateral,
7071
boolinFromCl);
7172
externRangeTblEntry*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+
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+
createtablerules_src(f1int, f2int);
942+
createtablerules_log(f1int, f2int, tagtext);
943+
insert into rules_srcvalues(1,2), (11,12);
944+
createruler1asonupdate to rules_src do also
945+
insert into rules_logvalues(old.*,'old'), (new.*,'new');
946+
update rules_srcset f2= f2+1;
947+
update rules_srcset f2= f2*10;
948+
select*from rules_src;
949+
select*from rules_log;
950+
createruler2asonupdate to rules_src do also
951+
values(old.*,'old'), (new.*,'new');
952+
update rules_srcset f2= f2/10;
953+
select*from rules_src;
954+
select*from rules_log;
955+
\d+ rules_src

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp