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

Commitc892643

Browse files
committed
Code review for SELECT INTO STRICT patch: use saner choices of error
SQLSTATEs, fix some documentation problems.
1 parent3ba3e6c commitc892643

File tree

7 files changed

+54
-35
lines changed

7 files changed

+54
-35
lines changed

‎doc/src/sgml/errcodes.sgml

Lines changed: 13 additions & 1 deletion
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/1622:41:45 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/errcodes.sgml,v 1.20 2006/06/1623: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

Lines changed: 25 additions & 18 deletions
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 (butonly 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 INTOSTRICTmyrec * 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 canmustuse exceptionblocks to determine
1145-
the number of rows generated by the query:
1145+
(more than one row). You can useanexceptionblock 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

Lines changed: 3 additions & 1 deletion
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
#defineERRCODE_PLPGSQL_ERRORMAKE_SQLSTATE('P','0', '0','0','0')
333333
#defineERRCODE_RAISE_EXCEPTIONMAKE_SQLSTATE('P','0', '0','0','1')
334+
#defineERRCODE_NO_DATA_FOUNDMAKE_SQLSTATE('P','0', '0','0','2')
335+
#defineERRCODE_TOO_MANY_ROWSMAKE_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

Lines changed: 2 additions & 2 deletions
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 @@ staticvoid check_labels(const char *start_label,
157157
%tokenK_ELSE
158158
%tokenK_ELSIF
159159
%tokenK_END
160-
%tokenK_STRICT
161160
%tokenK_EXCEPTION
162161
%tokenK_EXECUTE
163162
%tokenK_EXIT
@@ -187,6 +186,7 @@ staticvoid check_labels(const char *start_label,
187186
%tokenK_RETURN_NEXT
188187
%tokenK_REVERSE
189188
%tokenK_SELECT
189+
%tokenK_STRICT
190190
%tokenK_THEN
191191
%tokenK_TO
192192
%tokenK_TYPE

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

Lines changed: 3 additions & 3 deletions
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/1618:42:23 tgl Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.173 2006/06/1623: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

Lines changed: 6 additions & 8 deletions
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

Lines changed: 2 additions & 2 deletions
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)
[8]ページ先頭
©2009-2025 Movatter.jp