Skip to content

Commit c892643

Browse files
tglsfdctglsfdc
committed
Code review for SELECT INTO STRICT patch: use saner choices of error
SQLSTATEs, fix some documentation problems.
1 parent 3ba3e6c commit c892643

File tree

7 files changed

+54
-35
lines changed

7 files changed

+54
-35
lines changed

‎doc/src/sgml/errcodes.sgml

+13-1
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/errcodes.sgml,v 1.19 2006/06/16 22:41:45 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/errcodes.sgml,v 1.20 2006/06/16 23:29:26 tgl Exp $ -->
22

33
<appendix id="errcodes-appendix">
44
<title><productname>PostgreSQL</productname> Error Codes</title>
@@ -1344,6 +1344,18 @@
13441344
<entry>raise_exception</entry>
13451345
</row>
13461346

1347+
<row>
1348+
<entry><literal>P0002</literal></entry>
1349+
<entry>NO DATA FOUND</entry>
1350+
<entry>no_data_found</entry>
1351+
</row>
1352+
1353+
<row>
1354+
<entry><literal>P0003</literal></entry>
1355+
<entry>TOO MANY ROWS</entry>
1356+
<entry>too_many_rows</entry>
1357+
</row>
1358+
13471359

13481360
<row>
13491361
<entry spanname="span13"><emphasis role="bold">Class XX &mdash; Internal Error</></entry>

‎doc/src/sgml/plpgsql.sgml

+25-18
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.96 2006/06/15 18:02:22 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.97 2006/06/16 23:29:26 tgl Exp $ -->
22

33
<chapter id="plpgsql">
44
<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -1076,8 +1076,8 @@ tax := subtotal * 0.06;
10761076
</indexterm>
10771077

10781078
<para>
1079-
The result of a <command>SELECT</command> command yielding multiple columns (but
1080-
only one row) can be assigned to a record variable, row-type
1079+
The result of a <command>SELECT</command> command yielding multiple
1080+
columns (but only one row) can be assigned to a record variable, row-type
10811081
variable, or list of scalar variables. This is done by:
10821082

10831083
<synopsis>
@@ -1126,23 +1126,24 @@ SELECT INTO <optional>STRICT</optional> <replaceable>target</replaceable> <repla
11261126
<replaceable>target</replaceable> will be set to the first row
11271127
returned by the query, or if the query returned no rows,
11281128
null values are assigned. (Note that <quote>the first row</> is not
1129-
well-defined unless you've used <literal>ORDER BY</>.)
1130-
You can check the special <literal>FOUND</literal> variable to
1131-
determine if any rows were found:
1129+
well-defined unless you've used <literal>ORDER BY</>.) Any result rows
1130+
after the first row are discarded.
1131+
You can check the special <literal>FOUND</literal> variable (see
1132+
<xref linkend="plpgsql-statements-diagnostics">) to
1133+
determine whether a row was returned:
11321134

11331135
<programlisting>
1134-
SELECT INTO STRICT myrec * FROM emp WHERE empname = myname;
1136+
SELECT INTO myrec * FROM emp WHERE empname = myname;
11351137
IF NOT FOUND THEN
11361138
RAISE EXCEPTION 'employee % not found', myname;
11371139
END IF;
11381140
</programlisting>
11391141

1140-
<para>
1141-
If the <literal>STRICT</literal> option is specified, a query must
1142+
If the <literal>STRICT</literal> option is specified, the query must
11421143
return exactly one row or a run-time error will be thrown, either
11431144
<literal>NO_DATA_FOUND</> (no rows) or <literal>TOO_MANY_ROWS</>
1144-
(more than one row). You can must use exception blocks to determine
1145-
the number of rows generated by the query:
1145+
(more than one row). You can use an exception block if you wish
1146+
to catch the error, for example:
11461147

11471148
<programlisting>
11481149
BEGIN;
@@ -1154,11 +1155,17 @@ BEGIN;
11541155
RAISE EXCEPTION 'employee % not unique', myname;
11551156
END;
11561157
</programlisting>
1157-
Only <command>SELECT INTO STRICT</command> allows you to check if more
1158-
than one row was retrieved. <command>SELECT INTO STRICT</command>
1159-
matches Oracle's PL/SQL <command>SELECT INTO</command> behavior.
1158+
Successful execution of <command>SELECT INTO STRICT</command>
1159+
always sets <literal>FOUND</literal> to true.
11601160
</para>
11611161

1162+
<note>
1163+
<para>
1164+
<command>SELECT INTO STRICT</command> matches the behavior of
1165+
Oracle PL/SQL's <command>SELECT INTO</command> statement.
1166+
</para>
1167+
</note>
1168+
11621169
</sect2>
11631170

11641171
<sect2 id="plpgsql-statements-perform">
@@ -1987,7 +1994,7 @@ END LOOP <optional> <replaceable>label</replaceable> </optional>;
19871994
the loop. If the <literal>BY</> clause isn't specified the iteration
19881995
step is 1 otherwise it's the value specified in the <literal>BY</>
19891996
clause. If <literal>REVERSE</> is specified then the step value is
1990-
considered negative.
1997+
considered negative.
19911998
</para>
19921999

19932000
<para>
@@ -2764,7 +2771,7 @@ RAISE EXCEPTION 'Nonexistent ID --> %', user_id;
27642771
<para>
27652772
Data type <type>name</type>; the name of the table that caused the trigger
27662773
invocation. This is now deprecated, and could disappear in a future
2767-
release. Use <literal>TG_TABLE_NAME</> instead.
2774+
release. Use <literal>TG_TABLE_NAME</> instead.
27682775
</para>
27692776
</listitem>
27702777
</varlistentry>
@@ -2774,7 +2781,7 @@ RAISE EXCEPTION 'Nonexistent ID --> %', user_id;
27742781
<listitem>
27752782
<para>
27762783
Data type <type>name</type>; the name of the table that
2777-
caused the trigger invocation.
2784+
caused the trigger invocation.
27782785
</para>
27792786
</listitem>
27802787
</varlistentry>
@@ -2784,7 +2791,7 @@ RAISE EXCEPTION 'Nonexistent ID --> %', user_id;
27842791
<listitem>
27852792
<para>
27862793
Data type <type>name</type>; the name of the schema of the
2787-
table that caused the trigger invocation.
2794+
table that caused the trigger invocation.
27882795
</para>
27892796
</listitem>
27902797
</varlistentry>

‎src/include/utils/errcodes.h

+3-1
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
* Copyright (c) 2003-2006, PostgreSQL Global Development Group
1313
*
14-
* $PostgreSQL: pgsql/src/include/utils/errcodes.h,v 1.19 2006/03/05 15:59:07 momjian Exp $
14+
* $PostgreSQL: pgsql/src/include/utils/errcodes.h,v 1.20 2006/06/16 23:29:26 tgl Exp $
1515
*
1616
*-------------------------------------------------------------------------
1717
*/
@@ -331,6 +331,8 @@
331331
/* Class P0 - PL/pgSQL Error (PostgreSQL-specific error class) */
332332
#define ERRCODE_PLPGSQL_ERROR MAKE_SQLSTATE('P','0', '0','0','0')
333333
#define ERRCODE_RAISE_EXCEPTION MAKE_SQLSTATE('P','0', '0','0','1')
334+
#define ERRCODE_NO_DATA_FOUND MAKE_SQLSTATE('P','0', '0','0','2')
335+
#define ERRCODE_TOO_MANY_ROWS MAKE_SQLSTATE('P','0', '0','0','3')
334336

335337
/* Class XX - Internal Error (PostgreSQL-specific error class) */
336338
/* (this is for "can't-happen" conditions and software bugs) */

‎src/pl/plpgsql/src/gram.y

+2-2
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.92 2006/06/15 18:02:22 momjian Exp $
12+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.93 2006/06/16 23:29:26 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -157,7 +157,6 @@ static void check_labels(const char *start_label,
157157
%token K_ELSE
158158
%token K_ELSIF
159159
%token K_END
160-
%token K_STRICT
161160
%token K_EXCEPTION
162161
%token K_EXECUTE
163162
%token K_EXIT
@@ -187,6 +186,7 @@ static void check_labels(const char *start_label,
187186
%token K_RETURN_NEXT
188187
%token K_REVERSE
189188
%token K_SELECT
189+
%token K_STRICT
190190
%token K_THEN
191191
%token K_TO
192192
%token K_TYPE

‎src/pl/plpgsql/src/pl_exec.c

+3-3
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.172 2006/06/16 18:42:23 tgl Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.173 2006/06/16 23:29:27 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1721,7 +1721,7 @@ exec_stmt_select(PLpgSQL_execstate *estate, PLpgSQL_stmt_select *stmt)
17211721
{
17221722
if (stmt->strict)
17231723
ereport(ERROR,
1724-
(errcode(ERRCODE_NO_DATA),
1724+
(errcode(ERRCODE_NO_DATA_FOUND),
17251725
errmsg("query returned no rows")));
17261726

17271727
/* set the target to NULL(s) */
@@ -1732,7 +1732,7 @@ exec_stmt_select(PLpgSQL_execstate *estate, PLpgSQL_stmt_select *stmt)
17321732

17331733
if (n > 1 && stmt->strict)
17341734
ereport(ERROR,
1735-
(errcode(ERRCODE_CARDINALITY_VIOLATION),
1735+
(errcode(ERRCODE_TOO_MANY_ROWS),
17361736
errmsg("query returned more than one row")));
17371737

17381738
/*

‎src/pl/plpgsql/src/plerrcodes.h

+6-8
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
* Copyright (c) 2003-2006, PostgreSQL Global Development Group
1111
*
12-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plerrcodes.h,v 1.8 2006/06/15 18:02:22 momjian Exp $
12+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plerrcodes.h,v 1.9 2006/06/16 23:29:27 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -712,23 +712,21 @@
712712
},
713713

714714
{
715-
"internal_error", ERRCODE_INTERNAL_ERROR
715+
"no_data_found", ERRCODE_NO_DATA_FOUND
716716
},
717717

718718
{
719-
"data_corrupted", ERRCODE_DATA_CORRUPTED
719+
"too_many_rows", ERRCODE_TOO_MANY_ROWS
720720
},
721721

722722
{
723-
"index_corrupted", ERRCODE_INDEX_CORRUPTED
723+
"internal_error", ERRCODE_INTERNAL_ERROR
724724
},
725725

726726
{
727-
"no_data_found", ERRCODE_NO_DATA
727+
"data_corrupted", ERRCODE_DATA_CORRUPTED
728728
},
729729

730730
{
731-
"too_many_rows", ERRCODE_CARDINALITY_VIOLATION
731+
"index_corrupted", ERRCODE_INDEX_CORRUPTED
732732
},
733-
734-

‎src/pl/plpgsql/src/scan.l

+2-2
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/scan.l,v 1.51 2006/06/15 18:02:22 momjian Exp $
12+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/scan.l,v 1.52 2006/06/16 23:29:27 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -129,7 +129,6 @@ else { return K_ELSE; }
129129
elseif { return K_ELSIF; }
130130
elsif { return K_ELSIF; }
131131
end { return K_END; }
132-
strict { return K_STRICT; }
133132
exception { return K_EXCEPTION; }
134133
execute { return K_EXECUTE; }
135134
exit { return K_EXIT; }
@@ -158,6 +157,7 @@ return { return K_RETURN; }
158157
reverse { return K_REVERSE; }
159158
row_count { return K_ROW_COUNT; }
160159
select { return K_SELECT; }
160+
strict { return K_STRICT; }
161161
then { return K_THEN; }
162162
to { return K_TO; }
163163
type { return K_TYPE; }

0 commit comments

Comments
 (0)