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

Commitca0d219

Browse files
committed
Change the row constructor syntax (ROW(...)) so that list elements foo.*
will be expanded to a list of their member fields, rather than creatinga nested rowtype field as formerly. (The old behavior is still availableby omitting '.*'.) This syntax is not allowed by the SQL spec AFAICS,so changing its behavior doesn't violate the spec. The new behavior issubstantially more useful since it allows, for example, triggers to checkfor data changes with 'if row(new.*) is distinct from row(old.*)'. Permy recent proposal.
1 parent4b98d42 commitca0d219

File tree

6 files changed

+219
-26
lines changed

6 files changed

+219
-26
lines changed

‎doc/src/sgml/syntax.sgml

Lines changed: 27 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.106 2006/03/10 19:10:49 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.107 2006/06/26 17:24:40 tgl Exp $ -->
22

33
<chapter id="sql-syntax">
44
<title>SQL Syntax</title>
@@ -1570,6 +1570,31 @@ SELECT ROW(1,2.5,'this is a test');
15701570
expression in the list.
15711571
</para>
15721572

1573+
<para>
1574+
A row constructor can include the syntax
1575+
<replaceable>rowvalue</replaceable><literal>.*</literal>,
1576+
which will be expanded to a list of the elements of the row value,
1577+
just as occurs when the <literal>.*</> syntax is used at the top level
1578+
of a <command>SELECT</> list. For example, if table <literal>t</> has
1579+
columns <literal>f1</> and <literal>f2</>, these are the same:
1580+
<programlisting>
1581+
SELECT ROW(t.*, 42) FROM t;
1582+
SELECT ROW(t.f1, t.f2, 42) FROM t;
1583+
</programlisting>
1584+
</para>
1585+
1586+
<note>
1587+
<para>
1588+
Before <productname>PostgreSQL</productname> 8.2, the
1589+
<literal>.*</literal> syntax was not expanded, so that writing
1590+
<literal>ROW(t.*, 42)</> created a two-field row whose first field
1591+
was another row value. The new behavior is usually more useful.
1592+
If you need the old behavior of nested row values, write the inner
1593+
row value without <literal>.*</literal>, for instance
1594+
<literal>ROW(t, 42)</>.
1595+
</para>
1596+
</note>
1597+
15731598
<para>
15741599
By default, the value created by a <literal>ROW</> expression is of
15751600
an anonymous record type. If necessary, it can be cast to a named
@@ -1619,7 +1644,7 @@ SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype));
16191644
<programlisting>
16201645
SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same');
16211646

1622-
SELECT ROW(a, b, c) ISNOTNULL FROM table;
1647+
SELECT ROW(table.*) IS NULL FROM table; -- detect all-null rows
16231648
</programlisting>
16241649
For more detail see <xref linkend="functions-comparisons">.
16251650
Row constructors can also be used in connection with subqueries,

‎src/backend/parser/parse_expr.c

Lines changed: 39 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/parser/parse_expr.c,v 1.192 2006/04/22 01:26:00 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/parser/parse_expr.c,v 1.193 2006/06/26 17:24:41 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -29,6 +29,7 @@
2929
#include"parser/parse_func.h"
3030
#include"parser/parse_oper.h"
3131
#include"parser/parse_relation.h"
32+
#include"parser/parse_target.h"
3233
#include"parser/parse_type.h"
3334
#include"utils/builtins.h"
3435
#include"utils/lsyscache.h"
@@ -1289,6 +1290,43 @@ transformRowExpr(ParseState *pstate, RowExpr *r)
12891290
Node*e= (Node*)lfirst(arg);
12901291
Node*newe;
12911292

1293+
/*
1294+
* Check for "something.*". Depending on the complexity of the
1295+
* "something", the star could appear as the last name in ColumnRef,
1296+
* or as the last indirection item in A_Indirection.
1297+
*/
1298+
if (IsA(e,ColumnRef))
1299+
{
1300+
ColumnRef*cref= (ColumnRef*)e;
1301+
1302+
if (strcmp(strVal(llast(cref->fields)),"*")==0)
1303+
{
1304+
/* It is something.*, expand into multiple items */
1305+
newargs=list_concat(newargs,
1306+
ExpandColumnRefStar(pstate,cref,
1307+
false));
1308+
continue;
1309+
}
1310+
}
1311+
elseif (IsA(e,A_Indirection))
1312+
{
1313+
A_Indirection*ind= (A_Indirection*)e;
1314+
Node*lastitem=llast(ind->indirection);
1315+
1316+
if (IsA(lastitem,String)&&
1317+
strcmp(strVal(lastitem),"*")==0)
1318+
{
1319+
/* It is something.*, expand into multiple items */
1320+
newargs=list_concat(newargs,
1321+
ExpandIndirectionStar(pstate,ind,
1322+
false));
1323+
continue;
1324+
}
1325+
}
1326+
1327+
/*
1328+
* Not "something.*", so transform as a single expression
1329+
*/
12921330
newe=transformExpr(pstate,e);
12931331
newargs=lappend(newargs,newe);
12941332
}

‎src/backend/parser/parse_target.c

Lines changed: 50 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/parser/parse_target.c,v 1.143 2006/06/16 18:42:22 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/parser/parse_target.c,v 1.144 2006/06/26 17:24:41 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -42,9 +42,7 @@ static Node *transformAssignmentIndirection(ParseState *pstate,
4242
ListCell*indirection,
4343
Node*rhs,
4444
intlocation);
45-
staticList*ExpandColumnRefStar(ParseState*pstate,ColumnRef*cref);
4645
staticList*ExpandAllTables(ParseState*pstate);
47-
staticList*ExpandIndirectionStar(ParseState*pstate,A_Indirection*ind);
4846
staticintFigureColnameInternal(Node*node,char**name);
4947

5048

@@ -117,7 +115,8 @@ transformTargetList(ParseState *pstate, List *targetlist)
117115
{
118116
/* It is something.*, expand into multiple items */
119117
p_target=list_concat(p_target,
120-
ExpandColumnRefStar(pstate,cref));
118+
ExpandColumnRefStar(pstate,cref,
119+
true));
121120
continue;
122121
}
123122
}
@@ -131,7 +130,8 @@ transformTargetList(ParseState *pstate, List *targetlist)
131130
{
132131
/* It is something.*, expand into multiple items */
133132
p_target=list_concat(p_target,
134-
ExpandIndirectionStar(pstate,ind));
133+
ExpandIndirectionStar(pstate,ind,
134+
true));
135135
continue;
136136
}
137137
}
@@ -696,13 +696,16 @@ checkInsertTargets(ParseState *pstate, List *cols, List **attrnos)
696696

697697
/*
698698
* ExpandColumnRefStar()
699-
*Turns foo.*(in the target list)into a list of targetlist entries.
699+
*Transforms foo.* into a list of expressions or targetlist entries.
700700
*
701701
* This handles the case where '*' appears as the last or only name in a
702-
* ColumnRef.
702+
* ColumnRef. The code is shared between the case of foo.* at the top level
703+
* in a SELECT target list (where we want TargetEntry nodes in the result)
704+
* and foo.* in a ROW() construct (where we want just bare expressions).
703705
*/
704-
staticList*
705-
ExpandColumnRefStar(ParseState*pstate,ColumnRef*cref)
706+
List*
707+
ExpandColumnRefStar(ParseState*pstate,ColumnRef*cref,
708+
booltargetlist)
706709
{
707710
List*fields=cref->fields;
708711
intnumnames=list_length(fields);
@@ -713,7 +716,12 @@ ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref)
713716
* Target item is a bare '*', expand all tables
714717
*
715718
* (e.g., SELECT * FROM emp, dept)
719+
*
720+
* Since the grammar only accepts bare '*' at top level of SELECT,
721+
* we need not handle the targetlist==false case here.
716722
*/
723+
Assert(targetlist);
724+
717725
returnExpandAllTables(pstate);
718726
}
719727
else
@@ -775,13 +783,22 @@ ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref)
775783

776784
rtindex=RTERangeTablePosn(pstate,rte,&sublevels_up);
777785

778-
returnexpandRelAttrs(pstate,rte,rtindex,sublevels_up);
786+
if (targetlist)
787+
returnexpandRelAttrs(pstate,rte,rtindex,sublevels_up);
788+
else
789+
{
790+
List*vars;
791+
792+
expandRTE(rte,rtindex,sublevels_up, false,
793+
NULL,&vars);
794+
returnvars;
795+
}
779796
}
780797
}
781798

782799
/*
783800
* ExpandAllTables()
784-
*Turns '*' (in the target list) into a list of targetlist entries.
801+
*Transforms '*' (in the target list) into a list of targetlist entries.
785802
*
786803
* tlist entries are generated for each relation appearing in the query's
787804
* varnamespace. We do not consider relnamespace because that would include
@@ -814,18 +831,22 @@ ExpandAllTables(ParseState *pstate)
814831

815832
/*
816833
* ExpandIndirectionStar()
817-
*Turns foo.*(in the target list)into a list of targetlist entries.
834+
*Transforms foo.* into a list of expressions or targetlist entries.
818835
*
819836
* This handles the case where '*' appears as the last item in A_Indirection.
837+
* The code is shared between the case of foo.* at the top level in a SELECT
838+
* target list (where we want TargetEntry nodes in the result) and foo.* in
839+
* a ROW() construct (where we want just bare expressions).
820840
*/
821-
staticList*
822-
ExpandIndirectionStar(ParseState*pstate,A_Indirection*ind)
841+
List*
842+
ExpandIndirectionStar(ParseState*pstate,A_Indirection*ind,
843+
booltargetlist)
823844
{
845+
List*result=NIL;
824846
Node*expr;
825847
TupleDesctupleDesc;
826848
intnumAttrs;
827849
inti;
828-
List*te_list=NIL;
829850

830851
/* Strip off the '*' to create a reference to the rowtype object */
831852
ind=copyObject(ind);
@@ -860,7 +881,6 @@ ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind)
860881
{
861882
Form_pg_attributeatt=tupleDesc->attrs[i];
862883
Node*fieldnode;
863-
TargetEntry*te;
864884

865885
if (att->attisdropped)
866886
continue;
@@ -893,14 +913,22 @@ ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind)
893913
fieldnode= (Node*)fselect;
894914
}
895915

896-
te=makeTargetEntry((Expr*)fieldnode,
897-
(AttrNumber)pstate->p_next_resno++,
898-
pstrdup(NameStr(att->attname)),
899-
false);
900-
te_list=lappend(te_list,te);
916+
if (targetlist)
917+
{
918+
/* add TargetEntry decoration */
919+
TargetEntry*te;
920+
921+
te=makeTargetEntry((Expr*)fieldnode,
922+
(AttrNumber)pstate->p_next_resno++,
923+
pstrdup(NameStr(att->attname)),
924+
false);
925+
result=lappend(result,te);
926+
}
927+
else
928+
result=lappend(result,fieldnode);
901929
}
902930

903-
returnte_list;
931+
returnresult;
904932
}
905933

906934
/*

‎src/include/parser/parse_target.h

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/parser/parse_target.h,v 1.39 2006/03/23 00:19:30 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/parser/parse_target.h,v 1.40 2006/06/26 17:24:41 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -22,6 +22,10 @@ extern void markTargetListOrigins(ParseState *pstate, List *targetlist);
2222
externTargetEntry*transformTargetEntry(ParseState*pstate,
2323
Node*node,Node*expr,
2424
char*colname,boolresjunk);
25+
externList*ExpandColumnRefStar(ParseState*pstate,ColumnRef*cref,
26+
booltargetlist);
27+
externList*ExpandIndirectionStar(ParseState*pstate,A_Indirection*ind,
28+
booltargetlist);
2529
externvoidupdateTargetListEntry(ParseState*pstate,TargetEntry*tle,
2630
char*colname,intattrno,
2731
List*indirection,

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

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -487,3 +487,53 @@ DROP TRIGGER show_trigger_data_trig on trigger_test;
487487

488488
DROP FUNCTION trigger_data();
489489
DROP TABLE trigger_test;
490+
--
491+
-- Test use of row comparisons on OLD/NEW
492+
--
493+
CREATE TABLE trigger_test (f1 int, f2 text, f3 text);
494+
-- this is the obvious (and wrong...) way to compare rows
495+
CREATE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$
496+
begin
497+
if row(old.*) = row(new.*) then
498+
raise notice 'row % not changed', new.f1;
499+
else
500+
raise notice 'row % changed', new.f1;
501+
end if;
502+
return new;
503+
end$$;
504+
CREATE TRIGGER t
505+
BEFORE UPDATE ON trigger_test
506+
FOR EACH ROW EXECUTE PROCEDURE mytrigger();
507+
INSERT INTO trigger_test VALUES(1, 'foo', 'bar');
508+
INSERT INTO trigger_test VALUES(2, 'baz', 'quux');
509+
UPDATE trigger_test SET f3 = 'bar';
510+
NOTICE: row 1 not changed
511+
NOTICE: row 2 changed
512+
UPDATE trigger_test SET f3 = NULL;
513+
NOTICE: row 1 changed
514+
NOTICE: row 2 changed
515+
-- this demonstrates that the above isn't really working as desired:
516+
UPDATE trigger_test SET f3 = NULL;
517+
NOTICE: row 1 changed
518+
NOTICE: row 2 changed
519+
-- the right way when considering nulls is
520+
CREATE OR REPLACE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$
521+
begin
522+
if row(old.*) is distinct from row(new.*) then
523+
raise notice 'row % changed', new.f1;
524+
else
525+
raise notice 'row % not changed', new.f1;
526+
end if;
527+
return new;
528+
end$$;
529+
UPDATE trigger_test SET f3 = 'bar';
530+
NOTICE: row 1 changed
531+
NOTICE: row 2 changed
532+
UPDATE trigger_test SET f3 = NULL;
533+
NOTICE: row 1 changed
534+
NOTICE: row 2 changed
535+
UPDATE trigger_test SET f3 = NULL;
536+
NOTICE: row 1 not changed
537+
NOTICE: row 2 not changed
538+
DROP TABLE trigger_test;
539+
DROP FUNCTION mytrigger();

‎src/test/regress/sql/triggers.sql

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -367,3 +367,51 @@ DROP TRIGGER show_trigger_data_trig on trigger_test;
367367
DROPFUNCTION trigger_data();
368368

369369
DROPTABLE trigger_test;
370+
371+
--
372+
-- Test use of row comparisons on OLD/NEW
373+
--
374+
375+
CREATETABLEtrigger_test (f1int, f2text, f3text);
376+
377+
-- this is the obvious (and wrong...) way to compare rows
378+
CREATEFUNCTIONmytrigger() RETURNS trigger LANGUAGE plpgsqlas $$
379+
begin
380+
if row(old.*)= row(new.*) then
381+
raise notice'row % not changed',new.f1;
382+
else
383+
raise notice'row % changed',new.f1;
384+
end if;
385+
return new;
386+
end$$;
387+
388+
CREATETRIGGERt
389+
BEFOREUPDATEON trigger_test
390+
FOR EACH ROW EXECUTE PROCEDURE mytrigger();
391+
392+
INSERT INTO trigger_testVALUES(1,'foo','bar');
393+
INSERT INTO trigger_testVALUES(2,'baz','quux');
394+
395+
UPDATE trigger_testSET f3='bar';
396+
UPDATE trigger_testSET f3=NULL;
397+
-- this demonstrates that the above isn't really working as desired:
398+
UPDATE trigger_testSET f3=NULL;
399+
400+
-- the right way when considering nulls is
401+
CREATE OR REPLACEFUNCTIONmytrigger() RETURNS trigger LANGUAGE plpgsqlas $$
402+
begin
403+
if row(old.*) is distinctfrom row(new.*) then
404+
raise notice'row % changed',new.f1;
405+
else
406+
raise notice'row % not changed',new.f1;
407+
end if;
408+
return new;
409+
end$$;
410+
411+
UPDATE trigger_testSET f3='bar';
412+
UPDATE trigger_testSET f3=NULL;
413+
UPDATE trigger_testSET f3=NULL;
414+
415+
DROPTABLE trigger_test;
416+
417+
DROPFUNCTION mytrigger();

0 commit comments

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