syntax.sgml 24.1 KB
Newer Older
1
 <chapter id="syntax">
2 3 4 5
  <title>SQL Syntax</title>

  <abstract>
   <para>
6
    A description of the general syntax of SQL.
7 8 9
   </para>
  </abstract>

10 11 12 13 14 15 16 17
  <para>
   <acronym>SQL</acronym> manipulates sets of data. The language is
   composed of various <firstterm>key words</firstterm>. Arithmetic
   and procedural expressions are allowed. We will cover these topics
   in this chapter; subsequent chapters will include details on data
   types, functions, and operators.
  </para>

18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74
  <sect1>
   <title>Key Words</title>

   <para>
    <acronym>SQL92</acronym> defines <firstterm>key words</firstterm> 
    for the language
    which have specific meaning. Some key words are
    <firstterm>reserved</firstterm>, which indicates that they are
    restricted to appear in only certain contexts. Other key words are
    <firstterm>not restricted</firstterm>, which indicates that in certain 
    contexts they
    have a specific meaning but are not otherwise constrained.
   </para>

   <para>
    <productname>Postgres</productname> implements an extended subset of the
    <acronym>SQL92</acronym> and <acronym>SQL3</acronym> languages. Some language
    elements are not as restricted in this implementation as is
    called for in the language standards, in part due
    to the extensibility features of <productname>Postgres</productname>. 
   </para>

   <para>
    Information on <acronym>SQL92</acronym> and <acronym>SQL3</acronym> key words
    is derived from <xref linkend="DATE97" endterm="DATE97">.
   </para>

   <sect2>
    <title>Reserved Key Words</title>

    <para>
     <acronym>SQL92</acronym> and <acronym>SQL3</acronym> have 
     <firstterm>reserved key words</firstterm> which are not allowed 
     as identifiers and not allowed in any usage other than as fundamental
     tokens in <acronym>SQL</acronym> statements.
     <productname>Postgres</productname> has additional key words
     which have similar restrictions. In particular, these key words
     are not allowed as column or table names, though in some cases
     they are allowed to be column labels (i.e. in AS clauses).
    </para>

    <tip>
     <para>
      Any string can be specified as an identifier if surrounded by
      double quotes (<quote>like this!</quote>). Some care is required since
      such an identifier will be case sensitive
      and will retain embedded whitespace other special characters.
     </para>
    </tip>

    <para>
     The following are <productname>Postgres</productname>
     reserved words which are neither <acronym>SQL92</acronym>
     nor <acronym>SQL3</acronym> reserved words. These are allowed
     to be present as column labels, but not as identifiers:

     <programlisting>
75 76 77 78 79 80 81 82 83 84 85 86
ABORT ANALYZE 
BINARY 
CLUSTER CONSTRAINT COPY
DO 
EXPLAIN EXTEND
LISTEN LOAD LOCK 
MOVE 
NEW NONE NOTIFY 
RESET 
SETOF SHOW
UNLISTEN UNTIL 
VACUUM VERBOSE
87 88
     </programlisting>
    </para>
89

90 91 92 93 94
    <para>
     The following are <productname>Postgres</productname>
     reserved words which are also <acronym>SQL92</acronym> 
     or <acronym>SQL3</acronym> reserved words, and which
     are allowed to be present as column labels, but not as identifiers:
95

96
     <programlisting>
97 98
CASE COALESCE CROSS CURRENT 
ELSE END
99
FALSE FOREIGN 
100 101
GLOBAL GROUP 
LOCAL
102
NULLIF
103 104
ORDER 
POSITION PRECISION 
105 106
TABLE THEN TRANSACTION TRUE
WHEN
107
     </programlisting>
108

109 110 111
     The following are <productname>Postgres</productname>
     reserved words which are also <acronym>SQL92</acronym> 
     or <acronym>SQL3</acronym> reserved words:
112

113
     <programlisting>
114 115
ADD ALL ALTER AND ANY AS ASC
BEGIN BETWEEN BOTH BY
116 117 118 119
CASCADE CAST CHAR CHARACTER CHECK CLOSE 
 COLLATE COLUMN COMMIT CONSTRAINT 
 CREATE CURRENT_DATE CURRENT_TIME 
 CURRENT_TIMESTAMP CURRENT_USER CURSOR
120
DECIMAL DECLARE DEFAULT DELETE DESC DISTINCT DROP
121
EXECUTE EXISTS EXTRACT
122 123 124 125 126 127 128 129 130 131 132
FETCH FLOAT FOR FROM FULL
GRANT 
HAVING
IN INNER INSERT INTERVAL INTO IS
JOIN 
LEADING LEFT LIKE LOCAL
NAMES NATIONAL NATURAL NCHAR NO NOT NULL NUMERIC
ON OR OUTER
PARTIAL PRIMARY PRIVILEGES PROCEDURE PUBLIC
REFERENCES REVOKE RIGHT ROLLBACK
SELECT SET SUBSTRING
133
TO TRAILING TRIM 
134 135 136
UNION UNIQUE UPDATE USER USING
VALUES VARCHAR VARYING VIEW
WHERE WITH WORK 
137 138
     </programlisting>
    </para>
139

140 141 142 143 144
    <para>
     The following are <acronym>SQL92</acronym> reserved key words which
     are not <productname>Postgres</productname> reserved key words, but which
     if used as function names are always translated into the function
     <function>length</function>:
145

146
     <programlisting>
147
CHAR_LENGTH CHARACTER_LENGTH
148 149
     </programlisting>
    </para>
150

151 152 153 154 155
    <para>
     The following are <acronym>SQL92</acronym> or <acronym>SQL3</acronym> 
     reserved key words which
     are not <productname>Postgres</productname> reserved key words, but
     if used as type names are always translated into an alternate, native type:
156

157
     <programlisting>
158
BOOLEAN DOUBLE FLOAT INT INTEGER INTERVAL REAL SMALLINT
159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179
     </programlisting>
    </para>

    <para>
     The following are either <acronym>SQL92</acronym>
     or <acronym>SQL3</acronym> reserved key words
     which are not key words in <productname>Postgres</productname>.
     These have no proscribed usage in <productname>Postgres</productname>
     at the time of writing (v6.5) but may become reserved key words in the
     future:

     <note>
      <para>
       Some of these key words represent functions in <acronym>SQL92</acronym>.
       These functions are defined in <productname>Postgres</productname>,
       but the parser does not consider the names to be key words and they are allowed
       in other contexts.
      </para>
     </note>

     <programlisting>
180 181
ALLOCATE ARE ASSERTION AT AUTHORIZATION AVG 
BIT BIT_LENGTH
182 183
CASCADED CATALOG COLLATION CONNECT CONNECTION
 CONSTRAINTS CONTINUE CONVERT CORRESPONDING COUNT
184 185
DATE DEALLOCATE DEC DEFERRABLE DEFERRED DESCRIBE DESCRIPTOR
 DIAGNOSTICS DISCONNECT DOMAIN 
186
END-EXEC ESCAPE EXCEPT EXCEPTION EXEC EXTERNAL 
187
FIRST FOUND
188
GET GO GOTO 
189 190
IDENTITY IMMEDIATE INDICATOR INITIALLY INPUT INTERSECT
LAST LOWER 
191 192 193 194 195
MAX MIN MODULE 
OCTET_LENGTH OPEN OUTPUT OVERLAPS 
PREPARE PRESERVE 
RESTRICT ROWS
SCHEMA SECTION SESSION SESSION_USER SIZE SOME 
196 197
 SQL SQLCODE SQLERROR SQLSTATE SUM SYSTEM_USER
TEMPORARY TRANSLATE TRANSLATION 
198 199
UNKNOWN UPPER USAGE
VALUE 
200
WHENEVER WRITE
201 202 203 204 205 206 207 208 209 210
     </programlisting>
    </para>
   </sect2>

   <sect2>
    <title>Non-reserved Keywords</title>

    <para>
     <acronym>SQL92</acronym> and <acronym>SQL3</acronym> have 
     <firstterm>non-reserved keywords</firstterm> which have
211
     a prescribed meaning in the language but which are also allowed
212 213 214 215 216 217 218 219 220 221 222 223 224 225
     as identifiers.
     <productname>Postgres</productname> has additional keywords
     which allow similar unrestricted usage.
     In particular, these keywords
     are allowed as column or table names.
    </para>

    <para>
     The following are <productname>Postgres</productname>
     non-reserved key words which are neither <acronym>SQL92</acronym>
     nor <acronym>SQL3</acronym> non-reserved key words:

     <programlisting>
ACCESS AFTER AGGREGATE 
226 227 228
BACKWARD BEFORE 
CACHE CREATEDB CREATEUSER CYCLE
DATABASE DELIMITERS 
229
EACH ENCODING EXCLUSIVE
230 231 232 233
FORWARD FUNCTION 
HANDLER
INCREMENT INDEX INHERITS INSENSITIVE INSTEAD ISNULL
LANCOMPILER LOCATION 
234
MAXVALUE MINVALUE MODE
235 236 237
NOCREATEDB NOCREATEUSER NOTHING NOTNULL 
OIDS OPERATOR 
PASSWORD PROCEDURAL
238
RECIPE RENAME RETURNS ROW RULE
239
SEQUENCE SERIAL SHARE START STATEMENT STDIN STDOUT 
240 241
TRUSTED 
VALID VERSION
242 243
     </programlisting>
    </para>
244

245 246 247 248
    <para>
     The following are <productname>Postgres</productname>
     non-reserved key words which are <acronym>SQL92</acronym>
     or <acronym>SQL3</acronym> reserved key words:
249

250
     <programlisting>
251 252 253
ABSOLUTE ACTION
DAY
HOUR
254
INSENSITIVE ISOLATION 
255
KEY 
256
LANGUAGE LEVEL 
257 258 259 260 261 262
MATCH MINUTE MONTH
NEXT 
OF ONLY OPTION 
PRIOR PRIVILEGES 
READ RELATIVE 
SCROLL SECOND
263
TIME TIMESTAMP TIMEZONE_HOUR TIMEZONE_MINUTE TRIGGER 
264 265
YEAR
ZONE
266 267
     </programlisting>
    </para>
268

269 270 271 272
    <para>
     The following are <productname>Postgres</productname>
     non-reserved key words which are also either <acronym>SQL92</acronym>
     or <acronym>SQL3</acronym> non-reserved key words:
273

274 275 276 277
     <programlisting>
COMMITTED SERIALIZABLE TYPE
     </programlisting>
    </para>
278

279 280 281 282
    <para>
     The following are either <acronym>SQL92</acronym>
     or <acronym>SQL3</acronym> non-reserved key words which are not
     key words of any kind in <productname>Postgres</productname>:
283

284
     <programlisting>
285 286
ADA
C CATALOG_NAME CHARACTER_SET_CATALOG CHARACTER_SET_NAME 
287 288 289 290 291
 CHARACTER_SET_SCHEMA CLASS_ORIGIN COBOL COLLATION_CATALOG
 COLLATION_NAME COLLATION_SCHEMA COLUMN_NAME
 COMMAND_FUNCTION CONDITION_NUMBER
 CONNECTION_NAME CONSTRAINT_CATALOG CONSTRAINT_NAME
 CONSTRAINT_SCHEMA CURSOR_NAME
292
DATA DATE_TIME_INTERVAL_CODE DATE_TIME_INTERVAL_PRECISION
293
 DYNAMIC_FUNCTION
294 295 296 297 298 299
FORTRAN
LENGTH
MESSAGE_LENGTH MESSAGE_OCTET_LENGTH MORE MUMPS
NAME NULLABLE NUMBER
PAD PASCAL PLI
REPEATABLE RETURNED_LENGTH RETURNED_OCTET_LENGTH
300 301
 RETURNED_SQLSTATE ROW_COUNT
SCALE SCHEMA_NAME SERVER_NAME SPACE SUBCLASS_ORIGIN
302 303
TABLE_NAME
UNCOMMITTED UNNAMED
304 305 306 307 308
     </programlisting>
    </para>
   </sect2>
  </sect1>

309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326
  <sect1>
   <title>Comments</title>

   <para>
    A <firstterm>comment</firstterm>
    is an arbitrary sequence of characters following double dashes up to the end
    of the line.  We also support double-slashes as comments, e.g.:

    <programlisting>
-- This is a standard SQL comment
// And this is another supported comment style, like C++
    </programlisting>

We also support C-style block comments, e.g.:

    <programlisting>
/* multi
   line
327 328
   comment
 */
329 330 331 332 333 334 335 336 337 338 339
    </programlisting>
   </para>
  </sect1>

  <sect1>
   <title>Names</title>

   <para>
    Names in SQL are sequences of less than NAMEDATALEN alphanumeric characters,
    starting with an alphabetic character.  By default, NAMEDATALEN is set
    to 32, but at the time the system is built, NAMEDATALEN can be changed
340 341 342 343 344 345 346 347 348 349
    by changing the <literal>#define</literal> in
    src/backend/include/postgres.h.
    Underscore ("_") is considered an alphabetic character.
   </para>

   <para>
    In some contexts, names may contain other characters if surrounded 
    by double quotes. For example, table or column names may contain otherwise
    disallowed characters such as spaces, ampersands, etc. using this
    technique.
350 351 352 353 354 355 356
   </para>
  </sect1>

  <sect1>
   <title>Constants</title>

   <para>
357 358 359 360 361 362 363
    There are three <firstterm>implicitly typed constants</firstterm>
    for use in <productname>Postgres</productname>: strings, integers, 
    and floating point numbers.  Constants can
    also be specified with explicit types, which can enable more
    accurate representation and more efficient handling by the
    backend. The implicit constants are described below; explicit
    constants are discussed afterwards.
364 365 366 367 368 369 370 371
   </para>

   <sect2>
    <title>String Constants</title>

    <para>
     <firstterm>Strings</firstterm>
     in SQL are arbitrary sequences of ASCII characters bounded by single
372
     quotes ("'", e.g. <literal>'This is a string'</literal>).
373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400
     Uppercase alphabetics within strings are accepted
     literally.  Non-printing characters may be embedded within strings by
     prepending them with a backslash
     ("\"; e.g. "\<replaceable>tab</replaceable>".
     SQL92 allows single quotes to be embedded in strings by typing two 
     adjacent single quotes (e.g. 'Dianne''s horse'), and for
     historical reasons <productname>Postgres</productname> also allows 
     single quotes to be escaped with a backslash
     (e.g. 'Dianne\'s horse').
    </para>

    <para>
     Because of the limitations on
     instance sizes, string constants are currently limited to a length of
     a little less than 8192 bytes.  Larger strings may be handled using the
     Postgres Large Object interface.
    </para>
   </sect2>

   <sect2>
    <title>Integer Constants</title>

    <para>
     <firstterm>Integer constants</firstterm>
     in SQL are collection of ASCII digits with no decimal point.  Legal
     values range from -2147483648 to +2147483647.  This will vary
     depending on the operating system and host machine.
    </para>
401 402 403 404 405 406 407 408 409 410 411

    <para>
     Note that larger integers can be specified for <type>int8</type>
     by using <acronym>SQL92</acronym> string notation or
     <productname>Postgres</productname> type notation:

     <programlisting>
int8 '4000000000'  -- string style
'4000000000'::int8 -- Postgres (historical) style
     </programlisting>
    </para>
412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429
   </sect2>

   <sect2>
    <title>Floating Point Constants</title>

    <para>
     <firstterm>Floating point constants</firstterm>
     consist of an integer part, a decimal point, and a fraction part or
     scientific notation of the following format:

     <synopsis>
{<replaceable>dig</replaceable>}.{<replaceable>dig</replaceable>} [e [+-] {<replaceable>dig</replaceable>}]
     </synopsis>

     where <replaceable>dig</replaceable> is one or more digits.
     You must include at least one <replaceable>dig</replaceable> after the
     period and after the [+-] if you use those options.  An exponent with
     a missing mantissa has a mantissa of 1 inserted.  There may be no
430 431 432 433 434 435 436 437 438 439 440 441 442
     extra characters embedded in the string.
    </para>

    <para>
     Floating point constaints are of type
     <type>float8</type>. <type>float4</type> can be specified
     explicitly by using <acronym>SQL92</acronym> string notation or
     <productname>Postgres</productname> type notation:

     <programlisting>
float4 '1.23'  -- string style
'1.23'::float4 -- Postgres (historical) style
     </programlisting>
443 444 445 446 447 448 449 450 451
    </para>
   </sect2>

   <sect2>
    <title>Constants of Postgres User-Defined Types</title>

    <para>
     A constant of an
     <emphasis>arbitrary</emphasis>
452
     type can be entered using any one of the following notations:
453 454 455 456 457 458 459 460

     <synopsis>
<replaceable>type</replaceable> '<replaceable>string</replaceable>'
'<replaceable>string</replaceable>'::<replaceable>type</replaceable>
CAST '<replaceable>string</replaceable>' AS <replaceable>type</replaceable>
     </synopsis>

     The value inside the string is passed to the input
461 462
     conversion routine for the type called
     <replaceable>type</replaceable>. The result is a
463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610
     constant of the indicated type.  The explicit typecast may be omitted
     if there is no ambiguity as to the type the constant must be, in which
     case it is automatically coerced.
    </para>
   </sect2>

   <sect2>
    <title>Array constants</title>

    <para>
     <firstterm>Array constants</firstterm>
     are arrays of any Postgres type, including other arrays, string
     constants, etc.  The general format of an array constant is the
     following:

     <synopsis>
{<replaceable>val1</replaceable><replaceable>delim</replaceable><replaceable>val2</replaceable><replaceable>delim</replaceable>}
     </synopsis>

     where <replaceable>delim</replaceable>
     is the delimiter for the type stored in the <literal>pg_type</literal> class.
     (For built-in types, this is the comma character (",").  An
     example of an array constant is

     <programlisting>
{{1,2,3},{4,5,6},{7,8,9}}
     </programlisting>

     This constant is a two-dimensional, 3 by 3 array consisting of three
     sub-arrays of integers.
    </para>

    <para>
     Individual array elements can and should be placed between quotation
     marks whenever possible to avoid ambiguity problems with respect to
     leading white space.
    </para>
   </sect2>
  </sect1>

  <sect1>
   <title>Fields and Columns</title>

   <sect2>
    <title>Fields</title>

    <para>
     A <firstterm>field</firstterm>
     is either an attribute of a given class or one of the following:

     <variablelist>
      <varlistentry>
       <term>oid</term>
       <listitem>
	<para>
	 stands for the unique identifier of an instance which is added by
	 Postgres to all instances automatically. Oids are not reused and are 32
	 bit quantities.
	</para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term>xmin</term>
       <listitem>
	<para>
	 The identity of the inserting transaction.
	</para>
       </listitem>
      </varlistentry>

      <varlistentry>
      <term>xmax</term>
       <listitem>
	<para>
	 The identity of the deleting transaction.
	</para>
       </listitem>
      </varlistentry>

      <varlistentry>
      <term>cmin</term>
       <listitem>
	<para>
	 The command identifier within the transaction.
	</para>
       </listitem>
      </varlistentry>

      <varlistentry>
      <term>cmax</term>
       <listitem>
	<para>
	 The identity of the deleting command.
	</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>

    <para>
     For further information on these fields consult
     <xref linkend="STON87a" endterm="STON87a">.
     Times are represented internally as instances of the
     <literal>abstime</literal>
     data type.  Transaction and command identifiers are 32 bit quantities.
     Transactions are assigned sequentially starting at 512.
    </para>
   </sect2>

   <sect2>
    <title>Columns</title>

    <para>
     A <firstterm>column</firstterm> is a construct of the form:

     <synopsis>
<replaceable>instance</replaceable>{.<replaceable>composite_field</replaceable>}.<replaceable>field</replaceable> `['<replaceable>number</replaceable>`]'
     </synopsis>

     <replaceable>instance</replaceable>
     identifies a particular class and can be thought of as standing for
     the instances of that class.  An instance variable is either a class
     name, a surrogate for a class defined by means of a FROM clause,
     or the keyword NEW or CURRENT.
     NEW and CURRENT can only appear in the action portion of a rule, while
     other instance variables can be used in any SQL statement.
     <replaceable>composite_field</replaceable>
     is a field of of one of the Postgres composite types,
     while successive composite fields address attributes in the
     class(s) to which the composite field evaluates.  Lastly,
     <replaceable>field</replaceable>
     is a normal (base type) field in the class(s) last addressed.  If
     <replaceable>field</replaceable>
     is of type <literal>array</literal>,
     then the optional <replaceable>number</replaceable>
     designator indicates a specific element in the array.  If no number is
     indicated, then all array elements are returned.
    </para>
   </sect2>
  </sect1>

  <sect1>
   <title>Operators</title>

   <para>
    Any built-in system, or user-defined operator may be used in SQL.
    For the list of built-in and system operators consult
611
    <xref linkend="operators" endterm="operators-title">.
612 613 614 615 616 617
    For a list of user-defined operators consult your system administrator
    or run a query on the <literal>pg_operator</literal> class.
    Parentheses may be used for arbitrary grouping of operators in expressions.
   </para>
  </sect1>

618 619 620 621 622
  <sect1>
   <title>Expressions</title>

   <para>
    <acronym>SQL92</acronym> allows <firstterm>expressions</firstterm>
623
    to transform data in tables. Expressions may contain operators
624
    (see <xref linkend="operators" endterm="operators-title">
625
    for more details) and functions
626
    (<xref linkend="functions" endterm="functions-title"> has
627
    more information).
628
   </para>
629

630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649
   <para>
    An expression is one of the following:

    <simplelist>
     <member>( a_expr )</member>
     <member>constant</member>
     <member>attribute</member>
     <member><replaceable>a_expr</replaceable> <replaceable>binary_operator</replaceable> <replaceable>a_expr</replaceable></member>
     <member><replaceable>a_expr</replaceable> <replaceable>right_unary_operator</replaceable></member>
     <member><replaceable>left_unary_operator</replaceable> <replaceable>a_expr</replaceable></member>
     <member>parameter</member>
     <member>functional expressions</member>
     <member>aggregate expressions</member>
    </simplelist>
   </para>

   <para>
    We have already discussed constants and attributes.  The two kinds of
    operator expressions indicate respectively binary and left_unary
    expressions.  The following sections discuss the remaining options.
650
   </para>
651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793

   <sect2>
    <title>Parameters</title>

    <para>
     A <firstterm>parameter</firstterm>
     is used to indicate a parameter in a SQL function.  Typically this
     is used in SQL function definition statement.  The form of a
     parameter is:

     <synopsis>
$<replaceable class="parameter">number</replaceable>
     </synopsis>
    </para>

    <para>
     For example, consider the definition of a function,
     <function>dept</function>, as

     <programlisting>
CREATE FUNCTION dept (name)
	RETURNS dept
	AS 'select * from 
	    dept where name=$1'
	LANGUAGE 'sql';
     </programlisting>
    </para>
   </sect2>

   <sect2>
    <title>Functional Expressions</title>

    <para>
     A <firstterm>functional expression</firstterm>
     is the name of a legal SQL function, followed by its argument list
     enclosed in parentheses:

     <synopsis>
<replaceable>function</replaceable> (<replaceable>a_expr</replaceable> [, <replaceable>a_expr</replaceable> )
     </synopsis>
    </para>

    <para>
     For example, the following computes the square root of an employee
     salary:

     <programlisting>
sqrt(emp.salary)
     </programlisting>
    </para>
   </sect2>

   <sect2>
    <title>Aggregate Expression</title>

    <para>
     An <firstterm>aggregate expression</firstterm>
     represents a simple aggregate (i.e., one that computes a single value)
     or an aggregate function (i.e., one that computes a set of values).
     The syntax is the following:

     <synopsis>
<replaceable>aggregate_name</replaceable> (<replaceable>attribute</replaceable>)
     </synopsis>

     where <replaceable>aggregate_name</replaceable>
     must be a previously defined aggregate.
    </para>
   </sect2>

   <sect2>
    <title>Target List</title>

    <para>
     A <firstterm>target list</firstterm>
     is a parenthesized, comma-separated list of one or more elements, each
     of which must be of the form:

     <synopsis>
<replaceable>a_expr</replaceable> [ AS <replaceable>result_attname</replaceable> ]
     </synopsis>

     where <replaceable>result_attname</replaceable>
     is the name of the attribute to be created (or an
     already existing attribute name in the case of update statements.)  If
     <replaceable>result_attname</replaceable>
     is not present, then 
     <replaceable>a_expr</replaceable>
     must contain only one attribute name which is assumed to be the name
     of the result field.  In <productname>Postgres</productname>
     default naming is only used if
     <replaceable>a_expr</replaceable>
     is an attribute.
    </para>
   </sect2>

   <sect2>
    <title>Qualification</title>

    <para>
     A <firstterm>qualification</firstterm>
     consists of any number of clauses connected by the logical operators:

     <simplelist>
      <member>NOT</member>
      <member>AND</member>
      <member>OR</member>
     </simplelist>

     A clause is an <replaceable>a_expr</replaceable>
     that evaluates to a <literal>boolean</literal> over a set of instances.
    </para>
   </sect2>

   <sect2>
    <title>From List</title>

    <para>
     The <firstterm>from list</firstterm>
     is a comma-separated list of <firstterm>from expressions</firstterm>.
     Each "from expression" is of the form:

     <synopsis>
[ <replaceable>class_reference</replaceable> ] <replaceable>instance_variable</replaceable>
	{, [ <replaceable>class_ref</replaceable> ] <replaceable>instance_variable</replaceable>... }
     </synopsis>

     where <replaceable>class_reference</replaceable>
     is of the form

     <synopsis>
<replaceable>class_name</replaceable> [ * ]
     </synopsis>

     The "from expression"
     defines one or more instance variables to range over the class
     indicated in <replaceable>class_reference</replaceable>.
     One can also request 
     the instance variable to range over all classes that are beneath the
     indicated class in the inheritance hierarchy by postpending the
     designator asterisk ("*").
    </para>
   </sect2>
794 795
  </sect1>
 </chapter>
796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/CATALOG"
sgml-local-ecat-files:nil
End:
-->