11<!--
2- $Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.12 2002/11/23 03:59:05 momjian Exp $
2+ $Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.13 2003/01/15 16:40:24 momjian Exp $
33-->
44
55<chapter id="plpgsql">
@@ -68,6 +68,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.12 2002/11/23 03:59:05 mom
6868 <acronym>SQL</acronym> expressions and <acronym>SQL</acronym> queries
6969 used in the function are not translated immediately.
7070 </para>
71+
7172 <para>
7273 As each expression and <acronym>SQL</acronym> query is first used
7374in the function, the <application>PL/pgSQL</> interpreter creates
@@ -84,12 +85,13 @@ $Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.12 2002/11/23 03:59:05 mom
8485that errors in a specific expression or query may not be detected
8586until that part of the function is reached in execution.
8687 </para>
87- <para>
88- Once <application>PL/pgSQL</> has made a query plan for a particular
89- query in a function, it will re-use that plan for the life of the
90- database connection. This is usually a win for performance, but it
91- can cause some problems if you dynamically
92- alter your database schema. For example:
88+
89+ <para>
90+ Once <application>PL/pgSQL</> has made a query plan for a particular
91+ query in a function, it will re-use that plan for the life of the
92+ database connection. This is usually a win for performance, but it
93+ can cause some problems if you dynamically
94+ alter your database schema. For example:
9395
9496<programlisting>
9597CREATE FUNCTION populate() RETURNS INTEGER AS '
@@ -100,14 +102,19 @@ BEGIN
100102END;
101103' LANGUAGE 'plpgsql';
102104</programlisting>
105+
103106 If you execute the above function, it will reference the OID for
104107 <function>my_function()</function> in the query plan produced for
105108 the <command>PERFORM</command> statement. Later, if you
106109 drop and re-create <function>my_function()</function>, then
107110 <function>populate()</function> will not be able to find
108111 <function>my_function()</function> anymore. You would then have to
109112 re-create <function>populate()</function>, or at least start a new
110- database session so that it will be compiled afresh.
113+ database session so that it will be compiled afresh. Another way
114+ to avoid this problem is to use <command>CREATE OR REPLACE
115+ FUNCTION</command> when updating the definition of
116+ <function>my_function</function> (when a function is
117+ <quote>replaced</quote>, its OID is not changed).
111118 </para>
112119
113120 <para>
@@ -221,35 +228,17 @@ END;
221228 <title>Developing in <application>PL/pgSQL</application></title>
222229
223230 <para>
224- Developing in <application>PL/pgSQL</application> is pretty straight forward, especially
225- if you have developed in other database procedural languages,
226- such as Oracle's <application>PL/SQL</application>. Two good ways of developing in
227- <application>PL/pgSQL</application> are:
228-
229- <itemizedlist>
230- <listitem>
231- <para>
232- Using a text editor and reloading the file with <command>psql</command>
233- </para>
234- </listitem>
235-
236- <listitem>
237- <para>
238- Using <productname>PostgreSQL</>'s GUI Tool: <application>PgAccess</>
239- </para>
240- </listitem>
241- </itemizedlist>
242- </para>
243-
244- <para>
245- One good way to develop in <application>PL/pgSQL</> is to simply
246- use the text editor of your choice to create your functions, and
247- in another window, use <command>psql</command>
248- (<productname>PostgreSQL</>'s interactive monitor) to load those
249- functions. If you are doing it this way, it is a good idea to
250- write the function using <command>CREATE OR REPLACE
251- FUNCTION</>. That way you can reload the file to update the
252- function definition. For example:
231+ Developing in <application>PL/pgSQL</application> is pretty
232+ straight forward, especially if you have developed in other
233+ database procedural languages, such as Oracle's
234+ <application>PL/SQL</application>. One good way to develop in
235+ <application>PL/pgSQL</> is to simply use the text editor of your
236+ choice to create your functions, and in another window, use
237+ <command>psql</command> (<productname>PostgreSQL</>'s interactive
238+ monitor) to load those functions. If you are doing it this way, it
239+ is a good idea to write the function using <command>CREATE OR
240+ REPLACE FUNCTION</>. That way you can reload the file to update
241+ the function definition. For example:
253242<programlisting>
254243CREATE OR REPLACE FUNCTION testfunc(INTEGER) RETURNS INTEGER AS '
255244 ....
@@ -268,10 +257,12 @@ end;
268257 </para>
269258
270259 <para>
271- Another good way to develop in <application>PL/pgSQL</> is using
272- <productname>PostgreSQL</>'s GUI tool: <application>PgAccess</>. It does some
273- nice things for you, like escaping single-quotes, and making
274- it easy to recreate and debug functions.
260+ Another good way to develop in <application>PL/pgSQL</> is using a
261+ GUI database access tool that facilitates development in a
262+ procedural language. One example of such as a tool is
263+ <application>PgAccess</>, although others exist. These tools often
264+ provide convenient features such as escaping single-quotes, and
265+ making it easier to recreate and debug functions.
275266 </para>
276267 </sect2>
277268 </sect1>