Skip to content

Commit 9aac623

Browse files
bmomjianbmomjian
committed
Add item for plpgsql temp table access.
1 parent 090319b commit 9aac623

File tree

2 files changed

+40
-16
lines changed

2 files changed

+40
-16
lines changed

‎doc/FAQ

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11

22
Frequently Asked Questions (FAQ) for PostgreSQL
33

4-
Last updated: Fri Apr 26 23:03:46 EDT 2002
4+
Last updated: Mon Jun 10 15:47:38 EDT 2002
55

66
Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us)
77

@@ -92,6 +92,8 @@
9292
4.23) How do I perform an outer join?
9393
4.24) How do I perform queries using multiple databases?
9494
4.25) How do I return multiple rows or columns from a function?
95+
4.26) Why can't I reliably create/drop temporary tables in PL/PgSQL
96+
functions?
9597

9698
Extending PostgreSQL
9799

@@ -1031,6 +1033,17 @@ SELECT *
10311033
http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html,
10321034
section 23.7.3.3.
10331035

1036+
4.26) Why can't I reliably create/drop temporary tables in PL/PgSQL
1037+
functions?
1038+
1039+
PL/PgSQL caches function contents, and an unfortunate side effect is
1040+
that if a PL/PgSQL function accesses a temporary table, and that table
1041+
is later dropped and recreated, and the function called again, the
1042+
function will fail because the cached function contents still point to
1043+
the old temporary table. The solution is to use EXECUTE for temporary
1044+
table access in PL/PgSQL. This will cause the query to be reparsed
1045+
every time.
1046+
10341047
Extending PostgreSQL
10351048

10361049
5.1) I wrote a user-defined function. When I run it in psql, why does it

‎doc/src/FAQ/FAQ.html

Lines changed: 26 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
alink="#0000ff">
1515
<H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1>
1616

17-
<P>Last updated: Fri Apr 26 23:03:46 EDT 2002</P>
17+
<P>Last updated: Mon Jun 10 15:47:38 EDT 2002</P>
1818

1919
<P>Current maintainer: Bruce Momjian (<A href=
2020
"mailto:pgman@candle.pha.pa.us">pgman@candle.pha.pa.us</A>)<BR>
@@ -138,7 +138,9 @@ <H2 align="center">Operational Questions</H2>
138138
<A href="#4.24">4.24</A>) How do I perform queries using multiple
139139
databases?<BR>
140140
<A href="#4.25">4.25</A>) How do I return multiple rows or columns
141-
from a function?<BR>
141+
from a function?<BR>
142+
<A href="#4.26">4.26</A>) Why can't I reliably create/drop
143+
temporary tables in PL/PgSQL functions?<BR>
142144

143145

144146
<H2 align="center">Extending PostgreSQL</H2>
@@ -742,7 +744,7 @@ <H4><A name="3.7">3.7</A>) What debugging features are
742744
<P>You can also compile with profiling to see what functions are
743745
taking execution time. The backend profile files will be deposited
744746
in the <I>pgsql/data/base/dbname</I> directory. The client profile
745-
file will be put in the client's current directory. Linux requires
747+
file will be put in the client's current directory. Linux requires
746748
a compile with <I>-DLINUX_PROFILE</I> for proper profiling.</P>
747749

748750
<H4><A name="3.8">3.8</A>) Why do I get <I>"Sorry, too many
@@ -900,9 +902,9 @@ <H4><A name="4.7">4.7</A>) How do I find out what tables, indexes,
900902
databases, and users are defined?</H4>
901903

902904
<P><I>psql</I> has a variety of backslash commands to show such
903-
information. Use \? to see them. There are also system tables
904-
beginning with <i>pg_</i> that describe these too. Also, <i>psql
905-
-l</i> will list all databases.</P>
905+
information. Use \? to see them. There are also system tables
906+
beginning with <I>pg_</I> that describe these too. Also, <I>psql
907+
-l</I> will list all databases.</P>
906908

907909
<P>Also try the file <I>pgsql/src/tutorial/syscat.source</I>. It
908910
illustrates many of the <SMALL>SELECT</SMALL>s needed to get
@@ -1307,16 +1309,25 @@ <H4><A name="4.24">4.24</A>) How do I perform queries using
13071309
different databases and merge the information that way.</P>
13081310
<HR>
13091311

1310-
<H4><A name="4.25">4.25</A>) How do I return multiple rows or columns
1311-
from a function?</H4>
1312+
<H4><A name="4.25">4.25</A>) How do I return multiple rows or
1313+
columns from a function?</H4>
1314+
1315+
<P>You can return result sets from PL/pgSQL functions using
1316+
<I>refcursors</I>. See <A href=
1317+
"http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html">
1318+
http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html,</A>
1319+
section 23.7.3.3.</P>
1320+
1321+
<H4><A href="#4.26">4.26</A>) Why can't I reliably create/drop
1322+
temporary tables in PL/PgSQL functions?</H4>
1323+
PL/PgSQL caches function contents, and an unfortunate side effect
1324+
is that if a PL/PgSQL function accesses a temporary table, and that
1325+
table is later dropped and recreated, and the function called
1326+
again, the function will fail because the cached function contents
1327+
still point to the old temporary table. The solution is to use
1328+
<SMALL>EXECUTE</SMALL> for temporary table access in PL/PgSQL. This
1329+
will cause the query to be reparsed every time.
13121330

1313-
<P>You can return result sets from PL/pgSQL functions using
1314-
<i>refcursors</i>. See <a
1315-
href="http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html">
1316-
http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html,</a>
1317-
section 23.7.3.3.</P>
1318-
1319-
13201331
<H2 align="center">Extending PostgreSQL</H2>
13211332

13221333
<H4><A name="5.1">5.1</A>) I wrote a user-defined function. When I

0 commit comments

Comments
 (0)