Skip to content

Commit ca0d219

Browse files
tglsfdctglsfdc
committed
Change the row constructor syntax (ROW(...)) so that list elements foo.*
will be expanded to a list of their member fields, rather than creating a nested rowtype field as formerly. (The old behavior is still available by omitting '.*'.) This syntax is not allowed by the SQL spec AFAICS, so changing its behavior doesn't violate the spec. The new behavior is substantially more useful since it allows, for example, triggers to check for data changes with 'if row(new.*) is distinct from row(old.*)'. Per my recent proposal.
1 parent 4b98d42 commit ca0d219

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) IS NOT NULL 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+
else if (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
int location);
45-
static List *ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref);
4645
static List *ExpandAllTables(ParseState *pstate);
47-
static List *ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind);
4846
static int FigureColnameInternal(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-
static List *
705-
ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref)
706+
List *
707+
ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref,
708+
bool targetlist)
706709
{
707710
List *fields = cref->fields;
708711
int numnames = 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
return ExpandAllTables(pstate);
718726
}
719727
else
@@ -775,13 +783,22 @@ ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref)
775783

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

778-
return expandRelAttrs(pstate, rte, rtindex, sublevels_up);
786+
if (targetlist)
787+
return expandRelAttrs(pstate, rte, rtindex, sublevels_up);
788+
else
789+
{
790+
List *vars;
791+
792+
expandRTE(rte, rtindex, sublevels_up, false,
793+
NULL, &vars);
794+
return vars;
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-
static List *
822-
ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind)
841+
List *
842+
ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind,
843+
bool targetlist)
823844
{
845+
List *result = NIL;
824846
Node *expr;
825847
TupleDesc tupleDesc;
826848
int numAttrs;
827849
int i;
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_attribute att = 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-
return te_list;
931+
return result;
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
extern TargetEntry *transformTargetEntry(ParseState *pstate,
2323
Node *node, Node *expr,
2424
char *colname, bool resjunk);
25+
extern List *ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref,
26+
bool targetlist);
27+
extern List *ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind,
28+
bool targetlist);
2529
extern void updateTargetListEntry(ParseState *pstate, TargetEntry *tle,
2630
char *colname, int attrno,
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
DROP FUNCTION trigger_data();
368368

369369
DROP TABLE trigger_test;
370+
371+
--
372+
-- Test use of row comparisons on OLD/NEW
373+
--
374+
375+
CREATE TABLE trigger_test (f1 int, f2 text, f3 text);
376+
377+
-- this is the obvious (and wrong...) way to compare rows
378+
CREATE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$
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+
CREATE TRIGGER t
389+
BEFORE UPDATE ON trigger_test
390+
FOR EACH ROW EXECUTE PROCEDURE mytrigger();
391+
392+
INSERT INTO trigger_test VALUES(1, 'foo', 'bar');
393+
INSERT INTO trigger_test VALUES(2, 'baz', 'quux');
394+
395+
UPDATE trigger_test SET f3 = 'bar';
396+
UPDATE trigger_test SET f3 = NULL;
397+
-- this demonstrates that the above isn't really working as desired:
398+
UPDATE trigger_test SET f3 = NULL;
399+
400+
-- the right way when considering nulls is
401+
CREATE OR REPLACE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$
402+
begin
403+
if row(old.*) is distinct from 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_test SET f3 = 'bar';
412+
UPDATE trigger_test SET f3 = NULL;
413+
UPDATE trigger_test SET f3 = NULL;
414+
415+
DROP TABLE trigger_test;
416+
417+
DROP FUNCTION mytrigger();

0 commit comments

Comments
 (0)