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

Commitb62f246

Browse files
committed
Support SQL/PSM-compatible CASE statement in plpgsql.
Pavel Stehule
1 parent0fdb350 commitb62f246

File tree

11 files changed

+704
-65
lines changed

11 files changed

+704
-65
lines changed

‎doc/src/sgml/errcodes.sgml

Lines changed: 15 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/errcodes.sgml,v 1.23 2008/01/1501:36:53 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/errcodes.sgml,v 1.24 2008/05/1522:39:48 tgl Exp $ -->
22

33
<appendix id="errcodes-appendix">
44
<title><productname>PostgreSQL</productname> Error Codes</title>
@@ -62,14 +62,14 @@
6262

6363
<tgroup cols="3">
6464
<colspec colnum="1" colname="errorcode">
65-
<colspec colnum="3" colname="constant">
66-
<spanspec namest="errorcode" nameend="constant" spanname="span13">
65+
<colspec colnum="3" colname="condname">
66+
<spanspec namest="errorcode" nameend="condname" spanname="span13">
6767

6868
<thead>
6969
<row>
7070
<entry>Error Code</entry>
7171
<entry>Meaning</entry>
72-
<entry>Constant</entry>
72+
<entry>Condition Name</entry>
7373
</row>
7474
</thead>
7575

@@ -292,6 +292,17 @@
292292
</row>
293293

294294

295+
<row>
296+
<entry spanname="span13"><emphasis role="bold">Class 20 &mdash; Case Not Found</></entry>
297+
</row>
298+
299+
<row>
300+
<entry><literal>20000</literal></entry>
301+
<entry>CASE NOT FOUND</entry>
302+
<entry>case_not_found</entry>
303+
</row>
304+
305+
295306
<row>
296307
<entry spanname="span13"><emphasis role="bold">Class 21 &mdash; Cardinality Violation</></entry>
297308
</row>

‎doc/src/sgml/plpgsql.sgml

Lines changed: 107 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.129 2008/05/13 22:10:29 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.130 2008/05/15 22:39:49 tgl Exp $ -->
22

33
<chapter id="plpgsql">
44
<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -1581,9 +1581,9 @@ SELECT * FROM getallfoo();
15811581
<title>Conditionals</title>
15821582

15831583
<para>
1584-
<literal>IF</> statements let you execute commands based on
1585-
certain conditions. <application>PL/pgSQL</> has five forms of
1586-
<literal>IF</>:
1584+
<command>IF</>and <command>CASE</>statements let you execute
1585+
alternative commands based on certain conditions.
1586+
<application>PL/pgSQL</> has five forms of <command>IF</>:
15871587
<itemizedlist>
15881588
<listitem>
15891589
<para><literal>IF ... THEN</></>
@@ -1601,6 +1601,22 @@ SELECT * FROM getallfoo();
16011601
<para><literal>IF ... THEN ... ELSEIF ... THEN ... ELSE</></>
16021602
</listitem>
16031603
</itemizedlist>
1604+
1605+
and four forms of <command>CASE</>:
1606+
<itemizedlist>
1607+
<listitem>
1608+
<para><literal>CASE ... WHEN ... THEN ... END CASE</></>
1609+
</listitem>
1610+
<listitem>
1611+
<para><literal>CASE ... WHEN ... THEN ... ELSE ... END CASE</></>
1612+
</listitem>
1613+
<listitem>
1614+
<para><literal>CASE WHEN ... THEN ... END CASE</></>
1615+
</listitem>
1616+
<listitem>
1617+
<para><literal>CASE WHEN ... THEN ... ELSE ... END CASE</></>
1618+
</listitem>
1619+
</itemizedlist>
16041620
</para>
16051621

16061622
<sect3>
@@ -1751,6 +1767,93 @@ END IF;
17511767
<literal>ELSEIF</> is an alias for <literal>ELSIF</>.
17521768
</para>
17531769
</sect3>
1770+
1771+
<sect3>
1772+
<title>Simple <literal>CASE</></title>
1773+
1774+
<synopsis>
1775+
CASE <replaceable>search-expression</replaceable>
1776+
WHEN <replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> <optional> ... </optional></optional> THEN
1777+
<replaceable>statements</replaceable>
1778+
<optional> WHEN <replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> <optional> ... </optional></optional> THEN
1779+
<replaceable>statements</replaceable>
1780+
... </optional>
1781+
<optional> ELSE
1782+
<replaceable>statements</replaceable> </optional>
1783+
END CASE;
1784+
</synopsis>
1785+
1786+
<para>
1787+
The simple form of <command>CASE</> provides conditional execution
1788+
based on equality of operands. The <replaceable>search-expression</>
1789+
is evaluated (once) and successively compared to each
1790+
<replaceable>expression</> in the <literal>WHEN</> clauses.
1791+
If a match is found, then the corresponding
1792+
<replaceable>statements</replaceable> are executed, and then control
1793+
passes to the next statement after <literal>END CASE</>. (Subsequent
1794+
<literal>WHEN</> expressions are not evaluated.) If no match is
1795+
found, the <literal>ELSE</> <replaceable>statements</replaceable> are
1796+
executed; but if <literal>ELSE</> is not present, then a
1797+
<literal>CASE_NOT_FOUND</literal> exception is raised.
1798+
</para>
1799+
1800+
<para>
1801+
Here is a simple example:
1802+
1803+
<programlisting>
1804+
CASE x
1805+
WHEN 1, 2 THEN
1806+
msg := 'one or two';
1807+
ELSE
1808+
msg := 'other value than one or two';
1809+
END CASE;
1810+
</programlisting>
1811+
</para>
1812+
</sect3>
1813+
1814+
<sect3>
1815+
<title>Searched <literal>CASE</></title>
1816+
1817+
<synopsis>
1818+
CASE
1819+
WHEN <replaceable>boolean-expression</replaceable> THEN
1820+
<replaceable>statements</replaceable>
1821+
<optional> WHEN <replaceable>boolean-expression</replaceable> THEN
1822+
<replaceable>statements</replaceable>
1823+
... </optional>
1824+
<optional> ELSE
1825+
<replaceable>statements</replaceable> </optional>
1826+
END CASE;
1827+
</synopsis>
1828+
1829+
<para>
1830+
The searched form of <command>CASE</> provides conditional execution
1831+
based on truth of boolean expressions. Each <literal>WHEN</> clause's
1832+
<replaceable>boolean-expression</replaceable> is evaluated in turn,
1833+
until one is found that yields <literal>true</>. Then the
1834+
corresponding <replaceable>statements</replaceable> are executed, and
1835+
then control passes to the next statement after <literal>END CASE</>.
1836+
(Subsequent <literal>WHEN</> expressions are not evaluated.)
1837+
If no true result is found, the <literal>ELSE</>
1838+
<replaceable>statements</replaceable> are executed;
1839+
but if <literal>ELSE</> is not present, then a
1840+
<literal>CASE_NOT_FOUND</literal> exception is raised.
1841+
</para>
1842+
1843+
<para>
1844+
Here is an example:
1845+
1846+
<programlisting>
1847+
CASE
1848+
WHEN x BETWEEN 0 AND 10 THEN
1849+
msg := 'value is between zero and ten';
1850+
WHEN x BETWEEN 11 AND 20 THEN
1851+
msg := 'value is between eleven and twenty';
1852+
END CASE;
1853+
</programlisting>
1854+
</para>
1855+
1856+
</sect3>
17541857
</sect2>
17551858

17561859
<sect2 id="plpgsql-control-structures-loops">

‎src/include/utils/errcodes.h

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
* Copyright (c) 2003-2008, PostgreSQL Global Development Group
1313
*
14-
* $PostgreSQL: pgsql/src/include/utils/errcodes.h,v 1.24 2008/01/01 19:45:59 momjian Exp $
14+
* $PostgreSQL: pgsql/src/include/utils/errcodes.h,v 1.25 2008/05/15 22:39:49 tgl Exp $
1515
*
1616
*-------------------------------------------------------------------------
1717
*/
@@ -101,6 +101,9 @@
101101
/* Class 0P - Invalid Role Specification */
102102
#defineERRCODE_INVALID_ROLE_SPECIFICATIONMAKE_SQLSTATE('0','P', '0','0','0')
103103

104+
/* Class 20 - Case Not Found */
105+
#defineERRCODE_CASE_NOT_FOUNDMAKE_SQLSTATE('2','0', '0','0','0')
106+
104107
/* Class 21 - Cardinality Violation */
105108
/* (this means something returned the wrong number of rows) */
106109
#defineERRCODE_CARDINALITY_VIOLATIONMAKE_SQLSTATE('2','1', '0','0','0')

0 commit comments

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