11<!--
2- $PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.92 2004/12/30 21:45:37 tgl Exp $
2+ $PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.93 2005/01/07 22:40:46 tgl Exp $
33-->
44
55 <sect1 id="xfunc">
@@ -111,6 +111,39 @@ $PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.92 2004/12/30 21:45:37 tgl Exp $
111111 <type>void</>, the last statement must be a <command>SELECT</>.
112112 </para>
113113
114+ <para>
115+ Any collection of commands in the <acronym>SQL</acronym>
116+ language can be packaged together and defined as a function.
117+ Besides <command>SELECT</command> queries, the commands can include data
118+ modification queries (<command>INSERT</command>,
119+ <command>UPDATE</command>, and <command>DELETE</command>), as well as
120+ other SQL commands. (The only exception is that you can't put
121+ <command>BEGIN</>, <command>COMMIT</>, <command>ROLLBACK</>, or
122+ <command>SAVEPOINT</> commands into a <acronym>SQL</acronym> function.)
123+ However, the final command
124+ must be a <command>SELECT</command> that returns whatever is
125+ specified as the function's return type. Alternatively, if you
126+ want to define a SQL function that performs actions but has no
127+ useful value to return, you can define it as returning <type>void</>.
128+ In that case, the function body must not end with a <command>SELECT</command>.
129+ For example, this function removes rows with negative salaries from
130+ the <literal>emp</> table:
131+
132+ <screen>
133+ CREATE FUNCTION clean_emp() RETURNS void AS '
134+ DELETE FROM emp
135+ WHERE salary < 0;
136+ ' LANGUAGE SQL;
137+
138+ SELECT clean_emp();
139+
140+ clean_emp
141+ -----------
142+
143+ (1 row)
144+ </screen>
145+ </para>
146+
114147 <para>
115148 The syntax of the <command>CREATE FUNCTION</command> command requires
116149 the function body to be written as a string constant. It is usually
@@ -219,35 +252,6 @@ $$ LANGUAGE SQL;
219252
220253 which adjusts the balance and returns the new balance.
221254 </para>
222-
223- <para>
224- Any collection of commands in the <acronym>SQL</acronym>
225- language can be packaged together and defined as a function.
226- Besides <command>SELECT</command> queries,
227- the commands can include data modification (i.e.,
228- <command>INSERT</command>, <command>UPDATE</command>, and
229- <command>DELETE</command>). However, the final command
230- must be a <command>SELECT</command> that returns whatever is
231- specified as the function's return type. Alternatively, if you
232- want to define a SQL function that performs actions but has no
233- useful value to return, you can define it as returning <type>void</>.
234- In that case, the function body must not end with a <command>SELECT</command>.
235- For example:
236-
237- <screen>
238- CREATE FUNCTION clean_emp() RETURNS void AS $$
239- DELETE FROM emp
240- WHERE salary <= 0;
241- $$ LANGUAGE SQL;
242-
243- SELECT clean_emp();
244-
245- clean_emp
246- -----------
247-
248- (1 row)
249- </screen>
250- </para>
251255 </sect2>
252256
253257 <sect2>
@@ -282,7 +286,7 @@ SELECT name, double_salary(emp.*) AS dream
282286
283287 name | dream
284288------+-------
285- Sam |2400
289+ Bill |8400
286290</screen>
287291 </para>
288292
@@ -307,7 +311,7 @@ SELECT name, double_salary(emp) AS dream
307311 on-the-fly. This can be done with the <literal>ROW</> construct.
308312 For example, we could adjust the data being passed to the function:
309313<screen>
310- SELECT name, double_salary(row (name, salary*1.1, age, cubicle)) AS dream
314+ SELECT name, double_salary(ROW (name, salary*1.1, age, cubicle)) AS dream
311315 FROM emp;
312316</screen>
313317 </para>
@@ -320,7 +324,7 @@ SELECT name, double_salary(row(name, salary*1.1, age, cubicle)) AS dream
320324<programlisting>
321325CREATE FUNCTION new_emp() RETURNS emp AS $$
322326 SELECT text 'None' AS name,
323- 1000 AS salary,
327+ 1000.0 AS salary,
324328 25 AS age,
325329 point '(2,2)' AS cubicle;
326330$$ LANGUAGE SQL;
@@ -358,9 +362,46 @@ ERROR: function declared to return emp returns varchar instead of text at colum
358362 </para>
359363
360364 <para>
361- When you call a function that returns a row (composite type) in a
362- SQL expression, you might want only one field (attribute) from its
363- result. You can do that with syntax like this:
365+ A different way to define the same function is:
366+
367+ <programlisting>
368+ CREATE FUNCTION new_emp() RETURNS emp AS $$
369+ SELECT ROW('None', 1000.0, 25, '(2,2)')::emp;
370+ $$ LANGUAGE SQL;
371+ </programlisting>
372+
373+ Here we wrote a <command>SELECT</> that returns just a single
374+ column of the correct composite type. This isn't really better
375+ in this situation, but it is a handy alternative in some cases
376+ — for example, if we need to compute the result by calling
377+ another function that returns the desired composite value.
378+ </para>
379+
380+ <para>
381+ We could call this function directly in either of two ways:
382+
383+ <screen>
384+ SELECT new_emp();
385+
386+ new_emp
387+ --------------------------
388+ (None,1000.0,25,"(2,2)")
389+
390+ SELECT * FROM new_emp();
391+
392+ name | salary | age | cubicle
393+ ------+--------+-----+---------
394+ None | 1000.0 | 25 | (2,2)
395+ </screen>
396+
397+ The second way is described more fully in <xref
398+ linkend="xfunc-sql-table-functions">.
399+ </para>
400+
401+ <para>
402+ When you use a function that returns a composite type,
403+ you might want only one field (attribute) from its result.
404+ You can do that with syntax like this:
364405
365406<screen>
366407SELECT (new_emp()).name;
@@ -398,15 +439,14 @@ SELECT name(new_emp());
398439
399440<screen>
400441-- This is the same as:
401- -- SELECT emp.name AS youngster FROM emp WHERE emp.age < 30
442+ -- SELECT emp.name AS youngster FROM emp WHERE emp.age < 30;
402443
403- SELECT name(emp) AS youngster
404- FROM emp
405- WHERE age(emp) < 30;
444+ SELECT name(emp) AS youngster FROM emp WHERE age(emp) < 30;
406445
407446 youngster
408447-----------
409448 Sam
449+ Andy
410450</screen>
411451 </para>
412452
@@ -433,7 +473,7 @@ SELECT getname(new_emp());
433473 </para>
434474 </sect2>
435475
436- <sect2>
476+ <sect2 id="xfunc-sql-table-functions" >
437477 <title><acronym>SQL</acronym> Functions as Table Sources</title>
438478
439479 <para>