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

Commita584c12

Browse files
committed
Add STRICT to PL/pgSQL SELECT INTO, so exceptions are thrown if more or
less than one row is returned by the SELECT, for Oracle PL/SQLcompatibility.Improve SELECT INTO documentation.Matt Miller
1 parenteb5558b commita584c12

File tree

6 files changed

+78
-44
lines changed

6 files changed

+78
-44
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 29 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.95 2006/06/12 16:45:30 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.96 2006/06/15 18:02:22 momjian Exp $ -->
22

33
<chapter id="plpgsql">
44
<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -1081,7 +1081,7 @@ tax := subtotal * 0.06;
10811081
variable, or list of scalar variables. This is done by:
10821082

10831083
<synopsis>
1084-
SELECT INTO <replaceable>target</replaceable> <replaceable>select_expressions</replaceable> FROM ...;
1084+
SELECT INTO <optional>STRICT</optional> <replaceable>target</replaceable> <replaceable>select_expressions</replaceable> FROM ...;
10851085
</synopsis>
10861086

10871087
where <replaceable>target</replaceable> can be a record variable, a row
@@ -1122,47 +1122,43 @@ SELECT INTO <replaceable>target</replaceable> <replaceable>select_expressions</r
11221122
</para>
11231123

11241124
<para>
1125-
If the query returns zero rows, null values are assigned to the
1126-
target(s). If the query returns multiple rows, the first
1127-
row is assigned to the target(s) and the rest are discarded.
1128-
(Note that <quote>the first row</> is not well-defined unless you've
1129-
used <literal>ORDER BY</>.)
1130-
</para>
1131-
1132-
<para>
1133-
You can check the special <literal>FOUND</literal> variable (see
1134-
<xref linkend="plpgsql-statements-diagnostics">) after a
1135-
<command>SELECT INTO</command> statement to determine whether the
1136-
assignment was successful, that is, at least one row was was returned by
1137-
the query. For example:
1125+
If <literal>STRICT</literal> is not specified then
1126+
<replaceable>target</replaceable> will be set to the first row
1127+
returned by the query, or if the query returned no rows,
1128+
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:
11381132

11391133
<programlisting>
1140-
SELECT INTO myrec * FROM emp WHERE empname = myname;
1134+
SELECT INTOSTRICTmyrec * FROM emp WHERE empname = myname;
11411135
IF NOT FOUND THEN
11421136
RAISE EXCEPTION 'employee % not found', myname;
11431137
END IF;
11441138
</programlisting>
1145-
</para>
11461139

11471140
<para>
1148-
To test for whether a record/row result is null, you can use the
1149-
<literal>IS NULL</literal> conditional. There is, however, no
1150-
way to tell whether any additional rows might have been
1151-
discarded. Here is an example that handles the case where no
1152-
rows have been returned:
1153-
<programlisting>
1154-
DECLARE
1155-
users_rec RECORD;
1156-
BEGIN
1157-
SELECT INTO users_rec * FROM users WHERE user_id=3;
1141+
If the <literal>STRICT</literal> option is specified, a query must
1142+
return exactly one row or a run-time error will be thrown, either
1143+
<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:
11581146

1159-
IF users_rec.homepage IS NULL THEN
1160-
-- user entered no homepage, return "http://"
1161-
RETURN 'http://';
1162-
END IF;
1147+
<programlisting>
1148+
BEGIN;
1149+
SELECT INTO STRICT myrec * FROM emp WHERE empname = myname;
1150+
EXCEPTION
1151+
WHEN NO_DATA_FOUND THEN
1152+
RAISE EXCEPTION 'employee % not found', myname;
1153+
WHEN TOO_MANY_ROWS THEN
1154+
RAISE EXCEPTION 'employee % not unique', myname;
11631155
END;
11641156
</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.
11651160
</para>
1161+
11661162
</sect2>
11671163

11681164
<sect2 id="plpgsql-statements-perform">
@@ -1424,8 +1420,8 @@ GET DIAGNOSTICS integer_var = ROW_COUNT;
14241420
<itemizedlist>
14251421
<listitem>
14261422
<para>
1427-
A <command>SELECT INTO</command> statement sets
1428-
<literal>FOUND</literal> true ifit returnsa row, false if no
1423+
A <command>SELECT INTO</command> statement sets
1424+
<literal>FOUND</literal> true if a row is assigned, false if no
14291425
row is returned.
14301426
</para>
14311427
</listitem>

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

Lines changed: 9 additions & 1 deletion
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.91 2006/06/12 16:45:30 momjian Exp $
12+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.92 2006/06/15 18:02:22 momjian Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -157,6 +157,7 @@ staticvoid check_labels(const char *start_label,
157157
%tokenK_ELSE
158158
%tokenK_ELSIF
159159
%tokenK_END
160+
%tokenK_STRICT
160161
%tokenK_EXCEPTION
161162
%tokenK_EXECUTE
162163
%tokenK_EXIT
@@ -2001,6 +2002,7 @@ make_select_stmt(void)
20012002
PLpgSQL_rec*rec =NULL;
20022003
inttok;
20032004
boolhave_into =false;
2005+
boolhave_strict =false;
20042006

20052007
plpgsql_dstring_init(&ds);
20062008
plpgsql_dstring_append(&ds,"SELECT");
@@ -2028,6 +2030,11 @@ make_select_stmt(void)
20282030
errmsg("INTO specified more than once")));
20292031
}
20302032
tok =yylex();
2033+
if (tok == K_STRICT)
2034+
{
2035+
have_strict =true;
2036+
tok =yylex();
2037+
}
20312038
switch (tok)
20322039
{
20332040
case T_ROW:
@@ -2108,6 +2115,7 @@ make_select_stmt(void)
21082115
select->rec = rec;
21092116
select->row = row;
21102117
select->query = expr;
2118+
select->strict = have_strict;
21112119

21122120
return (PLpgSQL_stmt *)select;
21132121
}

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

Lines changed: 25 additions & 7 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.170 2006/06/12 16:45:30 momjian Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.171 2006/06/15 18:02:22 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1701,23 +1701,41 @@ exec_stmt_select(PLpgSQL_execstate *estate, PLpgSQL_stmt_select *stmt)
17011701

17021702
/*
17031703
* Run the query
1704+
* Retrieving two rows can be slower than a single row, e.g.
1705+
* a sequential scan where the scan has to be completed to
1706+
* check for a second row. For this reason, we only do the
1707+
* second-line check for STRICT.
17041708
*/
1705-
exec_run_select(estate,stmt->query,1,NULL);
1709+
exec_run_select(estate,stmt->query,stmt->strict ?2 :1,NULL);
17061710
tuptab=estate->eval_tuptable;
17071711
n=estate->eval_processed;
17081712

17091713
/*
1710-
* If the query didn't return any rows, set the target to NULL and return.
1714+
* If SELECT ... INTO specified STRICT, and the query didn't
1715+
* find exactly one row, throw an error. If STRICT was not specified,
1716+
* then allow the query to find any number of rows.
17111717
*/
17121718
if (n==0)
17131719
{
1714-
exec_move_row(estate,rec,row,NULL,tuptab->tupdesc);
1715-
exec_eval_cleanup(estate);
1716-
returnPLPGSQL_RC_OK;
1720+
if (!stmt->strict)
1721+
{
1722+
/* null the target */
1723+
exec_move_row(estate,rec,row,NULL,tuptab->tupdesc);
1724+
exec_eval_cleanup(estate);
1725+
returnPLPGSQL_RC_OK;
1726+
}
1727+
else
1728+
ereport(ERROR,
1729+
(errcode(ERRCODE_NO_DATA),
1730+
errmsg("query returned no rows")));
17171731
}
1732+
elseif (n>1&&stmt->strict)
1733+
ereport(ERROR,
1734+
(errcode(ERRCODE_CARDINALITY_VIOLATION),
1735+
errmsg("query more than one row")));
17181736

17191737
/*
1720-
* Put the result into the target and set found to true
1738+
* Put thefirstresult into the target and set found to true
17211739
*/
17221740
exec_move_row(estate,rec,row,tuptab->vals[0],tuptab->tupdesc);
17231741
exec_set_found(estate, true);

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

Lines changed: 11 additions & 1 deletion
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.7 2006/03/05 15:59:10 momjian Exp $
12+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plerrcodes.h,v 1.8 2006/06/15 18:02:22 momjian Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -722,3 +722,13 @@
722722
{
723723
"index_corrupted",ERRCODE_INDEX_CORRUPTED
724724
},
725+
726+
{
727+
"no_data_found",ERRCODE_NO_DATA
728+
},
729+
730+
{
731+
"too_many_rows",ERRCODE_CARDINALITY_VIOLATION
732+
},
733+
734+

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

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.75 2006/06/12 16:45:30 momjian Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.76 2006/06/15 18:02:22 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -432,6 +432,7 @@ typedef struct
432432
{/* SELECT ... INTO statement*/
433433
intcmd_type;
434434
intlineno;
435+
boolstrict;
435436
PLpgSQL_rec*rec;
436437
PLpgSQL_row*row;
437438
PLpgSQL_expr*query;

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

Lines changed: 2 additions & 1 deletion
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.50 2006/06/12 16:45:30 momjian Exp $
12+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/scan.l,v 1.51 2006/06/15 18:02:22 momjian Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -129,6 +129,7 @@ else{ return K_ELSE;}
129129
elseif {return K_ELSIF; }
130130
elsif {return K_ELSIF; }
131131
end{return K_END;}
132+
strict{return K_STRICT; }
132133
exception{return K_EXCEPTION;}
133134
execute{return K_EXECUTE;}
134135
exit{return K_EXIT;}

0 commit comments

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